File Group Alert

SQL Server Database Free Space Monitoring and Alert Notification

Purpose

To proactively monitor free space usage for all user databases across a SQL Server instance and send email alerts when the available free space falls below 20%.

Scope

This SOP applies to all SQL Server instances maintained by the DBA team. It ensures that storage issues are detected early to prevent database unavailability or performance degradation.

Responsibilities

DBA Team: Responsible for deploying, maintaining, and monitoring this job.
On-call DBA: Responds to email alerts and takes necessary actions.

Pre-requisites

Database Mail must be configured and working on the SQL Server instance.
SQL Server Agent must be running.
The job must be scheduled according to operational requirements (e.g., daily or hourly).

Setup Instructions

Step 1: Create the Monitoring Job

Open SQL Server Management Studio (SSMS).
Connect to the target SQL Server instance.
Navigate to SQL Server Agent > Jobs.
Right-click Jobs, select New Job.
Set the Job Name to: Monitor Database Free Space.

Step 2: Add Job Step

Step Name: Check Free Space and Alert
Type: Transact-SQL script (T-SQL)
Database: msdb (or any user DB)
Command: Paste the T-SQL script below

Step 3: Add Schedule

Schedule the job to run hourly or daily during off-peak hours.

Step 4: Configure Notifications (Optional)

Configure the job to notify operators upon failure, if required.

T-SQL Script

-- Monitor database free space and send email alert if any database is below 20% free space

DECLARE @threshold INT = 20;
DECLARE @tableHTML NVARCHAR(MAX);

;WITH db_size AS (
    SELECT
        DB_NAME(database_id) AS DatabaseName,
        SUM(size) * 8.0 / 1024 AS TotalSizeMB
    FROM sys.master_files
    WHERE type_desc = 'ROWS' AND database_id NOT IN (1, 2, 3, 4)
    GROUP BY database_id
),
free_space AS (
    SELECT
        DB_NAME() AS DatabaseName,
        name AS FileName,
        size * 8.0 / 1024 AS FileSizeMB,
        size * 8.0 / 1024 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) * 8.0 / 1024 AS FreeSpaceMB
    FROM sys.database_files
),
db_combined AS (
    SELECT
        d.DatabaseName,
        d.TotalSizeMB,
        f.FileName,
        f.FreeSpaceMB,
        CAST((f.FreeSpaceMB / d.TotalSizeMB) * 100 AS DECIMAL(5,2)) AS FreePercent
    FROM db_size d
    JOIN free_space f ON d.DatabaseName = f.DatabaseName
)
SELECT *
INTO #Results
FROM db_combined
WHERE FreePercent < @threshold;

IF EXISTS (SELECT 1 FROM #Results)
BEGIN
    SET @tableHTML = 
        N'<H3>Database Free Space Alert - Less than ' + CAST(@threshold AS NVARCHAR) + '%</H3>' +
        N'<table border="1" cellpadding="5" cellspacing="0">' +
        N'<tr><th>Database</th><th>File</th><th>Total Size (MB)</th><th>Free Space (MB)</th><th>Free %</th></tr>' +
        (
            SELECT 
                td.DatabaseName AS [td/@style='''padding:5px;'''],
                td.FileName,
                td.TotalSizeMB,
                td.FreeSpaceMB,
                td.FreePercent
            FROM #Results td
            FOR XML PATH('tr'), TYPE
        ).value('.', 'NVARCHAR(MAX)') +
        N'</table>';

    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'SQLAlertProfile',  -- Change to your mail profile
        @recipients = 'dba_team@example.com',  -- Change to actual recipients
        @subject = 'SQL Server Alert: Low Free Space in Databases',
        @body = @tableHTML,
        @body_format = 'HTML';
END

DROP TABLE #Results;

Response Procedure

Review the alert email for affected databases and file(s).
Log in to the instance and validate the issue.
Investigate root cause:
- Unusual data growth
- Backup or log file retention
- Disk issues
Take corrective action:
- Increase disk/file size
- Archive/purge old data
- Contact system admin for storage extension
Document the incident and resolution steps.

Maintenance

Verify job execution and success status weekly.
Test email delivery monthly.
Review and adjust thresholds quarterly.