Thursday, March 31, 2011

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

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!

    ReplyDelete

All suggestions are welcome