Space Management Report - Get Drive Size and Drive Free Space :SQL Server

One of the regular DBA practice is to maintain the free space on Drives where the data files are located.

Here i have created a script (Space Management) which gives us the database files, its location,size, growth rate, the drive where its located, drive size, drive free space etc...

Most of the thing is very easy to retrieve through SQL Queries as those are maintained in tables under msdb databases, but the free space and size of drive is something which require system interaction using master..xp_cmdshell and that part of query is a bit complex :)

declare @Drive table(DriveName char,freespace decimal(9,2), totalspace decimal(9,2),percentfree decimal(9,2))

BEGIN

    SET NOCOUNT ON;

    DECLARE @v_cmd nvarchar(255)
    ,@v_drive char(99)
    ,@v_sql nvarchar(255)
    ,@i int

    SELECT @v_cmd = 'fsutil volume diskfree %d%'
    SET @i = 1

    -- Creating temporary tables to retrive system space information.
    CREATE TABLE #drives(iddrive smallint ,drive char(99))

    CREATE TABLE #temp1(drive char(99),shellCmd nvarchar(500));

    CREATE TABLE #results(drive char(99),freespace decimal(9,2), totalspace decimal(9,2));


    INSERT #drives (drive)

    EXEC master..xp_cmdshell 'mountvol'

    DELETE #drives WHERE drive not like '%:\%' or drive is null


    WHILE (@i <= (SELECT count(drive) FROM #drives))

    BEGIN

         UPDATE #drives
         SET iddrive=@i
         WHERE drive = (SELECT TOP 1 drive FROM #drives WHERE iddrive IS NULL)

         SELECT @v_sql = REPLACE(@v_cmd,'%d%',LTRIM(RTRIM(drive))) from #drives where iddrive=@i

         INSERT #temp1(shellCmd)
         EXEC master..xp_cmdshell @v_sql

         UPDATE #temp1
         SET #temp1.drive = d.drive
         FROM #drives d
         WHERE #temp1.drive IS NULL and iddrive=@i

        SET @i = @i + 1

    END


        INSERT INTO #results
        SELECT bb.drive
        ,CAST(CAST(REPLACE(REPLACE(SUBSTRING(shellCmd,CHARINDEX(':',shellCmd)+1,LEN(shellCmd)),SPACE(1),SPACE(0))
        ,char(13),SPACE(0)) AS NUMERIC(32,2))/1024/1024/1024 AS DECIMAL(9,2)) as freespace
        ,tt.titi as total
        FROM #temp1 bb
        JOIN (SELECT drive
        ,CAST(CAST(REPLACE(REPLACE(SUBSTRING(shellCmd,CHARINDEX(':',shellCmd)+1,LEN(shellCmd)),SPACE(1),SPACE(0))
        ,char(13),SPACE(0)) AS NUMERIC(32,2))/1024/1024/1024 AS DECIMAL(9,2)) as titi
        FROM #temp1
        WHERE drive IS NOT NULL
        AND shellCmd NOT LIKE '%free bytes%') tt
        ON bb.drive = tt.drive
        WHERE bb.drive IS NOT NULL
        AND bb.shellCmd NOT LIKE '%avail free bytes%'
        AND bb.shellCmd LIKE '%free bytes%';

    -- getting all system specific formatted data into @Drive table
    INSERT INTO @Drive
    SELECT LEFT(RTRIM(LTRIM(drive)),1) as drive
     ,freespace
     ,totalspace
     ,CAST((freespace/totalspace * 100) AS DECIMAL(5,2)) as [percent free]
    FROM #results
    ORDER BY drive

    -- Dropping temporary tables
    DROP TABLE #drives
    DROP TABLE #temp1
    DROP TABLE #results

    SELECT
         @@Servername as Server,
         sd.name as Database_name,
        mas.name as File_Name,
        mas.size * 8 / 1024 File_Size_MB,
        CASE [is_percent_growth]  
               WHEN 1 THEN CAST(mas.growth AS varchar(20)) + '%' 
                ELSE CAST(mas.growth*8/1024 AS varchar(20)) + ' MB' 
        END as Growth_rate,     
        drv.DriveName as Drive_Name,
        drv.FreeSpace,
        drv.totalspace,
        CASE type
               WHEN 0 THEN 'Data'  
               WHEN 1 THEN 'Log' 
        END as File_type, 
        mas.physical_name as File_Location
    FROM sys.master_files mas
    LEFT JOIN @Drive drv ON LEFT(mas.physical_name, 1) = drv.DriveName
    LEFT JOIN sys.databases sd ON mas.database_id = sd.database_id
    --WHERE sd.name IN ('db1','db2','db3') -- Specify the database name if this report is needed for specific databases
    --AND mas.growth <>0 -- Condition to retrive the record for growing files only.

END

• You can restrict the output for some of the databases by providing the database name which is commented by default.

             --WHERE sd.name IN ('db1','db2','db3')  

•  You can also restrict the output for growing files of the databases only by providing the growth <>0 which is commented by default.

             --AND mas.growth <>0

There are further filters also possible like if someone want to restrict the query upto a size limit of files etc. simply write it down in where clause.


Output:

Output should be like this:

This could be very useful where someone have multiple servers and DBA has to generate weekly/Monthly report for all of the server, in such scenario one can create a SSIS package and execute this query on all the server and generate a single report for all the servers.

Hope this will be helpful for people out there :) let me know if anyone has any issue.