Introduction
xp_fixeddrives only provide how much disk space is free on each drive but not the maximum space of the drive & percentage of free space. If you need to find out Drive Space for SQL server with Drive Space, Free space and percentage of free space then we need to create a stored procedure ‘sp_diskspace‘.
Ole Automation Procedures
But !!!! before that, you need to enable Ole Automation Procedures feature. Ole Automation Procedures feature must be disabled unless specifically required and approved. To more about visit: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/ole-automation-procedures-server-configuration-option?view=sql-server-2017
To enable Ole Automation Procedures feature we may follow below steps
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Ole Automation Procedures', 1; GO RECONFIGURE; GO

Ole Automation Procedures is enabled.
sp_diskspace
Now we will create ‘sp_diskspace’ with the below script.
USE master GO -- Create the stored procedure CREATE PROCEDURE sp_diskspace AS /* Displays the free space, free space percentage, plus total drive size for a server */ SET NOCOUNT ON DECLARE @hr INT DECLARE @fso INT DECLARE @drive CHAR(1) DECLARE @odrive INT DECLARE @TotalSize VARCHAR(20) DECLARE @MB BIGINT SET @MB = 1048576 -- Create a temporary table to store drive information CREATE TABLE #drives ( drive CHAR(1) PRIMARY KEY, FreeSpace INT NULL, TotalSize INT NULL ) -- Retrieve drive free space using xp_fixeddrives INSERT #drives(drive, FreeSpace) EXEC master.dbo.xp_fixeddrives -- Create a FileSystemObject instance EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @fso OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso -- Cursor to iterate over drives DECLARE dcur CURSOR LOCAL FAST_FORWARD FOR SELECT drive FROM #drives ORDER BY drive OPEN dcur FETCH NEXT FROM dcur INTO @drive WHILE @@FETCH_STATUS = 0 BEGIN -- Get Drive object for the current drive EXEC @hr = sp_OAMethod @fso, 'GetDrive', @odrive OUT, @drive IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso -- Get the total size of the drive EXEC @hr = sp_OAGetProperty @odrive, 'TotalSize', @TotalSize OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive -- Update the temporary table with the total size in MB UPDATE #drives SET TotalSize = @TotalSize / @MB WHERE drive = @drive FETCH NEXT FROM dcur INTO @drive END CLOSE dcur DEALLOCATE dcur -- Clean up the FileSystemObject instance EXEC @hr = sp_OADestroy @fso IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso -- Select the drive information from the temporary table SELECT drive, FreeSpace AS 'Free(MB)', TotalSize AS 'Total(MB)', CAST((FreeSpace / (TotalSize * 1.0)) * 100.0 AS INT) AS 'Free(%)' FROM #drives ORDER BY drive -- Drop the temporary table DROP TABLE #drives -- End the stored procedure RETURN GO
This will execute the stored procedure and display the free space, free space percentage, and total drive size for each drive on the server.
exec sp_diskspace
The result will be displayed like below snapshot.

Same output by a script
Now if you don’t want all this HUNGAMA!! of creating store procedures and enabling Ole Automation Procedures feature then another simple solution is waiting for you. We can also get our desire result by running a simple script also. Simply run the below script. But the disadvantage is that below script will display only that disk status which contains only SQL Server files/data.
-- Retrieve SQL Server drive information SELECT DISTINCT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS [Server], -- Server name volume_mount_point AS [Disk], -- Drive letter or mount point file_system_type AS [File System], -- File system type logical_volume_name AS [Logical Drive Name], -- Logical drive name total_bytes/1073741824.0 AS [Total Size in GB], -- Total size in GB available_bytes/1073741824.0 AS [Available Size in GB], -- Available size in GB (available_bytes*100.0)/total_bytes AS [Space Free %] -- Free space percentage FROM sys.master_files CROSS APPLY sys.dm_os_volume_stats(database_id, file_id) UNION -- Retrieve non-SQL Server drive information SELECT DISTINCT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS [Server], -- Server name volume_mount_point AS [Disk], -- Drive letter or mount point file_system_type AS [File System], -- File system type NULL AS [Logical Drive Name], -- No logical drive name for non-SQL Server drives total_bytes/1073741824.0 AS [Total Size in GB], -- Total size in GB available_bytes/1073741824.0 AS [Available Size in GB], -- Available size in GB (available_bytes*100.0)/total_bytes AS [Space Free %] -- Free space percentage FROM sys.dm_os_volume_stats(NULL, NULL) WHERE volume_mount_point NOT IN ( SELECT DISTINCT volume_mount_point FROM sys.master_files CROSS APPLY sys.dm_os_volume_stats(database_id, file_id) )

Hasan
Good
We expected more..!!! 🙂
Sanjay Humania
Thanks, dude, keep visiting us daily !!
Thanks for your precious comment.
Amit
Helpful
OTI
Thanks