SQL Server Mentalist

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



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



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 |