Reviewing Logical Hard Drive details using T-SQL and OLE Automation Procedures
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.
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]()
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 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:
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.
- Read these additional articles related to this tip:
- Ole Automation Procedures Server Configuration Option
- sp_OACreate (Transact-SQL)
- sp_configure (Transact-SQL)
- Check out these additional MSSQLTips.com resources:
Last Updated: 2015-02-23
About the author
View all my tips