SQL Server: TSQL Script to Get Hard Drives Detail


Recently, I need a script which can provide me detail (Driver Letter, Drive Label, Free Space, Used Space etc) of all installed hard drives. I found following script by G. Rayburn very helpful.

Valid for versions: SQL Server 2005 and above

Expected Result:
 Note: Before executing script, don’t forget to enable Ole Automation Procedures from Surface Area Configuration or using sp_configure.
/********************************************************
**    Author:  G. Rayburn
*********************************************************/
SET NOCOUNT ON
IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = ‘##_DriveSpace’)
      DROP TABLE ##_DriveSpace
IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = ‘##_DriveInfo’)
      DROP TABLE ##_DriveInfo
DECLARE @Result INT
      , @objFSO INT
      , @Drv INT
      , @cDrive VARCHAR(13)
      , @Size VARCHAR(50)
      , @Free VARCHAR(50)
      , @Label varchar(10)
CREATE TABLE ##_DriveSpace
      (
        DriveLetter CHAR(1) not null
      , FreeSpace VARCHAR(10) not null
       )
CREATE TABLE ##_DriveInfo
      (
      DriveLetter CHAR(1)
      , TotalSpace bigint
      , FreeSpace bigint
      , Label varchar(10)
      )
INSERT INTO ##_DriveSpace
      EXEC master.dbo.xp_fixeddrives
— Iterate through drive letters.
DECLARE  curDriveLetters CURSOR
      FOR SELECT driveletter FROM ##_DriveSpace
DECLARE @DriveLetter char(1)
      OPEN curDriveLetters
FETCH NEXT FROM curDriveLetters INTO @DriveLetter
WHILE (@@fetch_status 1)
BEGIN
      IF (@@fetch_status 2)
      BEGIN
             SET @cDrive = ‘GetDrive(“‘ + @DriveLetter + ‘”)’
                  EXEC @Result = sp_OACreate ‘Scripting.FileSystemObject’, @objFSO OUTPUT
                        IF @Result = 0
                              EXEC @Result = sp_OAMethod @objFSO, @cDrive, @Drv OUTPUT
                        IF @Result = 0
                              EXEC @Result = sp_OAGetProperty @Drv,‘TotalSize’, @Size OUTPUT
                        IF @Result = 0
                              EXEC @Result = sp_OAGetProperty @Drv,‘FreeSpace’, @Free OUTPUT
                        IF @Result = 0
                              EXEC @Result = sp_OAGetProperty @Drv,‘VolumeName’, @Label OUTPUT
                        IF @Result 0
                              EXEC sp_OADestroy @Drv
                              EXEC sp_OADestroy @objFSO
                  SET @Size = (CONVERT(BIGINT,@Size) / 1048576 )
                  SET @Free = (CONVERT(BIGINT,@Free) / 1048576 )
                  INSERT INTO ##_DriveInfo
                        VALUES (@DriveLetter, @Size, @Free, @Label)
      END
      FETCH NEXT FROM curDriveLetters INTO @DriveLetter
END
CLOSE curDriveLetters
DEALLOCATE curDriveLetters
PRINT ‘Drive information for server ‘ + @@SERVERNAME + ‘.’
PRINT
— Produce report.
SELECT DriveLetter
      , Label
      , FreeSpace AS [FreeSpace MB]
      , (TotalSpace FreeSpace) AS [UsedSpace MB]
      , TotalSpace AS [TotalSpace MB]
      , ((CONVERT(NUMERIC(9,0),FreeSpace) / CONVERT(NUMERIC(9,0),TotalSpace)) * 100) AS [Percentage Free]
FROM ##_DriveInfo
ORDER BY [DriveLetter] ASC  
GO
DROP TABLE ##_DriveSpace
DROP TABLE ##_DriveInfo
Advertisements

Posted on March 31, 2011, in Sql Server 2005, Sql Server 2008, TSQL Tips n Tricks. Bookmark the permalink. 1 Comment.

  1. Hi, thanks a lot for the script, I find it very useful for my daily status reports. I did however noticed that it is slightly buggy when you run it on a Cluster (sql 2008).The bug is:the script doesn't return the correct value for the Label of the shared storage drives. It just repeats the last good value.I tried debugging but couldn't quite get it to work.Any ideas?Thanks a lot!

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: