Getting Drive Info Part 1, sp_OA

This blog series will cover several different methods to collect drive information from a Windows server that is running SQL Server. I’ve answered several requests in forums, news groups and twitter in the past about how to get the drive size of all of the hard drives on the server that houses a SQL Server instance. These people found the undocumented system stored procedure xp_fixeddrives which returns a result set of the drives on the server along with free space. They were looking for a way to get the drive size as well. This would be a useful addition to xp_fixeddrives to create a disk growth monitoring system. This blog series will also show methods that are specific to SQL Server 2000 as well as methods that utilize the newer features available in versions 2005 and 2008 in future posts. The techniques presented can be modified to get just about any information about the Windows server that houses the SQL Server instance.

Retrieving Drive Size Using sp_OA

The first method discussed is old to say the least as I have been using it since SQL Server 7.0 and there are still plenty of SQL Server 2000 instances running that can make use of this method. It utilizes the sp_OA suite of stored procedures. I have used this method to gather data for an automated SQL Server status report daily for years on SQL Server 7.0, 2000 and 2005. Although there are reports that the sp_OA stored procedures can have memory problems and are a security risk, I have yet to have an issue. They are also set to be deprecated at some point. These points are something that needs to be considered before using this solution. The sp_OA suite of stored procedures allows the use of COM objects from within Transact-SQL. Yes, in order to get the drive size you need to do a little bit of object oriented programming. Much like xp_cmdshell, this suite of store procedures has security risks. Microsoft recognized this and disabled them by default in SQL Server 2005 and 2008. They need to be enabled by running the script (the following is not required on SQL Server 2000):

-- For SQL Server 2005 and 2008 only
EXECUTE sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE

EXECUTE sp_configure 'Ole Automation Procedures', 1
RECONFIGURE WITH OVERRIDE

If you are really worried about the security aspects of having these stored procedures enabled continuously, the above script could be made part of the entire drive info script. This would enable the store procedures for only as long as necessary to get the drive information. The Ole Automation Procedures configuration option could be disabled at the end of the drive size script.

In order to get the drive size a new instance of the Windows Scripting Host File System Object has to be created. The GetDrive method has to be called and then the property TotalSize is retrieved. Here is the script for the C: drive.

DECLARE @rs INTEGER
      , @fso INTEGER
      , @drv INTEGER
      , @drivesize VARCHAR(20)

-- Create a new Windows Scripting Host File System Object
EXEC @rs = sp_OACreate 'Scripting.FileSystemObject', @fso OUTPUT

-- Get the drive information for the C: drive
IF @rs = 0
  EXEC @rs = sp_OAMethod @fso, 'GetDrive("C:\")', @drv OUTPUT

-- Get the size of this drive
IF @rs = 0
  EXEC @rs = sp_OAGetProperty @drv,'TotalSize', @drivesize OUTPUT
IF @rs<> 0
  SET @drivesize = '0'

-- Clean up
EXEC sp_OADestroy @drv
EXEC sp_OADestroy @fso

SELECT CAST( @drivesize AS FLOAT ) / 1024.0 / 1024.0

The next step in the process is to include xp_fixeddrives to get a list of all the drives on a server and iterate through the result set of drives to get the drive size. A table is populated with all the fixed drive info. This data is iterated and the drive size for each drive is collected from the file system object. Here is the script.

DECLARE @rs INTEGER
      , @fso INTEGER
      , @drv INTEGER
      , @drivesize VARCHAR(20)
      , @drive VARCHAR(255)
      , @GetDrive VARCHAR(255)

DECLARE @DriveInfo TABLE(Drive VARCHAR(255), DriveSize FLOAT, FreeSpace FLOAT)

-- Get all the drives and free space in MB
INSERT INTO @DriveInfo(Drive, FreeSpace) EXECUTE xp_fixeddrives
-- Convert Free Space to GB if applicable to the environment
UPDATE @DriveInfo SET FreeSpace = FreeSpace / 1024.

SELECT TOP 1 @drive = Drive FROM @DriveInfo WHERE DriveSize IS NULL

WHILE @drive IS NOT NULL
BEGIN

  -- Create a new Windows Scripting Host File System Object
  EXEC @rs = sp_OACreate 'Scripting.FileSystemObject', @fso OUTPUT

  -- Get the drive information for the drive
  SELECT @GetDrive = 'GetDrive("'+ @drive + ':\")'
  IF @rs = 0
    EXEC @rs = sp_OAMethod @fso, @GetDrive, @drv OUTPUT

  -- Get the size of this drive
  IF @rs = 0
    EXEC @rs = sp_OAGetProperty @drv,'TotalSize', @drivesize OUTPUT
  IF @rs<> 0
    SET @drivesize = '0'

  UPDATE @DriveInfo SET DriveSize = CAST(@drivesize AS FLOAT) / 1024.0 / 1024.0 WHERE Drive = @drive
  -- Convert Drive Size to GB if applicable to the environment
  UPDATE @DriveInfo SET DriveSize = DriveSize / 1024. WHERE Drive = @drive

  SELECT @drive = NULL
  SELECT TOP 1 @drive = Drive FROM @DriveInfo WHERE DriveSize IS NULL

END

SELECT * FROM @DriveInfo

-- Clean up
EXEC sp_OADestroy @drv
EXEC sp_OADestroy @fso

The data in the @DriveInfo table can be stored permanently in a persisted table for size monitoring and historical reporting.

This concludes the first part of Drive Info series. The sp_OA stored procedures can be used to gather all kinds of information. There are many more properties to look at in the Drive object, go here <add link to Drive docs> for more info. Other COM objects could be instantiated via sp_OA and used to gather server information.

In the next installment of the drive info series, I will show another method supported by SQL Server 2000 and collect the same information using DTS.