Monitoring storage consumption is a routine operational task. SQL Server exposes several way to inspect disk space, ranging from quick checks to more complete inventories that include total capacity.

Quicck check: xp_fixeddrives (free space only)

xp_fixeddrives shows the current free space (in MB) for fixed drives visible to SQL Server.

-- Minimal view of free space by drive letter (MB)
EXEC master.sys.xp_fixeddrives;

A formatted view in GB:

DECLARE @drives TABLE (
    drive     char(1),
    free_mb   int
);

INSERT @drives
EXEC master.sys.xp_fixeddrives;

SELECT
    drive                        AS Drive,
    CONVERT(decimal(12,2), free_mb / 1024.0) AS Free_GB
FROM @drives
ORDER BY drive;

Limitations:

• Does not return total disk capacity, only free space.
• Only reports drives that SQL Server can access.

Total and free space via sys.dm_os_volume_stats

For SQL Server 2008 R2 SP1 and newer, sys.dm_os_volume_stats exposes total and available bytes for volumes that host SQL Server database files.

-- Aggregate total/used/free space per volume hosting SQL Server files
WITH vols AS (
    SELECT
        REPLACE(vs.volume_mount_point, ':\', '') AS drive_key,
        vs.total_bytes                           AS total_bytes,
        vs.available_bytes                       AS free_bytes
    FROM sys.master_files AS mf
    CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS vs
)
SELECT
    drive_key                                    AS Drive,
    CONVERT(decimal(18,2), SUM(total_bytes) / 1024.0 / 1024 / 1024)            AS Total_GB,
    CONVERT(decimal(18,2), SUM(total_bytes - free_bytes) / 1024.0 / 1024 / 1024) AS Used_GB,
    CONVERT(decimal(18,2), SUM(free_bytes) / 1024.0 / 1024 / 1024)              AS Free_GB,
    CONVERT(decimal(18,2), 100.0 * SUM(free_bytes) / NULLIF(SUM(total_bytes), 0)) AS Free_Pct
FROM vols
GROUP BY drive_key
ORDER BY drive_key;

Notes:

• Requires SQL Server 2008 R2 SP1+.
• Only shows volumes that contain SQL Server database files; drives with no SQL files are not included.

Persisted inventory for older versions: combine xp_fixeddrives and xp_cmdshell

On versions where sys.dm_os_volume_stats is unavailable or when you want a complete drive inventory (including drives without SQL files), you can persist results and compute total size using fsutil via xp_cmdshell.

The following scripts create a table and three stored procedures:

• usp_refresh_drive_free_mb: capture free space using xp_fixeddrives
• usp_populate_drive_capacity_mb: compute total capacity via fsutil volume diskfree
• usp_report_drive_space: refresh and return a combined view

USE [monitor];
GO

-- Inventory table
IF OBJECT_ID('dbo.host_drive_inventory') IS NULL
BEGIN
    CREATE TABLE dbo.host_drive_inventory
    (
        drive_letter  nvarchar(10)  NOT NULL CONSTRAINT PK_host_drive_inventory PRIMARY KEY,
        capacity_mb   bigint        NOT NULL CONSTRAINT DF_host_drive_inventory_capacity_mb DEFAULT (0),
        free_mb       bigint        NOT NULL CONSTRAINT DF_host_drive_inventory_free_mb     DEFAULT (0),
        notes         nvarchar(400) NOT NULL CONSTRAINT DF_host_drive_inventory_notes       DEFAULT (N''),
        last_checked  datetime2     NOT NULL CONSTRAINT DF_host_drive_inventory_checked     DEFAULT (sysdatetime())
    );
END
GO

-- 1) Refresh free space using xp_fixeddrives
CREATE OR ALTER PROCEDURE dbo.usp_refresh_drive_free_mb
AS
BEGIN
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    DECLARE @free TABLE (letter nvarchar(10), free_mb int);
    INSERT @free
    EXEC master.sys.xp_fixeddrives;

    MERGE dbo.host_drive_inventory AS t
    USING (SELECT letter, free_mb FROM @free) AS s
      ON t.drive_letter = s.letter
    WHEN MATCHED THEN
        UPDATE
            SET t.free_mb = s.free_mb,
                t.last_checked = sysdatetime()
    WHEN NOT MATCHED THEN
        INSERT (drive_letter, free_mb)
        VALUES (s.letter, s.free_mb);
END
GO

-- 2) For drives with unknown capacity, enable xp_cmdshell temporarily and query fsutil
CREATE OR ALTER PROCEDURE dbo.usp_populate_drive_capacity_mb
AS
BEGIN
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    IF NOT EXISTS (SELECT 1 FROM dbo.host_drive_inventory WHERE capacity_mb = 0)
        RETURN;

    -- Enable xp_cmdshell (temporarily)
    EXEC sys.sp_configure N'show advanced options', 1;
    RECONFIGURE WITH OVERRIDE;
    EXEC sys.sp_configure N'xp_cmdshell', 1;
    RECONFIGURE WITH OVERRIDE;

    DECLARE @drv  nvarchar(10),
            @cmd  nvarchar(4000);

    DECLARE c CURSOR LOCAL FAST_FORWARD FOR
        SELECT drive_letter
        FROM dbo.host_drive_inventory
        WHERE capacity_mb = 0;

    OPEN c;
    FETCH NEXT FROM c INTO @drv;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @cmd = N'fsutil volume diskfree ' + @drv + N':';

        CREATE TABLE #fsutil_out (line nvarchar(4000));
        INSERT #fsutil_out
        EXEC master.sys.xp_cmdshell @cmd;

        DECLARE @cap_mb bigint;
        -- Parse the "Total # of bytes" line from fsutil output
        SELECT TOP (1)
               @cap_mb = TRY_CONVERT(bigint,
                         REPLACE(RIGHT(line, LEN(line) - CHARINDEX(':', line)), ' ', '')) / 1024 / 1024
        FROM #fsutil_out
        WHERE line LIKE '%Total # of bytes%';

        IF @cap_mb IS NOT NULL
        BEGIN
            UPDATE dbo.host_drive_inventory
            SET capacity_mb = @cap_mb
            WHERE drive_letter = @drv;
        END

        DROP TABLE #fsutil_out;
        FETCH NEXT FROM c INTO @drv;
    END

    CLOSE c;
    DEALLOCATE c;

    -- Disable xp_cmdshell again
    EXEC sys.sp_configure N'xp_cmdshell', 0;
    RECONFIGURE WITH OVERRIDE;
    EXEC sys.sp_configure N'show advanced options', 0;
    RECONFIGURE WITH OVERRIDE;
END
GO

-- 3) Unified view: refresh and return totals/used/free with percentages
CREATE OR ALTER PROCEDURE dbo.usp_report_drive_space
AS
BEGIN
    SET NOCOUNT ON;

    EXEC dbo.usp_refresh_drive_free_mb;
    EXEC dbo.usp_populate_drive_capacity_mb;

    SELECT
        drive_letter AS Drive,
        CONVERT(decimal(18,2), capacity_mb / 1024.0)                   AS Total_GB,
        CONVERT(decimal(18,2), (capacity_mb - free_mb) / 1024.0)       AS Used_GB,
        CONVERT(decimal(18,2), free_mb / 1024.0)                       AS Free_GB,
        CONVERT(decimal(18,2), 100.0 * free_mb / NULLIF(capacity_mb,0)) AS Free_Pct,
        last_checked
    FROM dbo.host_drive_inventory
    ORDER BY drive_letter;
END
GO

-- Example usage
EXEC dbo.usp_report_drive_space;

Behavioral notes:

• The total capacity is only fetched for drives with capacity_mb = 0 to minimize the time xp_cmdshell is enabled.
• This can be scheduled via SQL Server Agent to keep the invantory fresh.

Avoiding RECONFIGURE errors inside user transactions

If you enable xp_cmdshell programmatically while a user transaction is open, RECONFIGURE fails with error 574 (CONFIG statements cannot be used in user transactions). One approach is to run the configuration in dynamic SQL that starts with a COMMIT; capture and ignore the "no corresponding BEGIN TRANSACTION" error if no transaction is open.

DECLARE @cfg nvarchar(2000) = N'
COMMIT;
EXEC sys.sp_configure ''show advanced options'', 1;
RECONFIGURE WITH OVERRIDE;
EXEC sys.sp_configure ''xp_cmdshell'', 1;
RECONFIGURE WITH OVERRIDE;';

BEGIN TRY
    EXEC (@cfg);
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() NOT IN (3902)  -- 3902: COMMIT without active transaction
        THROW;
END CATCH;

Alternatively, execute configuration on a separate connection that is guaranteed to be autocommit.

Querying additional disk/NTFS details via xp_cmdshell

Once xp_cmdshell is enibled, you can retrieve filesystem characteristics such as sector and cluster size.

-- Enable xp_cmdshell
EXEC sys.sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sys.sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

-- Example: sector and cluster size for drive D:
EXEC master.sys.xp_cmdshell 'fsutil fsinfo ntfsinfo D: | findstr /C:"Bytes Per Sector" /C:"Bytes Per Cluster"';

-- Disable xp_cmdshell
EXEC sys.sp_configure 'xp_cmdshell', 0;
RECONFIGURE;
EXEC sys.sp_configure 'show advanced options', 0;
RECONFIGURE;

On non-English systems, adjust the filter strings passed to find/findstr based on localized output from fsutil.