Home > Link, Microsoft SQL Server, MSBI, Optimization, Query, Script, SQL Mentalist, SQL PraRup, SQL Query, SQL Server, Technology,, TSQL, Vishal Pawar > BI SQL # 126 : SQL Server DBA Scripts : Space utilization in terms of Driver Space

BI SQL # 126 : SQL Server DBA Scripts : Space utilization in terms of Driver Space

Hi Geeks,

In this article we are going to cover Space utilization in terms of Driver Space.

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 the Space Utilization?

    Description of SQL Script:

    Script helps to find the how much the databases are using the space on disk taking in account the free space at the end of the database and the sum of space used bay all databases per disk. It’s very useful to see how much space you can gain on disk if you shrink your databases on a specific disk unit.

    SQL Script Output Column

    image

    SQL Script Code

    BEGIN
        SET NOCOUNT ON
    
        IF EXISTS (
                SELECT 1
                FROM tempdb..sysobjects
                WHERE [Id] = object_id('tempdb..#dbfileinfo')
                )
        BEGIN
            DROP TABLE #dbfileinfo
        END
    
        IF EXISTS (
                SELECT 1
                FROM tempdb..sysobjects
                WHERE [Id] = object_id('tempdb..#logsizestats')
                )
        BEGIN
            DROP TABLE #logsizestats
        END
    
        IF EXISTS (
                SELECT 1
                FROM tempdb..sysobjects
                WHERE [Id] = object_id('tempdb..#datafilestats')
                )
        BEGIN
            DROP TABLE #datafilestats
        END
    
        IF EXISTS (
                SELECT 1
                FROM tempdb..sysobjects
                WHERE [Id] = object_id('tempdb..#fixeddrives')
                )
        BEGIN
            DROP TABLE #fixeddrives
        END
    
        IF EXISTS (
                SELECT 1
                FROM tempdb..sysobjects
                WHERE [Id] = object_id('tempdb..#usados')
                )
        BEGIN
            DROP TABLE #usados
        END
    
        CREATE TABLE #fixeddrives (
            DriveLetter VARCHAR(10)
            ,MB_Free DEC(20, 2)
            )
    
        CREATE TABLE #datafilestats (
            DBName VARCHAR(255)
            ,DBId INT
            ,FileId TINYINT
            ,[FileGroup] TINYINT
            ,TotalExtents DEC(20, 2)
            ,UsedExtents DEC(20, 2)
            ,[Name] VARCHAR(255)
            ,[FileName] VARCHAR(400)
            )
    
        CREATE TABLE #logsizestats (
            DBName VARCHAR(255) NOT NULL PRIMARY KEY CLUSTERED
            ,DBId INT
            ,LogFile REAL
            ,LogFileUsed REAL
            ,STATUS BIT
            )
    
        CREATE TABLE #dbfileinfo (
            [ServerName] VARCHAR(255)
            ,[DBName] VARCHAR(65)
            ,[LogicalFileName] VARCHAR(400)
            ,[UsageType] VARCHAR(30)
            ,[Size_MB] DEC(20, 2)
            ,[SpaceUsed_MB] DEC(20, 2)
            ,[MaxSize_MB] DEC(20, 2)
            ,[NextAllocation_MB] DEC(20, 2)
            ,[GrowthType] VARCHAR(65)
            ,[FileId] SMALLINT
            ,[GroupId] SMALLINT
            ,[PhysicalFileName] VARCHAR(400)
            ,[DateChecked] DATETIME
            )
    
        DECLARE @SQLString VARCHAR(3000)
        DECLARE @MinId INT
        DECLARE @MaxId INT
        DECLARE @DBName VARCHAR(255)
        DECLARE @tblDBName TABLE (
            RowId INT identity(1, 1)
            ,DBName VARCHAR(255)
            ,DBId INT
            )
    
        INSERT INTO @tblDBName (
            DBName
            ,DBId
            )
        SELECT [Name]
            ,DBId
        FROM master..sysdatabases
        WHERE (STATUS & 512) = 0
        ORDER BY [Name]
    
        INSERT INTO #logsizestats (
            DBName
            ,LogFile
            ,LogFileUsed
            ,STATUS
            )
        EXEC ('dbcc sqlperf(logspace) with no_infomsgs')
    
        UPDATE #logsizestats
        SET DBId = db_id(DBName)
    
        INSERT INTO #fixeddrives
        EXEC master..xp_fixeddrives
    
        SELECT @MinId = min(RowId)
            ,@MaxId = max(RowId)
        FROM @tblDBName
    
        WHILE (@MinId <= @MaxId)
        BEGIN
            SELECT @DBName = [DBName]
            FROM @tblDBName
            WHERE RowId = @MinId
    
            SELECT @SQLString = 'SELECT ServerName = @@SERVERNAME,' + 
                ' DBName = ''' + @DBName + ''',' + ' LogicalFileName = [name],' + 
                ' UsageType = CASE WHEN (64&[status])=64 THEN ''Log'' ELSE '
    'Data'' END,' 
                + ' Size_MB = [size]*8/1024.00,' + ' SpaceUsed_MB = NULL,' + 
                ' MaxSize_MB = CASE [maxsize] WHEN -1 THEN -1 WHEN 0 THEN [size]
    *8/1024.00 ELSE maxsize/1024.00*8 END,' 
                + 
                ' NextExtent_MB = CASE WHEN (1048576&[status])=1048576 THEN 
    ([growth]/100.00)*([size]*8/1024.00) WHEN [growth]=0 THEN 0 ELSE 
    [growth]*8/1024.00 END,' 
                + 
                ' GrowthType = CASE WHEN (1048576&[status])=1048576 THEN ''%'' 
    ELSE ''Pages'' END,' 
                + ' FileId = [fileid],' + ' GroupId = [groupid],' + 
                ' PhysicalFileName= [filename],' + 
                ' CurTimeStamp = GETDATE()' + 'FROM [' + @DBName + ']..sysfiles'
    
            PRINT @SQLString
    
            INSERT INTO #dbfileinfo
            EXEC (@SQLString)
    
            UPDATE #dbfileinfo
            SET SpaceUsed_MB = Size_MB / 100.0 * (
                    SELECT LogFileUsed
                    FROM #logsizestats
                    WHERE DBName = @DBName
                    )
            WHERE UsageType = 'Log'
                AND DBName = @DBName
    
            SELECT @SQLString = 'USE [' + @DBName + 
                '] DBCC SHOWFILESTATS WITH NO_INFOMSGS'
    
            INSERT #datafilestats (
                FileId
                ,[FileGroup]
                ,TotalExtents
                ,UsedExtents
                ,[Name]
                ,[FileName]
                )
            EXECUTE (@SQLString)
    
            UPDATE #dbfileinfo
            SET [SpaceUsed_MB] = S.[UsedExtents] * 64 / 1024.00
            FROM #dbfileinfo AS F
            INNER JOIN #datafilestats AS S ON F.[FileId] = S.[FileId]
                AND F.[GroupId] = S.[FileGroup]
                AND F.[DBName] = @DBName
    
            TRUNCATE TABLE #datafilestats
    
            SELECT @MinId = @MinId + 1
        END
    
        SELECT @@servername AS servidor
            ,substring(A.PhysicalFileName, 1, 1) AS unidad
            ,sum([Size_MB]) AS SqlTotalDB
            ,sum([SpaceUsed_MB]) AS SqlTotalUsedSpaceDB
            ,sum(([Size_MB]) - ([SpaceUsed_MB]
                    )) AS SQLTotalFreeSpaceDB
        INTO #usados
        FROM #dbfileinfo AS A
        LEFT JOIN #fixeddrives AS B ON substring(A.PhysicalFileName, 1, 1) = B.
            DriveLetter
        GROUP BY substring(A.PhysicalFileName, 1, 1)
    
        SELECT servidor
            ,DriveLetter
            ,MB_Free AS RealMb_free
            ,MB_Free + SQLTotalFreeSpaceDB AS MB_FreeNeto
            ,SqlTotalDB
            ,abs((SqlTotalDB - SQLTotalFreeSpaceDB)) AS 
            SQLTotalUsedSpaceDB
            ,SQLTotalFreeSpaceDB
            ,(
                100 * abs((SqlTotalDB - SQLTotalFreeSpaceDB
                        ))
                ) / SqlTotalDB AS Porcentaje_Uso_DB
        FROM #fixeddrives AS f
        INNER JOIN #usados AS z ON z.unidad = f.DriveLetter
    
        IF EXISTS (
                SELECT 1
                FROM tempdb..sysobjects
                WHERE [Id] = object_id('tempdb..#dbfileinfo')
                )
        BEGIN
            DROP TABLE #dbfileinfo
        END
    
        IF EXISTS (
                SELECT 1
                FROM tempdb..sysobjects
                WHERE [Id] = object_id('tempdb..#logsizestats')
                )
        BEGIN
            DROP TABLE #logsizestats
        END
    
        IF EXISTS (
                SELECT 1
                FROM tempdb..sysobjects
                WHERE [Id] = object_id('tempdb..#datafilestats')
                )
        BEGIN
            DROP TABLE #datafilestats
        END
    
        IF EXISTS (
                SELECT 1
                FROM tempdb..sysobjects
                WHERE [Id] = object_id('tempdb..#fixeddrives')
                )
        BEGIN
            DROP TABLE #fixeddrives
        END
    
        IF EXISTS (
                SELECT 1
                FROM tempdb..sysobjects
                WHERE [Id] = object_id('tempdb..#usados')
                )
        BEGIN
            DROP TABLE #usados
        END
    
        SET NOCOUNT OFF
    END

    SQL Script Output Screenshot

    image

    User Level to execute

    400

    Hope you will like to Space utilization in terms of Driver Space.

    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. July 1, 2013 at 10:46 pm

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: