Reviewing Logical Hard Drive details using T-SQL and OLE Automation Procedures

By:   |   Updated: 2015-02-23   |   Comments (3)   |   Related: More > Database Administration


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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Percy Reyes Percy Reyes is a SQL Server MVP and Sr. Database Administrator focused on SQL Server Internals with over 10+ years of extensive experience managing critical database servers.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2015-02-23

Comments For This Article




Tuesday, March 3, 2015 - 3:44:05 PM - Jack Back To Top (36426)

Excellent  post! I created this rollup query from the Erin Stellato blog post. It work 2008 R2 and greater.  It even shows mount points. The query below is a rollup and uses sys.master_files  which is going away in future versions..

/*
Adapted from: http://sqlperformance.com/2014/12/io-subsystem/proactive-sql-server-health-checks-1
Proactive SQL Server Health Checks, Part 1 : Disk Space
Posted by Erin Stellato on December 15, 2014

Drive Size and Free Space
03/03/2015

*/
USE master
GO
SET NOCOUNT ON
GO

SELECT
  SERVERPROPERTY('SERVERNAME')[Server]
  ,vs.volume_mount_point AS [Drive]
  ,vs.logical_volume_name AS [Drive Name]
  ,(vs.total_bytes/1024/1024/1024) AS [Drive Size GB]
  ,(vs.available_bytes/1024/1024/1024) AS [Drive Free Space GB]
  ,SUM(CASE WHEN f.type = 0 then 1 else 0 END) [Number of Data Files]
  ,SUM(CASE WHEN f.type = 1 then 1 else 0 END) [Number of Logs Files]
  ,CONVERT(VARCHAR(25),GETDATE(),22) [Sample Time]
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) AS vs
GROUP BY vs.volume_mount_point
  ,vs.logical_volume_name
  ,(vs.total_bytes/1024/1024/1024)
  ,(vs.available_bytes/1024/1024/1024)

ORDER BY vs.volume_mount_point;
GO

Tuesday, March 3, 2015 - 7:47:44 AM - Perry Whittle Back To Top (36415)

You're much better off doing this via Powershell or WMIC, you don't seem to make any allowance for systems using mounted volumes, something that's more commonplace these days

Tuesday, March 3, 2015 - 6:18:24 AM - Hany Helmy Back To Top (36414)

Gr8 scripts, very usefull, thank you.














get free sql tips
agree to terms