Technical Article

Disk Capacities

,

Hey Guys,

This script will provide information on the drive capacities, in the form of total volume, used space, free space and then a percentage of used space.

Assuming that it is being executed on Windows Server 2003+ and the user calling it has permissions to run xp_cmdshell it should run without a problem

The script uses a built in tool present in Windows Server 2003 onwards, however this utility can be downloaded and deployed on other Windows Server operating systems. (Such as Windows Server 2000)

Hope you guys find it usefull!

/*
Author:Nicholas Williams
Date:            2009 sometime 
Description:   Provides information on the drive capacities for a particular instance.
                Requires xp_cmdshell to be active, and the fsutil windows utility. (Present on Windows Server 2003+) user must have sa permissions. uses a (fast) cursor.... yeah, i know... :(
                Results are returned in MB. 
                 xp_cmdshell enabler:
                    sp_configure 'show advanced options',1
                   GO
                    RECONFIGURE
                    GO
                    sp_configure 'xp_cmdshell',1
                    GO
                    RECONFIGURE
*/
SET NOCOUNT ON
DECLARE 
     @vcName     CHAR(1)
CREATE TABLE #tbl_disks
    (
     Drive    CHAR(1)
    ,[Space]    INT
    )

CREATE TABLE ##tbl_Volumes
    (
     Drive    CHAR(1)
    ,Info    VARCHAR(2000)
    )

CREATE TABLE ##tbl_Volumes2
    (
     Info    VARCHAR(2000)
    )

INSERT INTO #tbl_disks
EXEC master.dbo.xp_fixeddrives 

DECLARE file_cursor CURSOR FOR
SELECT Drive FROM #tbl_disks
ORDER BY Drive
OPEN file_cursor

 FETCH NEXT FROM file_cursor
 INTO @vcName

WHILE @@FETCH_STATUS = 0
BEGIN 

EXEC
    ('
    INSERT INTO ##tbl_Volumes (Drive)
    VALUES ('''+@vcName+''')

    INSERT INTO ##tbl_Volumes2
    EXEC master.dbo.xp_cmdshell ''fsutil volume diskfree '+@vcName+':''

    UPDATE ##tbl_Volumes 
    SET Info = v2.Info
    FROM ##tbl_Volumes2 v2
    WHERE v2.Info LIKE ''%Total # of bytes%''
    AND ##tbl_Volumes.Drive = '''+@vcName+'''

    TRUNCATE TABLE ##tbl_Volumes2
    ')

 FETCH NEXT FROM file_cursor
 INTO @vcName

END
CLOSE file_cursor
DEALLOCATE file_cursor

SELECT 
     CAST(@@SERVERNAME as VARCHAR(35)) ServerName
    ,v.Drive
    ,(CAST((SUBSTRING(v.Info,(CHARINDEX(':',v.Info,1)+1), (LEN(v.Info)-(CHARINDEX(':',v.Info,1))))) as DECIMAL(38,5)))/(1024*1024) TotalVolume
    ,d.[Space] FreeSpace
    ,(CAST((SUBSTRING(v.Info,(CHARINDEX(':',v.Info,1)+1), (LEN(v.Info)-(CHARINDEX(':',v.Info,1))))) as DECIMAL(38,5)))/(1024*1024) - (d.[Space]) as SpaceUsed
    ,CAST((d.[Space] / ((CAST((SUBSTRING(v.Info,(CHARINDEX(':',v.Info,1)+1), (LEN(v.Info)-(CHARINDEX(':',v.Info,1))))) as DECIMAL(38,5)))/(1024*1024) / 100)) AS INT) PercentFreeSpace 
FROM ##tbl_Volumes v
INNER JOIN #tbl_disks d ON v.Drive = d.Drive

PRINT 'Results are returned in MB, unless otherwise specified'

DROP TABLE #tbl_disks
DROP TABLE ##tbl_Volumes
DROP TABLE ##tbl_Volumes2
SET NOCOUNT OFF

Rate

4.27 (11)

You rated this post out of 5. Change rating

Share

Share

Rate

4.27 (11)

You rated this post out of 5. Change rating