Find disk space using Stored procedure or TSQL

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)
)