Database File Space Utilization Report

/*

===============================================================================

 Title       : Database File Space Utilization Report (All User Databases)

 Author      : [https://www.linkedin.com/in/mschbhanumurthy/]

 Date        : [2025/05/28]

 Description :

    This script generates a consolidated report of space usage for all 

    user databases (excluding system DBs) on the current SQL Server instance.

    For each file (data & log), the following details are provided:

      - Database Name

      - Logical File Name

      - File Type (ROWS or LOG)

      - Total Size (MB)

      - Used Space (MB)

      - Free Space Left (MB)

      - Free Space Percentage (rounded to 2 decimal places)

    Notes:

      - Results are stored in a global temp table: ##DbFileUsage

      - NULL values are avoided using proper FILEPROPERTY handling

      - Final output is ordered by Database and FileType

 Usage       :

    Run this script on any SQL Server instance to monitor and export

    file-level space usage across all non-system databases.

===============================================================================

*/

USE master;

GO

-- Step 1: Create global temp table

IF OBJECT_ID('tempdb..##DbFileUsage') IS NOT NULL

    DROP TABLE ##DbFileUsage;

CREATE TABLE ##DbFileUsage (

    [DatabaseName] NVARCHAR(128),

    [LogicalName] NVARCHAR(128),

    [FileType] NVARCHAR(10),

    [TotalSizeMB] DECIMAL(18,2),

    [UsedSpaceMB] DECIMAL(18,2),

    [SpaceLeftMB] DECIMAL(18,2),

    [FreePercentage] DECIMAL(5,2)

);

-- Step 2: Populate table using sp_MSforeachdb

EXEC sp_MSforeachdb '

IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')

BEGIN

    EXEC(''USE [?];

    INSERT INTO ##DbFileUsage (

        DatabaseName, LogicalName, FileType, TotalSizeMB, UsedSpaceMB, SpaceLeftMB, FreePercentage

    )

    SELECT 

        DB_NAME() AS DatabaseName,

        name AS LogicalName,

        type_desc AS FileType,

        ROUND(CAST(size AS FLOAT) * 8.0 / 1024, 2) AS TotalSizeMB,

        ROUND(CAST(FILEPROPERTY(name, ''''SpaceUsed'''') AS FLOAT) * 8.0 / 1024, 2) AS UsedSpaceMB,

        ROUND((CAST(size AS FLOAT) * 8.0 / 1024) - (CAST(FILEPROPERTY(name, ''''SpaceUsed'''') AS FLOAT) * 8.0 / 1024), 2) AS SpaceLeftMB,

        ROUND(((CAST(size AS FLOAT) - FILEPROPERTY(name, ''''SpaceUsed'''')) / CAST(size AS FLOAT)) * 100.0, 2) AS FreePercentage

    FROM sys.database_files

    WHERE FILEPROPERTY(name, ''''SpaceUsed'''') IS NOT NULL'');

END

';

-- Step 3: View results

SELECT *

FROM ##DbFileUsage

ORDER BY DatabaseName, FileType;