Get Logical Hard Drive details with T-SQL and OLE Automation

Problem

One of the most important SQL Server DBA tasks is checking logical hard drive details such as the total capacity, free disk space, and used space by the SQL Server data and log files. In this tip we look at a method to view this information in a faster and simpler way.

Solution

We have some options when we need to check the logical disks installed in the server and/or the free disk space for each drive. For example, we can use Windows Explorer and check capacity, file system type, free space, and more. Another way is use the extended stored procedure xp_fixeddrives directly from SQL Server and check the drive and the free space on each drive. What about if we want to create some customized disk free space alerts in SQL Server? Or we need to create automatic daily reports with hard disk information from SQL Server which includes more details? Or what about if we need a new way to check more information than is provided by xp_fixeddrives?

In today’s tip I will explain a new stored procedure sp_fixeddrives and the table-value function ufn_LogicalDiskDrives I have created to get the following information I consider that most DBAs need to check on a daily basis:

  • Drive Letter
  • Volume Name
  • File System type
  • Capacity in GB
  • Free Space in GB
  • Free Space as a Percentage
  • Total Data File Size
  • Total Log File Size

It is important to note that the name of this new stored procedure starts with “sp_” that is “sp_fixeddrives” and it is different than “xp_fixeddrives” which starts with “xp_” (system extended stored procedure).

The new Table-Value Function ufn_LogicalDiskDrives gets hard drive properties calling OLE Automation procedures sp_OACreate and sp_OAGetProperty. The T-SQL creation code is the following:

USE [master]
GO
CREATE FUNCTION [dbo].[ufn_LogicalDiskDrives]()
RETURNS @DriveList Table
  (
   [DriveLetter]  CHAR(1)
  ,[VolumeName]  VARCHAR(255)
  ,[FileSystem]  VARCHAR(50)
  ,[TotalSize] BIGINT
  ,[AvailableSpace]   BIGINT
  ,[FreeSpace] BIGINT 
  )
AS
BEGIN
--Written by Percy Reyes
  DECLARE @DriveLetter_ASCII_Code  INT
  DECLARE @FileSystemInstance  INT
  DECLARE @DriveCount  INT
  DECLARE @DriveCollection  INT
  DECLARE @Drive  INT
  DECLARE @Property  NVARCHAR(100)    
  DECLARE @DriveLetter  VARCHAR(1)  
  DECLARE @TotalSize BIGINT
  DECLARE @AvailableSpace  BIGINT
  DECLARE @FreeSpace  BIGINT  
  DECLARE @FileSystem VARCHAR(128)
  DECLARE @VolumeName VARCHAR(128)
  DECLARE @IsReady VARCHAR(5)
  
  --Creating a File System Object for getting files or disk info.
  exec sp_OACreate 'Scripting.FileSystemObject', @FileSystemInstance OUT
  --Getting the collection of drives
  exec sp_OAGetProperty @FileSystemInstance,'Drives', @DriveCollection OUT
  --Getting the count of drives from collection
  exec sp_OAGetProperty @DriveCollection,'Count', @DriveCount OUT
  --starting from Drive "A" (ASCII 65) 
  SET @DriveLetter_ASCII_Code = 65
  --to "Z" (ASCII 90)
  WHILE @DriveLetter_ASCII_Code <= 90
  BEGIN
  ---Creating the instance drive from Drive Collection 
        SET @Property = 'item("'+CHAR(@DriveLetter_ASCII_Code)+'")'
        exec sp_OAGetProperty @DriveCollection,@Property, @Drive OUT
  -- Getting the drive letter property
        exec sp_OAGetProperty @Drive,'DriveLetter', @DriveLetter OUT
  IF @DriveLetter = CHAR(@DriveLetter_ASCII_Code)
        BEGIN   
        -- Getting more properties from each drive   
     exec sp_OAGetProperty @Drive,'VolumeName', @VolumeName OUT 
     exec sp_OAGetProperty @Drive,'FileSystem', @FileSystem OUT
     exec sp_OAGetProperty @Drive,'TotalSize', @TotalSize OUT 
              exec sp_OAGetProperty @Drive,'AvailableSpace', @AvailableSpace OUT
              exec sp_OAGetProperty @Drive,'FreeSpace', @FreeSpace OUT          
              exec sp_OAGetProperty @Drive,'IsReady'  , @IsReady OUT; 
            
   IF @IsReady='True'
     INSERT INTO @DriveList ( [DriveLetter],[TotalSize], [AvailableSpace],[FreeSpace],[FileSystem] ,[VolumeName] )
     VALUES( @DriveLetter,@TotalSize,@AvailableSpace,@FreeSpace,@FileSystem,@VolumeName)
                             
            END
   -- forward next drive
            SET @DriveLetter_ASCII_Code = @DriveLetter_ASCII_Code +1
  END 
  
  EXEC sp_OADestroy @Drive 
  EXEC sp_OADestroy @DriveCollection
  RETURN
END

We will need to enable the “Ole Automation Procedures” configuration setting, so that we can use the new function otherwise it will fail.

EXECUTE sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXECUTE sp_configure 'Ole Automation Procedures', 1
RECONFIGURE WITH OVERRIDE
GO

Testing the new function ufn_LogicalDiskDrives:

SELECT * FROM [dbo].[ufn_LogicalDiskDrives]()

Testing the new function ufn_LogicalDiskDrives

The new stored procedure sp_fixeddrives will be marked as a system object, so that we can call it from any database. This stored procedure will use the results returned by the function ufn_LogicalDiskDrives and add the Total Data File Size and Total Log File Size information in the final result. Here is the T-SQL creation code:

select ISNULL(DF.DriveLetter, LF.DriveLetter) DriveLetter,  DF.DataSize_GB, LF.LogSize_GB from  (
   select left(physical_name, 1) DriveLetter, CAST(sum(size)/128.0/1024 AS DECIMAL(10,2)) DataSize_GB
   from sys.master_files
   where type_desc='ROWS'
   group by left(physical_name, 1), type_desc 
   ) DF
  full outer join 
  (
   select left(physical_name, 1) DriveLetter, CAST(sum(size)/128.0/1024  AS DECIMAL(10,2)) LogSize_GB
   from sys.master_files 
   where type_desc='LOG'
   group by left(physical_name, 1), type_desc
   ) LF
  on DF.DriveLetter=LF.DriveLetter

The result is showed here:

Finally, here the T-SQL creation code of the stored procedure sp_fixeddrives

Finally, here is the T-SQL creation code of the stored procedure sp_fixeddrives.

USE [master]
GO
CREATE PROC dbo.sp_fixeddrives
WITH ENCRYPTION
AS
BEGIN
 SET NOCOUNT ON 
 -----Reporting Drive details ....
 select LDD.DriveLetter ,[VolumeName] ,[FileSystem]  
 ,CAST(TotalSize/1024.0/1024/1024.0 AS DECIMAL(10,2)) [Capacity_GB]
 ,CAST(FreeSpace/1024.0/1024/1024.0 AS DECIMAL(10,2)) [FreeSpace_GB]
 ,CAST((CAST(FreeSpace/1024.0/1024/1024.0 AS DECIMAL(10,2))/CAST(TotalSize/1024.0/1024/1024.0 AS DECIMAL(10,2)) ) *100 AS DECIMAL(10,2)) [Free %]
 , ISNULL( DBFiles.DataSize_GB,0) DataSize_GB,ISNULL( DBFiles.LogSize_GB,0) LogSize_GB
 from master.[dbo].[ufn_LogicalDiskDrives]() LDD
 full outer join 
 (
 select ISNULL(DF.DriveLetter, LF.DriveLetter) DriveLetter,  DF.DataSize_GB, LF.LogSize_GB from
  (
   select left(physical_name, 1) DriveLetter, CAST(sum(size)/128.0/1024 AS DECIMAL(10,2)) DataSize_GB
   from sys.master_files
   where type_desc='ROWS'
   group by left(physical_name, 1), type_desc 
   ) DF
  full outer join 
  (
   select left(physical_name, 1) DriveLetter, CAST(sum(size)/128.0/1024  AS DECIMAL(10,2)) LogSize_GB
   from sys.master_files 
   where type_desc='LOG'
   group by left(physical_name, 1), type_desc
   ) LF
  on DF.DriveLetter=LF.DriveLetter
  
 )  DBFiles
 ON DBFiles.DriveLetter =LDD.DriveLetter
 SET NOCOUNT OFF
END

We will mark the stored procedure as a system object:

USE [master]
GO
EXEC sys.sp_MS_marksystemobject 'sp_fixeddrives'

Here is the output after executing the code:

EXEC sp_fixeddrives

We will mark the stored procedure as a system object

As you can see this new stored procedure sp_fixeddrives is very practical and useful to check logical hard disk information. Let me know if you have any questions or comments.

Next Steps

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *