How much space do my database files have?

Database files can fill up quickly and quietly. By the time someone notices, you’re usually already in trouble. This script gives you a quick view of how much space you’ve actually got left across your database files — run it in the context of the database you want to check.

-- Run in the context of the database you want to check

SELECT 
    @@Servername AS instance_name,
    sd.name AS db_name, 
    sd.recovery_model_desc AS recovery_model,
    sdf.physical_name AS filename,
    sdf.name AS logical_name,
    (FILEPROPERTY(sdf.name, 'IsPrimaryFile')) AS primary_file,
    (FILEPROPERTY(sdf.name, 'IsLogFile')) AS is_log_file,
    CONVERT(DECIMAL(10,2), size/128.0/1024)AS size_GB,
    CONVERT(DECIMAL(10,2), FILEPROPERTY(sdf.name, 'SpaceUsed')/128.0/1024) AS space_used,
    CONVERT(DECIMAL(10,2), size/128.0/1024-(FILEPROPERTY(sdf.name, 'SpaceUsed')/128.0/1024)) AS space_available,
    CONVERT(DECIMAL(10,2), (FILEPROPERTY(sdf.name, 'SpaceUsed')/(size/128.0))/128.0*100) AS pct_used,
    sd.log_reuse_wait_desc
FROM sys.database_files AS sdf
CROSS APPLY
(
    SELECT * FROM sys.databases AS sdf
    WHERE name = DB_NAME()
) AS sd
-- Uncomment to show log files only
--WHERE (FILEPROPERTY(sdf.name, 'IsLogFile')) = 1SQL

Anything showing high pct_used or low space_available needs attention before it becomes an incident. The log_reuse_wait_desc column will tell you why the log isn’t shrinking if that’s your problem.