Check Drive Space with T-SQL and PowerShell Updated

As a DBA, it’s often useful to check how much disk space is available on the SQL Server host without remoting into the server. With the help of xp_cmdshell and a little PowerShell, you can query drive capacity and free space directly from T-SQL.

⚠️ Note: xp_cmdshell is disabled by default for security reasons. In this script we enable it, run our check, then disable it again.

---

Quick Check Check Drive Space

/* Enable xp_cmdshell (temporarily) */
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;

EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE WITH OVERRIDE;

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;

/* Build the PowerShell command */
DECLARE @svrName VARCHAR(255);
DECLARE @sql     VARCHAR(400);

-- By default it takes the current server name; you can set a server name as well
SET @svrName = @@SERVERNAME;

SET @sql = 'powershell.exe -c "Get-WmiObject -ComputerName '
         + QUOTENAME(@svrName,'"')
         + ' -Class Win32_Volume -Filter ''DriveType = 3'' | '
         + 'select name,capacity,freespace | '
         + 'foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"';

/* Temp table for raw output */
CREATE TABLE #output (line VARCHAR(255));

/* Call PowerShell via xp_cmdshell */
INSERT #output
EXEC xp_cmdshell @sql;

/* Parse into a friendly result set */
CREATE TABLE #DriveSpace
(
  ServerName        SYSNAME,
  PhysicalName      SYSNAME,
  [Capacity(GB)]    NVARCHAR(20),
  [Drive FreeSpace(GB)] NVARCHAR(20)
);

INSERT INTO #DriveSpace (ServerName, PhysicalName, [Capacity(GB)], [Drive FreeSpace(GB)])
SELECT
  @@SERVERNAME,
  RTRIM(LTRIM(SUBSTRING(line, 1, CHARINDEX('|', line) - 1))) AS PhysicalName,
  ROUND(CAST(RTRIM(LTRIM(SUBSTRING(
           line,
           CHARINDEX('|', line) + 1,
           (CHARINDEX('%', line) - 1) - CHARINDEX('|', line)
       ))) AS FLOAT) / 1024, 0) AS [Capacity(GB)],
  ROUND(CAST(RTRIM(LTRIM(SUBSTRING(
           line,
           CHARINDEX('%', line) + 1,
           (CHARINDEX('*', line) - 1) - CHARINDEX('%', line)
       ))) AS FLOAT) / 1024, 0) AS [Drive FreeSpace(GB)]
FROM #output
WHERE line LIKE '[A-Z][:]%'
ORDER BY PhysicalName;

/* Results */
SELECT * FROM #DriveSpace;

/* Turn off xp_cmdshell again */
EXEC sp_configure 'xp_cmdshell', 0;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE WITH OVERRIDE;

/* Cleanup */
DROP TABLE #output;
DROP TABLE #DriveSpace;

Optional extras (keep original behavior, add safety/logging)

A) Safety wrapper (ensures xp_cmdshell is turned back off even on error)

DECLARE @xpWasEnabled BIT =
  (SELECT CAST(value_in_use AS BIT) FROM sys.configurations WHERE name = 'xp_cmdshell');

BEGIN TRY
  IF @xpWasEnabled = 0
  BEGIN
    EXEC sp_configure 'show advanced options', 1; RECONFIGURE WITH OVERRIDE;
    EXEC sp_configure 'xp_cmdshell', 1;           RECONFIGURE WITH OVERRIDE;
  END

  -- >>> your command + parsing here (the middle of the script) <<<

END TRY
BEGIN CATCH
  -- Bubble the error after cleanup
  DECLARE @msg NVARCHAR(4000) = ERROR_MESSAGE();

  IF @xpWasEnabled = 0
  BEGIN
    EXEC sp_configure 'xp_cmdshell', 0;
    EXEC sp_configure 'show advanced options', 0; RECONFIGURE WITH OVERRIDE;
  END

  THROW; -- rethrow original error
END CATCH;

-- Final ensure off if we enabled it
IF @xpWasEnabled = 0
BEGIN
  EXEC sp_configure 'xp_cmdshell', 0;
  EXEC sp_configure 'show advanced options', 0; RECONFIGURE WITH OVERRIDE;
END

B) (Optional) Log the results to a table for trending

-- One-time setup in a DBA database
-- CREATE TABLE DBA.dbo.DriveSpaceLog
-- (
--   server_name SYSNAME,
--   physical_name SYSNAME,
--   capacity_gb  DECIMAL(18,2),
--   free_gb      DECIMAL(18,2),
--   pct_free     DECIMAL(5,2),
--   captured_utc DATETIME2(0) NOT NULL DEFAULT SYSUTCDATETIME()
-- );

INSERT DBA.dbo.DriveSpaceLog (server_name, physical_name, capacity_gb, free_gb, pct_free)
SELECT
  @@SERVERNAME,
  PhysicalName,
  TRY_CONVERT(DECIMAL(18,2), [Capacity(GB)]),
  TRY_CONVERT(DECIMAL(18,2), [Drive FreeSpace(GB)]),
  TRY_CONVERT(DECIMAL(5,2),
    CASE WHEN TRY_CONVERT(DECIMAL(18,2), [Capacity(GB)]) > 0
         THEN 100.0 * TRY_CONVERT(DECIMAL(18,2), [Drive FreeSpace(GB)])
                    / TRY_CONVERT(DECIMAL(18,2), [Capacity(GB)])
    END)
FROM #DriveSpace;

Alternative Methods (No xp_cmdshell)

If your environment prefers to avoid xp_cmdshell, here are two lightweight alternatives you can add to your toolbox. They won’t replace your original script — but they give you safer options if security policies or auditors don’t allow xp_cmdshell.

---

Option 1 — Pure T-SQL with sys.dm_os_volume_stats

This DMV shows space for volumes that contain SQL Server database files.
No external calls, no PowerShell, just straight T-SQL.

SELECT DISTINCT
    @@SERVERNAME                         AS server_name,
    vs.volume_mount_point,
    vs.logical_volume_name,
    vs.file_system_type,
    CAST(vs.total_bytes     / 1073741824.0 AS DECIMAL(18,2)) AS total_gb,
    CAST(vs.available_bytes / 1073741824.0 AS DECIMAL(18,2)) AS free_gb,
    CAST(100.0 * vs.available_bytes / NULLIF(vs.total_bytes,0) AS DECIMAL(5,2)) AS pct_free
FROM sys.master_files AS mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS vs
ORDER BY vs.volume_mount_point;

👉 Best when you just need to know if SQL’s own drives are filling up.

---

Option 2 — SQL Agent PowerShell Job Step

If you want all volumes (not just SQL’s), you can run a PowerShell snippet from a SQL Agent job step — no xp_cmdshell required.

Get-CimInstance -ClassName Win32_Volume -Filter "DriveType=3" |
 Select-Object Name,
               @{Name='Capacity(GB)'; Expression={[math]::Round($_.Capacity/1GB,2)}},
               @{Name='FreeSpace(GB)'; Expression={[math]::Round($_.FreeSpace/1GB,2)}},
               @{Name='PctFree'; Expression={[math]::Round(100*($_.FreeSpace/[double]$_.Capacity),2)}}

• Schedule it as a PowerShell job step in SQL Agent.
• If you want trending, pipe the results into sqlcmd to insert into a table (e.g., DBA.dbo.DriveSpaceLog).

---

Option 3 — Quick & Dirty xp_fixeddrives

This one’s undocumented but widely used. It only shows free space (MB), no totals:

EXEC master..xp_fixeddrives;

Summary

• The script above uses PowerShell via xp_cmdshell to list each fixed volume’s total capacity and free space, all from inside SSMS.
• You can add an optional safety wrapper so xp_cmdshell is turned back off even if something errors, and you can also log results into a table for trending reports.
• If your environment prefers to avoid xp_cmdshell, keep this script as-is but add an Alternative Methods section:
  • sys.dm_os_volume_stats: pure T-SQL, safe, and limited to drives hosting SQL database files.
  • PowerShell job step: runs as a SQL Agent PowerShell job, gives full host coverage without enabling xp_cmdshell.
  • xp_fixeddrives: quick free-space check only, useful for a fast sanity check.

This way, you have a battle-tested script for SSMS, plus practical alternatives for shops with tighter security policies.