Monday, December 1, 2025

SQL Server: Identify Data Files with Less Than 10% Free Space

 

In database administration, monitoring storage utilization is critical to avoid performance degradation, blocking issues, and unexpected outages. One of the most important checks for a DBA is identifying data files that are running low on free space.

In this blog, we’ll look at a practical approach to identify database files in Microsoft SQL Server that have less than 10% free space using a dynamic query across all databases.

Why Monitoring File Space Matters

When data files run out of space, it can lead to:

  • Transaction failures
  • Slow query performance
  • Blocking and deadlocks
  • Application downtime
  • Emergency auto-growth events (which are expensive)

Proactive monitoring helps DBAs:

  • Plan storage expansion
  • Avoid emergency outages
  • Optimize database performance

Solution Overview

The following script:

  • Scans all databases
  • Collects data file size and free space
  • Stores results in a temporary table
  • Filters files with less than 10% free space

SQL Query to Identify Low-Space Data Files


CREATE TABLE #FileSize
(
    dbName NVARCHAR(128), 
    FileName NVARCHAR(128), 
    type_desc NVARCHAR(128),
    CurrentSizeMB DECIMAL(10,2), 
    FreeSpaceMB DECIMAL(10,2),
    FreeSpacePercentage DECIMAL(10,2)
);

INSERT INTO #FileSize
(dbName, FileName, type_desc, CurrentSizeMB, FreeSpaceMB, FreeSpacePercentage)
EXEC sp_msforeachdb 
'
USE [?];

SELECT 
    DB_NAME() AS DbName,
    name AS FileName,
    type_desc,
    CAST((size * 8.0 / 1024) AS DECIMAL(10, 2)) AS CurrentSizeMB,
    CAST(((size - FILEPROPERTY(name, ''SpaceUsed'')) * 8.0 / 1024) AS DECIMAL(10, 2)) AS FreeSpaceMB,
    CAST((((size - FILEPROPERTY(name, ''SpaceUsed'')) * 100.0) / size) AS DECIMAL(10, 2)) AS FreeSpacePercentage
FROM sys.database_files
WHERE type IN (0,1);
';

SELECT * 
FROM #FileSize
WHERE dbName NOT IN ('distribution', 'master', 'model', 'msdb')
AND FreeSpacePercentage < 10;

DROP TABLE #FileSize;


How This Query Works

1. Temporary Table Creation

A staging table #FileSize is created to store results from all databases.

2. Iterating Through All Databases

We use:

sp_msforeachdb

This stored procedure loops through every database in the instance and executes the query.

3. Collecting File Metrics

From:

sys.database_files

We extract:

  • File name
  • File type (data/log)
  • Total size
  • Used space
  • Free space percentage

4. Filtering Critical Files

We exclude system databases:

  • master
  • model
  • msdb
  • distribution

Then we filter:

FreeSpacePercentage < 10

This highlights files that are running critically low on space.

Output Example

dbNameFileNametype_descCurrentSizeMBFreeSpaceMBFreeSpacePercentage
SalesDBSales_DataROWS2048012005.8
HRDBHR_DataROWS102408007.9

Best Practices

1. Set Alerts

Integrate this query into SQL Agent jobs for proactive alerting.

2. Avoid Frequent Auto-Growth

Instead of relying on auto-growth, plan capacity ahead of time.

3. Separate Data and Log Monitoring

Data files and log files behave differently—monitor both independently.

4. Use Performance Dashboards

Visualize file growth trends using tools like:

  • Power BI
  • Grafana
  • SSRS

Enhancements You Can Add

If you want to extend this script further:

  • Add disk-level free space checks
  • Include auto-growth settings
  • Identify top growing databases
  • Export results to email alerts

Final Thoughts

In Microsoft SQL Server environments, storage issues are one of the most common causes of production incidents. This simple query helps DBAs proactively identify databases running low on space and take corrective action before users are impacted.