Home > Microsoft SQL Server, MSBI, Optimization, Query, Script, SQL, SQL Mentalist, SQL PraRup, SQL Query, SQL Server, Technology,, TSQL, Vishal Pawar > BI SQL # 141 : SQL Server DBA Scripts : Database Space usage in detail

BI SQL # 141 : SQL Server DBA Scripts : Database Space usage in detail

Hi Folks,

In this article we are going to cover Database Space usage in detail.

In this post we are going to discuss following points:

  • Problem Statement of SQL Script:
  • Description of SQL Script:
  • SQL Script Output Column
  • SQL Script Code
  • SQL Script Output Screenshot
  • User Level to execute

    Problem Statement of SQL Script:

    How to find Database space usage in Detailed

    Description of SQL Script:

    This script help us to Identify Many things.

    SQL Script Output Column

    image

    SQL Script Code

    USE master
    GO
    
    -- Create a set of temp tables to hold the working data of your  
    -- environment you are wanting to report on. 
    IF OBJECT_ID('tempdb..#tmp_DBNames') IS NOT NULL
        DROP TABLE #tmp_DBNames
    
    CREATE TABLE #tmp_DBNames (DBName VARCHAR(200))
    
    IF OBJECT_ID('tempdb..#tmp_DBSpacing') IS NOT NULL
        DROP TABLE #tmp_DBSpacing
    
    CREATE TABLE #tmp_DBSpacing (
        DBName VARCHAR(200)
        ,FileID INT
        ,database_size DECIMAL(35, 2)
        ,[MaxSize] DECIMAL(35, 2)
        ,Growth DECIMAL(35, 2)
        )
    
    IF OBJECT_ID('tempdb..#DBFile_Info') IS NOT NULL
        DROP TABLE #DBFile_Info
    
    CREATE TABLE #DBFile_Info (
        FileID INT
        ,FileGroup INT
        ,TotalExtents DEC(20, 2)
        ,UsedExtents DEC(20, 2)
        ,DB_FileName VARCHAR(200)
        ,FilePathName VARCHAR(500)
        )
    
    IF OBJECT_ID('tempdb..#DB_Logfile_Info') IS NOT NULL
        DROP TABLE #DB_Logfile_Info
    
    CREATE TABLE #DB_Logfile_Info (
        DBName VARCHAR(200)
        ,LogSize_Reserved DEC(20, 2)
        ,LogSpace_Used_Percent DEC(20, 2)
        ,STATUS BIT
        )
    
    IF OBJECT_ID('tempdb..#DBResults') IS NOT NULL
        DROP TABLE #DBResults
    
    CREATE TABLE #DBResults (
        DBName VARCHAR(200)
        ,FileID INT
        ,DB_Reserved_Space DEC(20, 2)
        ,DB_Used_Space DEC(20, 2)
        ,DB_Free_Space DEC(20, 2)
        ,DB_Free_Percent DEC(20, 2)
        ,DB_Max_Size DEC(35, 2)
        ,DB_Growth DEC(35, 2)
        ,Log_Reserved_Space DEC(20, 2)
        ,Log_Used_Percent DEC(20, 2)
        ,Log_Free_Percent DEC(20, 2)
        ,Log_Max_Size DEC(35, 2)
        ,Log_Growth DEC(35, 2)
        )
    
    IF OBJECT_ID('tempdb..#DBFileUsage_Report') IS NOT NULL
        DROP TABLE #DBFileUsage_Report
    
    CREATE TABLE #DBFileUsage_Report (
        DBName VARCHAR(200)
        ,FileType VARCHAR(100)
        ,DB_Size_GB DEC(35, 4)
        ,Datafile_freespace_GB DEC(35, 4)
        ,DB_Pct_Used DEC(35, 4)
        ,DB_File_Max_Size_GB DEC(35, 4)
        ,DB_File_Allowed_Growth_GB DEC(35, 4)
        ,DB_File_Growth_Increment_GB DEC(35, 4)
        ,TLog_Size_GB DEC(35, 4)
        ,Logfile_Freespace_GB DEC(35, 4)
        ,Log_Pct_Used DEC(35, 4)
        ,Log_File_Max_Size_GB DEC(35, 4)
        ,Log_File_Allowed_Growth_GB DEC(35, 4)
        ,Log_File_Growth_Increment_GB DEC(35, 4)
        )
    
    DECLARE @DBName VARCHAR(200)
    DECLARE @Cmd VARCHAR(4000)
    DECLARE @nCmd NVARCHAR(4000)
    DECLARE @RunCMD NVARCHAR(500)
    DECLARE @TotalMaxLogSize DEC(20, 2)
    DECLARE @TotalLogGrowth DEC(20, 2)
    
    INSERT INTO #tmp_DBNames
    SELECT NAME
    FROM sysdatabases
    --Where Name not in ('master','msdb','model','tempdb') 
    WHERE NAME NOT IN (
            'master'
            ,'msdb'
            ,'model'
            )
    
    -- only need to run once as it gets all DB's for current instance 
    INSERT INTO #DB_Logfile_Info
    EXEC ('DBCC SQLPERF(LOGSPACE)')
    
    DECLARE DBNames_Cursor CURSOR
    FOR
    SELECT DBName
    FROM #tmp_DBNames
    
    OPEN DBNames_Cursor
    
    FETCH NEXT
    FROM DBNames_Cursor
    INTO @DBName
    
    WHILE @@Fetch_Status = 0
    BEGIN
        SET @Cmd = 
            'Select    fileid, 
                            convert(decimal(35,2),size) / convert( float, (1048576 
    /(select low From master.dbo.spt_values where number = 1 and type = ''E''))), 
                            convert(decimal(35,2),maxsize) / convert( float, 
    (1048576 /(select low From master.dbo.spt_values where number = 1 
    and type = ''E''))), 
                            convert(decimal(35,2),growth) / convert( float, 
    (1048576 /(select low From master.dbo.spt_values where number = 1 
    and type = ''E''))) 
                            From [' 
            + @DBName + '].dbo.sysfiles'
        SET @nCmd = Cast(@Cmd AS NVARCHAR(1000))
    
        INSERT INTO #tmp_DBSpacing (
            fileid
            ,database_size
            ,[MaxSize]
            ,growth
            )
        EXEC sp_Executesql @nCmd
    
        UPDATE #tmp_DBSpacing
        SET DBName = @DBName
        WHERE DBName IS NULL
    
        SET @RunCMD = 'Use [' + @DBName + '] DBCC showfilestats'
    
        INSERT INTO #DBFile_Info
        EXEC sp_executesql @RunCMD
    
        INSERT INTO #DBResults (
            DBName
            ,FileID
            ,DB_Reserved_Space
            ,DB_Used_Space
            ,DB_Free_Space
            ,DB_Free_Percent
            ,Log_Reserved_Space
            ,Log_Used_Percent
            ,Log_Free_Percent
            )
        SELECT @DBName
            ,FileID
            ,(TotalExtents * 64 / 1024)
            ,(UsedExtents * 64 / 1024)
            ,(TotalExtents * 64 / 1024) - (UsedExtents * 64 / 1024
                )
            ,(
                (
                    (TotalExtents * 64 / 1024) - (UsedExtents * 64 / 1024
                        )
                    ) / (TotalExtents * 64 / 1024)
                ) * 100
            ,LogSize_Reserved
            ,LogSpace_Used_Percent
            ,100 - LogSpace_Used_Percent
        FROM #DBFile_Info dfi
            ,#DB_Logfile_Info dli
        WHERE dli.DBName = @DBName
    
       UPDATE #DBResults
        SET DB_Max_Size = #tmp_DBSpacing.[MaxSize]
            ,DB_Growth = #tmp_DBSpacing.Growth
        FROM #tmp_DBSpacing
        INNER JOIN #DBResults ON #DBResults.DBName = #tmp_DBSpacing.
            DBName
            AND #DBResults.FileID = #tmp_DBSpacing.FileID
        WHERE #DBResults.DBName = @DBName
    
        -- Determine the maxsize for the tlog file(s) 
        SELECT @TotalMaxLogSize = Sum(#tmp_DBSpacing.[MaxSize])
            ,@TotalLogGrowth = Sum(#tmp_DBSpacing.Growth)
        FROM #tmp_DBSpacing
        WHERE #tmp_DBSpacing.FileId NOT IN (
                SELECT FileID
                FROM #DBResults
                WHERE DBName = #tmp_DBSpacing.DBName
                )
            AND #tmp_DBSpacing.DBName = @DBName
    
        -- Update the max Tlog size based on the above calculation 
        UPDATE #DBResults
        SET Log_Max_Size = @TotalMaxLogSize
            ,Log_Growth = @TotalLogGrowth
        WHERE DBName = @DBName
    
        TRUNCATE TABLE #DBFile_Info
    
        FETCH NEXT
        FROM DBNames_Cursor
        INTO @DBName
    END
    
    CLOSE DBNames_Cursor
    
    DEALLOCATE DBNames_Cursor
    
    -- Capture the full set of data that we are looking at reporting on. 
    INSERT INTO #DBFileUsage_Report
    SELECT DBName
        ,FileType = CASE 
            WHEN FileId = 1
                THEN 'Primary File'
            ELSE 'Secondary File'
            END
        ,(DB_Reserved_Space / 1024) AS 'DB File Size (GB)'
        ,(DB_Free_Space / 1024) AS 
        'DB Free Space in File (GB)'
        ,'DB % Used' = CASE 
            WHEN (DB_Reserved_Space / DB_Max_Size) * 
                100 < 0
                THEN 0
            ELSE (DB_Reserved_Space / DB_Max_Size) * 
                100
            END
        ,(DB_Max_Size / 1024) AS 'DB Max Size (GB)'
        ,'Allowed DB Growth' = CASE 
            WHEN DB_Max_Size - DB_Reserved_Space < 0
                THEN 0
            ELSE (DB_Max_Size - DB_Reserved_Space) / 
                1024
            END
        ,(DB_Growth / 1024) AS 'Growth Increment (GB)'
        ,(Log_Reserved_Space / 1024) AS 'Logfile Size (GB)'
        ,(
            (
                (Log_Reserved_Space * Log_Free_Percent
                    ) / 100
                ) / 1024
            ) AS 'Logfile Freespace (GB)'
        ,'Log % Used' = CASE 
            WHEN Log_Max_Size > 0
                THEN (
                        (Log_Reserved_Space / Log_Max_Size
                            ) * 100
                        )
            ELSE 0
            END
        ,(Log_Max_Size / 1024) AS 'Log Max Size (GB)'
        ,'Allowed Log Growth (GB)' = CASE 
            WHEN Log_Max_Size > 0
                THEN (
                        (Log_Max_Size - Log_Reserved_Space
                            ) / 1024
                        )
            ELSE 0
            END
        ,(Log_Growth / 1024) AS 'Growth Increment (GB)'
    FROM #DBResults
    ORDER BY DBName ASC
        ,DB_Reserved_Space DESC
    
    -- Retrieve the full list of data retrieved 
    SELECT *
    FROM #DBFileUsage_Report
    --where DBName in ('[DBName]') 
    ORDER BY DB_PCT_Used DESC
    
    -- Clean up all temp tables 
    DROP TABLE #tmp_DBNames
    
    DROP TABLE #tmp_DBSpacing
    
    DROP TABLE #DBFile_Info
    
    DROP TABLE #DBResults
    
    DROP TABLE #DB_Logfile_Info
    
    DROP TABLE #DBFileUsage_Report

    SQL Script Output Screenshot

    image

    User Level to execute

    100

    Hope you will like Database Space usage in detail.

    If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.

If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog :

Link Resource Website

For More information related to BI World visit my Mentalist Blog

SQL Server Mentalist >> SQL Learning Blog

Business Intelligence Mentalist >> BI World

Infographic Mentalist >> Image worth explaining thousand Words

Microsoft Mentalist >> MVC,ASP.NET, WCF & LinQ

DBA Mentalist >>Advance SQL Server Blog

Microsoft BI Mentalist >> MS BI Development Update

Connect With me on

| FaceBook |Twitter | linkedIn| Google+ | WordPress | RSS |

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: