Azure Administration

Another V4 Health check report

July 27, 2026

Test this one
/*

/* ============================================================================

SQL SERVER HEALTH CHECK REPORT

----------------------------------------------------------------------------

Single self-contained stored procedure that:

1. Collects Server / Database / Backup / Disk health information

2. Renders it into an HTML email body (pure HTML + inline CSS only,

no external CDN / image / font links)

3. Sends it via Database Mail (msdb.dbo.sp_send_dbmail)

DEPLOY:

- Create this in a utility/admin database on EVERY SQL Server instance

(e.g. [DBA_Admin]) or in msdb if you prefer.

- Requires Database Mail to be configured with a working profile.

- Schedule via SQL Agent Job -> "EXEC dbo.usp_SQLServerHealthCheckReport"

ASSUMPTIONS (documented so you can tune them to your environment):

- Backup section excludes tempdb and system databases (master/model/msdb)

to mirror typical operational reporting. Remove the filter below if you

want system databases included in the backup checks too.

- Log-backup checks only apply to databases NOT in SIMPLE recovery model.

- A backup type that has NEVER been taken is treated as overdue

(NULL last-backup date = CheckNow), per the requested rule set.

- Disk stats are pulled via sys.dm_os_volume_stats (works for any drive

hosting at least one SQL Server data/log file).

============================================================================ */

CREATE OR ALTER PROCEDURE dbo.usp_SQLServerHealthCheckReport

@ProfileName NVARCHAR(128) = N'DBMailProfile', -- Database Mail profile name

@Recipients NVARCHAR(MAX) = N'dba-team@company.com',

@Subject NVARCHAR(255) = NULL,

@FullBackupDaysThreshold INT = 7, -- Full backup age threshold (days)

@DiffBackupHoursThreshold INT = 24, -- Diff backup age threshold (hours)

@LogBackupMinutesThreshold INT = 60, -- Log backup age threshold (minutes)

@LowDiskCriticalPct DECIMAL(5,2) = 15.00, -- < this % -> Red

@LowDiskWarningPct DECIMAL(5,2) = 25.00, -- < this % -> Yellow

@SignatureName NVARCHAR(200) = N'Database Administration Team',

@SignatureTitle NVARCHAR(200) = N'SQL Server Monitoring & Support',

@SignatureContact NVARCHAR(200) = N'dba-team@company.com'

BEGIN

SET NOCOUNT ON;

----------------------------------------------------------------------

-- 0. Common variables

----------------------------------------------------------------------

DECLARE @GetDate DATETIME = GETDATE();

DECLARE @HostName NVARCHAR(128) = CONVERT(NVARCHAR(128), SERVERPROPERTY('MachineName'));

DECLARE @ServerName NVARCHAR(128) = CONVERT(NVARCHAR(128), SERVERPROPERTY('ServerName'));

DECLARE @Edition NVARCHAR(128) = CONVERT(NVARCHAR(128), SERVERPROPERTY('Edition'));

DECLARE @ProductVersion NVARCHAR(128) = CONVERT(NVARCHAR(128), SERVERPROPERTY('ProductVersion'));

DECLARE @IsClusteredInt INT = CONVERT(INT, SERVERPROPERTY('IsClustered'));

DECLARE @IsClustered NVARCHAR(10) = CASE WHEN @IsClusteredInt = 1 THEN N'Yes' ELSE N'No' END;

DECLARE @CurrentNode NVARCHAR(128) = CONVERT(NVARCHAR(128), SERVERPROPERTY('ComputerNamePhysicalNetBIOS'));

DECLARE @SQLRestart DATETIME;

DECLARE @UptimeHrs INT;

DECLARE @SQLVersionFull NVARCHAR(400) = @@VERSION;

SELECT @SQLRestart = sqlserver_start_time FROM sys.dm_os_sys_info;

SET @UptimeHrs = DATEDIFF(HOUR, @SQLRestart, @GetDate);

SET @SQLVersionFull = LTRIM(RTRIM(LEFT(@SQLVersionFull, CHARINDEX(CHAR(10), @SQLVersionFull + CHAR(10)) - 1)));

IF @Subject IS NULL

SET @Subject = N'SQL Server Health Check Report - ' + @ServerName + N' - ' + CONVERT(NVARCHAR(20), @GetDate, 120);

-- Server status (uptime rule)

DECLARE @ServerStatusText NVARCHAR(20);

DECLARE @ServerStatusColor NVARCHAR(20);

DECLARE @ServerIsHealthy BIT;

IF @UptimeHrs < 24

BEGIN

SET @ServerStatusText = N'CheckNow';

SET @ServerStatusColor = N'#c0392b';

SET @ServerIsHealthy = 0;

END

ELSE

BEGIN

SET @ServerStatusText = N'Relax';

SET @ServerStatusColor = N'#1e7e34';

SET @ServerIsHealthy = 1;

END

----------------------------------------------------------------------

-- 1. DATABASE DETAILS

----------------------------------------------------------------------

IF OBJECT_ID('tempdb..#DatabaseDetails') IS NOT NULL DROP TABLE #DatabaseDetails;

SELECT

d.name AS DatabaseName,

d.recovery_model_desc AS RecoveryModel,

d.compatibility_level AS CompatLevel,

CAST(SUM(CASE WHEN mf.type = 0 THEN mf.size ELSE 0 END) * 8.0 / 1024 / 1024 AS DECIMAL(12,2)) AS SizeGB,

d.state_desc AS DbState,

CASE WHEN d.state_desc = 'ONLINE' THEN N'Relax' ELSE N'CheckNow' END AS Status,

CASE WHEN d.state_desc = 'ONLINE' THEN N'#1e7e34' ELSE N'#c0392b' END AS StatusColor

INTO #DatabaseDetails

FROM sys.databases d

LEFT JOIN sys.master_files mf ON mf.database_id = d.database_id

GROUP BY d.name, d.recovery_model_desc, d.compatibility_level, d.state_desc

ORDER BY d.name;

DECLARE @DbTotalCount INT, @DbOnlineCount INT;

SELECT @DbTotalCount = COUNT(*), @DbOnlineCount = SUM(CASE WHEN DbState = 'ONLINE' THEN 1 ELSE 0 END)

FROM #DatabaseDetails;

----------------------------------------------------------------------

-- 2. BACKUP DETAILS (user databases only - excludes tempdb / system DBs)

----------------------------------------------------------------------

IF OBJECT_ID('tempdb..#BackupDetails') IS NOT NULL DROP TABLE #BackupDetails;

;WITH LastFull AS (

SELECT database_name, MAX(backup_finish_date) AS LastBackupDate

FROM msdb.dbo.backupset WHERE type = 'D' GROUP BY database_name

LastDiff AS (

SELECT database_name, MAX(backup_finish_date) AS LastBackupDate

FROM msdb.dbo.backupset WHERE type = 'I' GROUP BY database_name

LastLog AS (

SELECT database_name, MAX(backup_finish_date) AS LastBackupDate

FROM msdb.dbo.backupset WHERE type = 'L' GROUP BY database_name

)

SELECT

d.name AS DatabaseName,

d.recovery_model_desc AS RecoveryModel,

lf.LastBackupDate AS LastFull,

ld.LastBackupDate AS LastDiff,

ll.LastBackupDate AS LastLog

INTO #BackupDetails

FROM sys.databases d

LEFT JOIN LastFull lf ON lf.database_name = d.name

LEFT JOIN LastDiff ld ON ld.database_name = d.name

LEFT JOIN LastLog ll ON ll.database_name = d.name

WHERE d.database_id > 4 -- excludes master/model/msdb/tempdb

AND d.name <> N'tempdb'

AND d.state_desc = 'ONLINE';

-- Add computed date/age / display / status columns

ALTER TABLE #BackupDetails ADD

FullBackupDT NVARCHAR(20),

DiffBackupDT NVARCHAR(20),

LogBackupDT NVARCHAR(20),

FullAgeDisplay NVARCHAR(20),

DiffAgeDisplay NVARCHAR(20),

LogAgeDisplay NVARCHAR(20),

Status NVARCHAR(20),

StatusColor NVARCHAR(20);

UPDATE b

SET

FullBackupDT = CASE WHEN LastFull IS NULL THEN N'Never' ELSE CONVERT(NVARCHAR(20), LastFull, 120) END,

DiffBackupDT = CASE WHEN LastDiff IS NULL THEN N'Never' ELSE CONVERT(NVARCHAR(20), LastDiff, 120) END,

LogBackupDT = CASE WHEN RecoveryModel = 'SIMPLE' THEN N'N/A'

WHEN LastLog IS NULL THEN N'Never'

ELSE CONVERT(NVARCHAR(20), LastLog, 120) END,

FullAgeDisplay = CASE WHEN LastFull IS NULL THEN N'Never'

ELSE CONVERT(NVARCHAR(10), DATEDIFF(DAY, LastFull, @GetDate)) + N' Days' END,

DiffAgeDisplay = CASE WHEN LastDiff IS NULL THEN N'Never'

ELSE CONVERT(NVARCHAR(10), DATEDIFF(HOUR, LastDiff, @GetDate)) + N' Hrs' END,

LogAgeDisplay = CASE WHEN RecoveryModel = 'SIMPLE' THEN N'N/A'

WHEN LastLog IS NULL THEN N'Never'

ELSE CONVERT(NVARCHAR(10), DATEDIFF(MINUTE, LastLog, @GetDate)) + N' Min' END,

Status = CASE WHEN

(LastFull IS NULL OR DATEDIFF(DAY, LastFull, @GetDate) > @FullBackupDaysThreshold)

OR (LastDiff IS NULL OR DATEDIFF(HOUR, LastDiff, @GetDate) > @DiffBackupHoursThreshold)

OR (RecoveryModel <> 'SIMPLE' AND (LastLog IS NULL OR DATEDIFF(MINUTE, LastLog, @GetDate) > @LogBackupMinutesThreshold))

THEN N'CheckNow' ELSE N'Relax' END,

StatusColor = CASE WHEN

(LastFull IS NULL OR DATEDIFF(DAY, LastFull, @GetDate) > @FullBackupDaysThreshold)

OR (LastDiff IS NULL OR DATEDIFF(HOUR, LastDiff, @GetDate) > @DiffBackupHoursThreshold)

OR (RecoveryModel <> 'SIMPLE' AND (LastLog IS NULL OR DATEDIFF(MINUTE, LastLog, @GetDate) > @LogBackupMinutesThreshold))

THEN N'#c0392b' ELSE N'#1e7e34' END

FROM #BackupDetails b;

DECLARE @BackupTotalCount INT, @BackupOverdueCount INT;

SELECT @BackupTotalCount = COUNT(*), @BackupOverdueCount = SUM(CASE WHEN Status = 'CheckNow' THEN 1 ELSE 0 END)

FROM #BackupDetails;

----------------------------------------------------------------------

-- 3. DISK DETAILS

----------------------------------------------------------------------

IF OBJECT_ID('tempdb..#DiskDetails') IS NOT NULL DROP TABLE #DiskDetails;

SELECT DISTINCT

vs.volume_mount_point AS DriveName,

ISNULL(vs.logical_volume_name, N'') AS VolumeLabel,

CAST(vs.total_bytes / 1073741824.0 AS DECIMAL(12,2)) AS TotalGB,

CAST((vs.total_bytes - vs.available_bytes) / 1073741824.0 AS DECIMAL(12,2)) AS UsedGB,

CAST(vs.available_bytes / 1073741824.0 AS DECIMAL(12,2)) AS FreeGB,

CAST(vs.available_bytes * 100.0 / NULLIF(vs.total_bytes,0) AS DECIMAL(5,2)) AS FreePct

INTO #DiskDetails

FROM sys.master_files AS mf

CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS vs;

ALTER TABLE #DiskDetails ADD Status NVARCHAR(20), StatusColor NVARCHAR(20);

UPDATE #DiskDetails

SET Status = CASE

WHEN FreePct < @LowDiskCriticalPct THEN N'CheckNow'

WHEN FreePct < @LowDiskWarningPct THEN N'CheckNow'

ELSE N'Relax'

END,

StatusColor = CASE

WHEN FreePct < @LowDiskCriticalPct THEN N'#c0392b' -- Red

WHEN FreePct < @LowDiskWarningPct THEN N'#d68910' -- Yellow/Amber

ELSE N'#1e7e34' -- Green

END;

DECLARE @DiskWorstPct DECIMAL(5,2), @DiskAnyWarning BIT = 0, @DiskAnyCritical BIT = 0;

SELECT @DiskWorstPct = MIN(FreePct) FROM #DiskDetails;

IF EXISTS (SELECT 1 FROM #DiskDetails WHERE FreePct < @LowDiskCriticalPct) SET @DiskAnyCritical = 1;

ELSE IF EXISTS (SELECT 1 FROM #DiskDetails WHERE FreePct < @LowDiskWarningPct) SET @DiskAnyWarning = 1;

----------------------------------------------------------------------

-- 4. Build HTML row fragments

----------------------------------------------------------------------

DECLARE @ServerDetailsRows NVARCHAR(MAX) = N'';

DECLARE @DatabaseDetailsRows NVARCHAR(MAX) = N'';

DECLARE @BackupDetailsRows NVARCHAR(MAX) = N'';

DECLARE @DiskDetailsRows NVARCHAR(MAX) = N'';

-- Server Details (single row)

SET @ServerDetailsRows =

N'<tr>' +

N'<td>' + CONVERT(NVARCHAR(20), @GetDate, 120) + N'</td>' +

N'<td>' + @HostName + N'</td>' +

N'<td>' + @ServerName + N'</td>' +

N'<td>' + @Edition + N'</td>' +

N'<td>' + @ProductVersion + N'</td>' +

N'<td>' + @IsClustered + N'</td>' +

N'<td>' + @CurrentNode + N'</td>' +

N'<td>' + CONVERT(NVARCHAR(20), @SQLRestart, 120) + N'</td>' +

N'<td>' + CONVERT(NVARCHAR(10), @UptimeHrs) + N'</td>' +

N'<td><span class="badge" style="background-color:' + @ServerStatusColor + N';">' + @ServerStatusText + N'</span></td>' +

N'</tr>';

-- Database Details (cursor over #DatabaseDetails)

DECLARE @DbName NVARCHAR(128), @DbRecovery NVARCHAR(60), @DbState NVARCHAR(60), @DbSize DECIMAL(12,2), @DbStatus NVARCHAR(20), @DbColor NVARCHAR(20);

DECLARE db_cur CURSOR LOCAL FAST_FORWARD FOR

SELECT DatabaseName, RecoveryModel, DbState, SizeGB, Status, StatusColor FROM #DatabaseDetails ORDER BY DatabaseName;

OPEN db_cur;

FETCH NEXT FROM db_cur INTO @DbName, @DbRecovery, @DbState, @DbSize, @DbStatus, @DbColor;

WHILE @@FETCH_STATUS = 0

BEGIN

SET @DatabaseDetailsRows = @DatabaseDetailsRows +

N'<tr>' +

N'<td>' + @DbName + N'</td>' +

N'<td>' + @DbRecovery + N'</td>' +

N'<td>' + CONVERT(NVARCHAR(20), @DbSize) + N'</td>' +

N'<td>' + @DbState + N'</td>' +

N'<td><span class="badge" style="background-color:' + @DbColor + N';">' + @DbStatus + N'</span></td>' +

N'</tr>';

FETCH NEXT FROM db_cur INTO @DbName, @DbRecovery, @DbState, @DbSize, @DbStatus, @DbColor;

END

CLOSE db_cur; DEALLOCATE db_cur;

-- Backup Details

DECLARE @BkName NVARCHAR(128), @BkRecovery NVARCHAR(60),

@BkFullDT NVARCHAR(20), @BkFull NVARCHAR(20),

@BkDiffDT NVARCHAR(20), @BkDiff NVARCHAR(20),

@BkLogDT NVARCHAR(20), @BkLog NVARCHAR(20),

@BkStatus NVARCHAR(20), @BkColor NVARCHAR(20);

DECLARE bk_cur CURSOR LOCAL FAST_FORWARD FOR

SELECT DatabaseName, RecoveryModel, FullBackupDT, FullAgeDisplay, DiffBackupDT, DiffAgeDisplay, LogBackupDT, LogAgeDisplay, Status, StatusColor

FROM #BackupDetails ORDER BY DatabaseName;

OPEN bk_cur;

FETCH NEXT FROM bk_cur INTO @BkName, @BkRecovery, @BkFullDT, @BkFull, @BkDiffDT, @BkDiff, @BkLogDT, @BkLog, @BkStatus, @BkColor;

WHILE @@FETCH_STATUS = 0

BEGIN

SET @BackupDetailsRows = @BackupDetailsRows +

N'<tr>' +

N'<td>' + @BkName + N'</td>' +

N'<td>' + @BkRecovery + N'</td>' +

N'<td>' + @BkFullDT + N'</td>' +

N'<td>' + @BkFull + N'</td>' +

N'<td>' + @BkDiffDT + N'</td>' +

N'<td>' + @BkDiff + N'</td>' +

N'<td>' + @BkLogDT + N'</td>' +

N'<td>' + @BkLog + N'</td>' +

N'<td><span class="badge" style="background-color:' + @BkColor + N';">' + @BkStatus + N'</span></td>' +

N'</tr>';

FETCH NEXT FROM bk_cur INTO @BkName, @BkRecovery, @BkFullDT, @BkFull, @BkDiffDT, @BkDiff, @BkLogDT, @BkLog, @BkStatus, @BkColor;

END

CLOSE bk_cur; DEALLOCATE bk_cur;

-- Disk Details

DECLARE @DkDrive NVARCHAR(10), @DkLabel NVARCHAR(128), @DkTotal DECIMAL(12,2), @DkUsed DECIMAL(12,2), @DkFree DECIMAL(12,2), @DkPct DECIMAL(5,2), @DkStatus NVARCHAR(20), @DkColor NVARCHAR(20);

DECLARE dk_cur CURSOR LOCAL FAST_FORWARD FOR

SELECT DriveName, VolumeLabel, TotalGB, UsedGB, FreeGB, FreePct, Status, StatusColor FROM #DiskDetails ORDER BY DriveName;

OPEN dk_cur;

FETCH NEXT FROM dk_cur INTO @DkDrive, @DkLabel, @DkTotal, @DkUsed, @DkFree, @DkPct, @DkStatus, @DkColor;

WHILE @@FETCH_STATUS = 0

BEGIN

SET @DiskDetailsRows = @DiskDetailsRows +

N'<tr>' +

N'<td>' + @DkDrive + N'</td>' +

N'<td>' + @DkLabel + N'</td>' +

N'<td>' + CONVERT(NVARCHAR(20), @DkTotal) + N'</td>' +

N'<td>' + CONVERT(NVARCHAR(20), @DkUsed) + N'</td>' +

N'<td>' + CONVERT(NVARCHAR(20), @DkFree) + N'</td>' +

N'<td>' + CONVERT(NVARCHAR(20), @DkPct) + N'%</td>' +

N'<td><span class="badge" style="background-color:' + @DkColor + N';">' + @DkStatus + N'</span></td>' +

N'</tr>';

FETCH NEXT FROM dk_cur INTO @DkDrive, @DkLabel, @DkTotal, @DkUsed, @DkFree, @DkPct, @DkStatus, @DkColor;

END

CLOSE dk_cur; DEALLOCATE dk_cur;

----------------------------------------------------------------------

-- 5. Dashboard card values

----------------------------------------------------------------------

DECLARE @CardServerStatus NVARCHAR(20) = CASE WHEN @ServerIsHealthy = 1 THEN N'HEALTHY' ELSE N'WARNING' END;

DECLARE @CardServerColor NVARCHAR(20) = CASE WHEN @ServerIsHealthy = 1 THEN N'#1e7e34' ELSE N'#c0392b' END;

DECLARE @CardServerBg NVARCHAR(20) = CASE WHEN @ServerIsHealthy = 1 THEN N'#eafaf1' ELSE N'#fdecea' END;

DECLARE @CardDbStatus NVARCHAR(20) = CASE WHEN @DbOnlineCount = @DbTotalCount THEN N'HEALTHY' ELSE N'WARNING' END;

DECLARE @CardDbColor NVARCHAR(20) = CASE WHEN @DbOnlineCount = @DbTotalCount THEN N'#1e7e34' ELSE N'#c0392b' END;

DECLARE @CardDbBg NVARCHAR(20) = CASE WHEN @DbOnlineCount = @DbTotalCount THEN N'#eaf3fb' ELSE N'#fdecea' END;

DECLARE @CardBackupStatus NVARCHAR(20) = CASE WHEN ISNULL(@BackupOverdueCount,0) = 0 THEN N'HEALTHY' ELSE N'WARNING' END;

DECLARE @CardBackupColor NVARCHAR(20) = CASE WHEN ISNULL(@BackupOverdueCount,0) = 0 THEN N'#1e7e34' ELSE N'#c0392b' END;

DECLARE @CardBackupBg NVARCHAR(20) = CASE WHEN ISNULL(@BackupOverdueCount,0) = 0 THEN N'#fdf6e3' ELSE N'#fdecea' END;

DECLARE @CardDiskStatus NVARCHAR(20) = CASE WHEN @DiskAnyCritical = 1 OR @DiskAnyWarning = 1 THEN N'WARNING' ELSE N'HEALTHY' END;

DECLARE @CardDiskColor NVARCHAR(20) = CASE WHEN @DiskAnyCritical = 1 OR @DiskAnyWarning = 1 THEN N'#c0392b' ELSE N'#1e7e34' END;

DECLARE @CardDiskBg NVARCHAR(20) = N'#fdecea';

----------------------------------------------------------------------

-- 6. Assemble final HTML

----------------------------------------------------------------------

DECLARE @HTML NVARCHAR(MAX);

SET @HTML = N'

<html>

<head>

<style>

body { font-family: Segoe UI, Arial, sans-serif; background-color:#f4f6f8; margin:0; padding:0; color:#2c3e50; }

.container { max-width:1200px; margin:0 auto; padding:20px; }

.report-header { background-color:#0b3d91; color:#ffffff; border-radius:10px; padding:22px 25px; margin-bottom:10px; }

.report-header h1 { color:#ffffff; font-size:26px; margin:0 0 10px 0; }

.meta { color:#eaf1fb; font-size:13px; }

.meta span { margin-right:25px; }

h2.section-title { color:#0b3d91; font-size:16px; border-bottom:2px solid #0b3d91; padding-bottom:5px; margin-top:30px; }

.table-wrap { border-radius:10px; overflow:hidden; border:1px solid #e0e0e0; margin-top:10px; }

table { border-collapse: collapse; width:100%; font-size:13px; }

th { background-color:#0b3d91; color:#ffffff; text-align:left; padding:8px 10px; }

td { padding:8px 10px; border-bottom:1px solid #e0e0e0; }

tr:nth-child(even) { background-color:#f9fafc; }

tr:last-child td { border-bottom:none; }

.badge { color:#ffffff; padding:4px 10px; border-radius:12px; font-size:12px; font-weight:bold; display:inline-block; }

.dashboard { width:100%; margin-top:10px; }

.card { border-radius:14px; padding:15px; width:23%; display:inline-block; vertical-align:top; margin-right:1%; box-sizing:border-box; }

.card-title { font-size:13px; font-weight:bold; color:#555; letter-spacing:0.5px; }

.card-value { font-size:20px; font-weight:bold; margin:8px 0 4px 0; }

.card-sub { font-size:12px; color:#777; }

.footer { margin-top:30px; padding:12px; background-color:#eaf3fb; border-radius:10px; font-size:12px; color:#555; }

.signature { margin-top:25px; padding:15px 20px; background-color:#ffffff; border:1px solid #e0e0e0; border-radius:10px; font-size:13px; color:#2c3e50; }

.signature .sig-name { font-weight:bold; color:#0b3d91; }

</style>

</head>

<body>

<h1>SQL Server Health Check Report</h1>

<span><b>Server:</b> ' + @ServerName + N'</span>

<span><b>Generated:</b> ' + CONVERT(NVARCHAR(20), @GetDate, 120) + N'</span>

<span><b>SQL Version:</b> ' + @SQLVersionFull + N'</span>

</div>

<h2 class="section-title">HEALTH SUMMARY DASHBOARD</h2>

<div class="card-title">SERVER STATUS</div>

<div class="card-value" style="color:' + @CardServerColor + N';">' + CASE WHEN @ServerIsHealthy = 1 THEN N'Running' ELSE N'Recently Restarted' END + N'</div>

<div class="card-sub">Uptime: ' + CONVERT(NVARCHAR(10), @UptimeHrs) + N' Hrs</div>

<div style="margin-top:8px;"><span class="badge" style="background-color:' + @CardServerColor + N';">' + @CardServerStatus + N'</span></div>

</div>

<div class="card-title">DATABASES</div>

<div class="card-value" style="color:' + @CardDbColor + N';">' + CONVERT(NVARCHAR(10), @DbOnlineCount) + N' / ' + CONVERT(NVARCHAR(10), @DbTotalCount) + N' Online</div>

<div class="card-sub">' + CONVERT(NVARCHAR(10), @DbTotalCount - @DbOnlineCount) + N' Offline / Suspect</div>

<div style="margin-top:8px;"><span class="badge" style="background-color:' + @CardDbColor + N';">' + @CardDbStatus + N'</span></div>

</div>

<div class="card-title">BACKUPS</div>

<div class="card-value" style="color:' + @CardBackupColor + N';">' + CASE WHEN ISNULL(@BackupOverdueCount,0) = 0 THEN N'All Successful' ELSE N'Attention Needed' END + N'</div>

<div class="card-sub">' + CONVERT(NVARCHAR(10), ISNULL(@BackupOverdueCount,0)) + N' Overdue</div>

<div style="margin-top:8px;"><span class="badge" style="background-color:' + @CardBackupColor + N';">' + @CardBackupStatus + N'</span></div>

</div>

<div class="card-title">DISK SPACE</div>

<div class="card-value" style="color:' + @CardDiskColor + N';">' + ISNULL(CONVERT(NVARCHAR(10), @DiskWorstPct), N'N/A') + N'% Free</div>

<div class="card-sub">Lowest Free Space</div>

<div style="margin-top:8px;"><span class="badge" style="background-color:' + @CardDiskColor + N';">' + @CardDiskStatus + N'</span></div>

</div>

<h2 class="section-title">1. SERVER DETAILS</h2>

<table>

<tr>

<th>GetDate</th><th>Hostname</th><th>SQL Instance</th><th>Edition</th><th>Build Number</th>

<th>Is Cluster</th><th>Current Node</th><th>SQL Restart</th><th>Uptime (Hrs)</th><th>Status</th>

</tr>

' + @ServerDetailsRows + N'

</table>

</div>

<h2 class="section-title">2. DATABASE DETAILS</h2>

<table>

<tr>

<th>Database Name</th><th>Recovery Model</th><th>Database Size (GB)</th><th>State</th><th>Status</th>

</tr>

' + @DatabaseDetailsRows + N'

</table>

</div>

<h2 class="section-title">3. DATABASE BACKUP DETAILS</h2>

<table>

<tr>

<th>Database Name</th><th>Recovery Model</th>

<th>Last Full Backup</th><th>Full Age</th>

<th>Last Diff Backup</th><th>Diff Age</th>

<th>Last Log Backup</th><th>Log Age</th>

<th>Status</th>

</tr>

' + @BackupDetailsRows + N'

</table>

</div>

<h2 class="section-title">4. DISK DETAILS</h2>

<table>

<tr>

<th>Drive</th><th>Volume Label</th><th>Total (GB)</th><th>Used (GB)</th><th>Free (GB)</th><th>Free %</th><th>Status</th>

</tr>

' + @DiskDetailsRows + N'

</table>

</div>

Regards,<br>

<span class="sig-name">' + @SignatureName + N'</span><br>

' + @SignatureTitle + N'<br>

' + @SignatureContact + N'

</div>

<div class="footer">This is an automated email generated by usp_SQLServerHealthCheckReport. Please do not reply.</div>

</div>

</body>

</html>';

----------------------------------------------------------------------

-- 7. Send mail

----------------------------------------------------------------------

EXEC msdb.dbo.sp_send_dbmail

@profile_name = @ProfileName,

@recipients = @Recipients,

@subject = @Subject,

@body = @HTML,

@body_format = 'HTML';

----------------------------------------------------------------------

-- 8. Cleanup

----------------------------------------------------------------------

IF OBJECT_ID('tempdb..#DatabaseDetails') IS NOT NULL DROP TABLE #DatabaseDetails;

IF OBJECT_ID('tempdb..#BackupDetails') IS NOT NULL DROP TABLE #BackupDetails;

IF OBJECT_ID('tempdb..#DiskDetails') IS NOT NULL DROP TABLE #DiskDetails;

END

/* ============================================================================

EXAMPLE EXECUTION

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

EXEC dbo.usp_SQLServerHealthCheckReport

@ProfileName = N'DBMailProfile',

@Recipients = N'dba-team@company.com';

-- Optional: schedule this as a daily/hourly SQL Agent Job on each server,

-- adjusting thresholds via the parameters if a given server needs

-- different SLAs (e.g. tighter log backup windows on OLTP boxes).

============================================================================ */

MS SQL health check report - Latest No links

July 26, 2026

This is the latest normal one

/* ============================================================================

SQL SERVER HEALTH CHECK REPORT

----------------------------------------------------------------------------

Single self-contained stored procedure that:

1. Collects Server / Database / Backup / Disk health information

2. Renders it into an HTML email body (pure HTML + inline CSS only,

no external CDN / image / font links)

3. Sends it via Database Mail (msdb.dbo.sp_send_dbmail)

DEPLOY:

- Create this in a utility/admin database on EVERY SQL Server instance

(e.g. [DBA_Admin]) or in msdb if you prefer.

- Requires Database Mail to be configured with a working profile.

- Schedule via SQL Agent Job -> "EXEC dbo.usp_SQLServerHealthCheckReport"

ASSUMPTIONS (documented so you can tune them to your environment):

- Backup section excludes tempdb and system databases (master/model/msdb)

to mirror typical operational reporting. Remove the filter below if you

want system databases included in the backup checks too.

- Log-backup checks only apply to databases NOT in SIMPLE recovery model.

- A backup type that has NEVER been taken is treated as overdue

(NULL last-backup date = CheckNow), per the requested rule set.

- Disk stats are pulled via sys.dm_os_volume_stats (works for any drive

hosting at least one SQL Server data/log file).

============================================================================ */

CREATE OR ALTER PROCEDURE dbo.usp_SQLServerHealthCheckReport

@ProfileName NVARCHAR(128) = N'DBMailProfile', -- Database Mail profile name

@Recipients NVARCHAR(MAX) = N'dba-team@company.com',

@Subject NVARCHAR(255) = NULL,

@FullBackupDaysThreshold INT = 7, -- Full backup age threshold (days)

@DiffBackupHoursThreshold INT = 24, -- Diff backup age threshold (hours)

@LogBackupMinutesThreshold INT = 60, -- Log backup age threshold (minutes)

@LowDiskCriticalPct DECIMAL(5,2) = 15.00, -- < this % -> Red

@LowDiskWarningPct DECIMAL(5,2) = 25.00 -- < this % -> Yellow

BEGIN

SET NOCOUNT ON;

----------------------------------------------------------------------

-- 0. Common variables

----------------------------------------------------------------------

DECLARE @GetDate DATETIME = GETDATE();

DECLARE @HostName NVARCHAR(128) = CONVERT(NVARCHAR(128), SERVERPROPERTY('MachineName'));

DECLARE @ServerName NVARCHAR(128) = CONVERT(NVARCHAR(128), SERVERPROPERTY('ServerName'));

DECLARE @Edition NVARCHAR(128) = CONVERT(NVARCHAR(128), SERVERPROPERTY('Edition'));

DECLARE @ProductVersion NVARCHAR(128) = CONVERT(NVARCHAR(128), SERVERPROPERTY('ProductVersion'));

DECLARE @IsClusteredInt INT = CONVERT(INT, SERVERPROPERTY('IsClustered'));

DECLARE @IsClustered NVARCHAR(10) = CASE WHEN @IsClusteredInt = 1 THEN N'Yes' ELSE N'No' END;

DECLARE @CurrentNode NVARCHAR(128) = CONVERT(NVARCHAR(128), SERVERPROPERTY('ComputerNamePhysicalNetBIOS'));

DECLARE @SQLRestart DATETIME;

DECLARE @UptimeHrs INT;

DECLARE @SQLVersionFull NVARCHAR(400) = @@VERSION;

SELECT @SQLRestart = sqlserver_start_time FROM sys.dm_os_sys_info;

SET @UptimeHrs = DATEDIFF(HOUR, @SQLRestart, @GetDate);

SET @SQLVersionFull = LTRIM(RTRIM(LEFT(@SQLVersionFull, CHARINDEX(CHAR(10), @SQLVersionFull + CHAR(10)) - 1)));

IF @Subject IS NULL

SET @Subject = N'SQL Server Health Check Report - ' + @ServerName + N' - ' + CONVERT(NVARCHAR(20), @GetDate, 120);

-- Server status (uptime rule)

DECLARE @ServerStatusText NVARCHAR(20);

DECLARE @ServerStatusColor NVARCHAR(20);

DECLARE @ServerIsHealthy BIT;

IF @UptimeHrs < 24

BEGIN

SET @ServerStatusText = N'CheckNow';

SET @ServerStatusColor = N'#c0392b';

SET @ServerIsHealthy = 0;

END

ELSE

BEGIN

SET @ServerStatusText = N'Relax';

SET @ServerStatusColor = N'#1e7e34';

SET @ServerIsHealthy = 1;

END

----------------------------------------------------------------------

-- 1. DATABASE DETAILS

----------------------------------------------------------------------

IF OBJECT_ID('tempdb..#DatabaseDetails') IS NOT NULL DROP TABLE #DatabaseDetails;

SELECT

d.name AS DatabaseName,

d.recovery_model_desc AS RecoveryModel,

d.compatibility_level AS CompatLevel,

CAST(SUM(CASE WHEN mf.type = 0 THEN mf.size ELSE 0 END) * 8.0 / 1024 / 1024 AS DECIMAL(12,2)) AS SizeGB,

d.state_desc AS DbState,

CASE WHEN d.state_desc = 'ONLINE' THEN N'Relax' ELSE N'CheckNow' END AS Status,

CASE WHEN d.state_desc = 'ONLINE' THEN N'#1e7e34' ELSE N'#c0392b' END AS StatusColor

INTO #DatabaseDetails

FROM sys.databases d

LEFT JOIN sys.master_files mf ON mf.database_id = d.database_id

GROUP BY d.name, d.recovery_model_desc, d.compatibility_level, d.state_desc

ORDER BY d.name;

DECLARE @DbTotalCount INT, @DbOnlineCount INT;

SELECT @DbTotalCount = COUNT(*), @DbOnlineCount = SUM(CASE WHEN DbState = 'ONLINE' THEN 1 ELSE 0 END)

FROM #DatabaseDetails;

----------------------------------------------------------------------

-- 2. BACKUP DETAILS (user databases only - excludes tempdb / system DBs)

----------------------------------------------------------------------

IF OBJECT_ID('tempdb..#BackupDetails') IS NOT NULL DROP TABLE #BackupDetails;

;WITH LastFull AS (

SELECT database_name, MAX(backup_finish_date) AS LastBackupDate

FROM msdb.dbo.backupset WHERE type = 'D' GROUP BY database_name

LastDiff AS (

SELECT database_name, MAX(backup_finish_date) AS LastBackupDate

FROM msdb.dbo.backupset WHERE type = 'I' GROUP BY database_name

LastLog AS (

SELECT database_name, MAX(backup_finish_date) AS LastBackupDate

FROM msdb.dbo.backupset WHERE type = 'L' GROUP BY database_name

)

SELECT

d.name AS DatabaseName,

d.recovery_model_desc AS RecoveryModel,

lf.LastBackupDate AS LastFull,

ld.LastBackupDate AS LastDiff,

ll.LastBackupDate AS LastLog

INTO #BackupDetails

FROM sys.databases d

LEFT JOIN LastFull lf ON lf.database_name = d.name

LEFT JOIN LastDiff ld ON ld.database_name = d.name

LEFT JOIN LastLog ll ON ll.database_name = d.name

WHERE d.database_id > 4 -- excludes master/model/msdb/tempdb

AND d.name <> N'tempdb'

AND d.state_desc = 'ONLINE';

-- Add computed age / display / status columns

ALTER TABLE #BackupDetails ADD

FullAgeDisplay NVARCHAR(20),

DiffAgeDisplay NVARCHAR(20),

LogAgeDisplay NVARCHAR(20),

Status NVARCHAR(20),

StatusColor NVARCHAR(20);

UPDATE b

SET

FullAgeDisplay = CASE WHEN LastFull IS NULL THEN N'Never'

ELSE CONVERT(NVARCHAR(10), DATEDIFF(DAY, LastFull, @GetDate)) + N' Days' END,

DiffAgeDisplay = CASE WHEN LastDiff IS NULL THEN N'Never'

ELSE CONVERT(NVARCHAR(10), DATEDIFF(HOUR, LastDiff, @GetDate)) + N' Hrs' END,

LogAgeDisplay = CASE WHEN RecoveryModel = 'SIMPLE' THEN N'N/A'

WHEN LastLog IS NULL THEN N'Never'

ELSE CONVERT(NVARCHAR(10), DATEDIFF(MINUTE, LastLog, @GetDate)) + N' Min' END,

Status = CASE WHEN

(LastFull IS NULL OR DATEDIFF(DAY, LastFull, @GetDate) > @FullBackupDaysThreshold)

OR (LastDiff IS NULL OR DATEDIFF(HOUR, LastDiff, @GetDate) > @DiffBackupHoursThreshold)

OR (RecoveryModel <> 'SIMPLE' AND (LastLog IS NULL OR DATEDIFF(MINUTE, LastLog, @GetDate) > @LogBackupMinutesThreshold))

THEN N'CheckNow' ELSE N'Relax' END,

StatusColor = CASE WHEN

(LastFull IS NULL OR DATEDIFF(DAY, LastFull, @GetDate) > @FullBackupDaysThreshold)

OR (LastDiff IS NULL OR DATEDIFF(HOUR, LastDiff, @GetDate) > @DiffBackupHoursThreshold)

OR (RecoveryModel <> 'SIMPLE' AND (LastLog IS NULL OR DATEDIFF(MINUTE, LastLog, @GetDate) > @LogBackupMinutesThreshold))

THEN N'#c0392b' ELSE N'#1e7e34' END

FROM #BackupDetails b;

DECLARE @BackupTotalCount INT, @BackupOverdueCount INT;

SELECT @BackupTotalCount = COUNT(*), @BackupOverdueCount = SUM(CASE WHEN Status = 'CheckNow' THEN 1 ELSE 0 END)

FROM #BackupDetails;

----------------------------------------------------------------------

-- 3. DISK DETAILS

----------------------------------------------------------------------

IF OBJECT_ID('tempdb..#DiskDetails') IS NOT NULL DROP TABLE #DiskDetails;

SELECT DISTINCT

vs.volume_mount_point AS DriveName,

ISNULL(vs.logical_volume_name, N'') AS VolumeLabel,

CAST(vs.total_bytes / 1073741824.0 AS DECIMAL(12,2)) AS TotalGB,

CAST((vs.total_bytes - vs.available_bytes) / 1073741824.0 AS DECIMAL(12,2)) AS UsedGB,

CAST(vs.available_bytes / 1073741824.0 AS DECIMAL(12,2)) AS FreeGB,

CAST(vs.available_bytes * 100.0 / NULLIF(vs.total_bytes,0) AS DECIMAL(5,2)) AS FreePct

INTO #DiskDetails

FROM sys.master_files AS mf

CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS vs;

ALTER TABLE #DiskDetails ADD Status NVARCHAR(20), StatusColor NVARCHAR(20);

UPDATE #DiskDetails

SET Status = CASE

WHEN FreePct < @LowDiskCriticalPct THEN N'CheckNow'

WHEN FreePct < @LowDiskWarningPct THEN N'CheckNow'

ELSE N'Relax'

END,

StatusColor = CASE

WHEN FreePct < @LowDiskCriticalPct THEN N'#c0392b' -- Red

WHEN FreePct < @LowDiskWarningPct THEN N'#d68910' -- Yellow/Amber

ELSE N'#1e7e34' -- Green

END;

DECLARE @DiskWorstPct DECIMAL(5,2), @DiskAnyWarning BIT = 0, @DiskAnyCritical BIT = 0;

SELECT @DiskWorstPct = MIN(FreePct) FROM #DiskDetails;

IF EXISTS (SELECT 1 FROM #DiskDetails WHERE FreePct < @LowDiskCriticalPct) SET @DiskAnyCritical = 1;

ELSE IF EXISTS (SELECT 1 FROM #DiskDetails WHERE FreePct < @LowDiskWarningPct) SET @DiskAnyWarning = 1;

----------------------------------------------------------------------

-- 4. Build HTML row fragments

----------------------------------------------------------------------

DECLARE @ServerDetailsRows NVARCHAR(MAX) = N'';

DECLARE @DatabaseDetailsRows NVARCHAR(MAX) = N'';

DECLARE @BackupDetailsRows NVARCHAR(MAX) = N'';

DECLARE @DiskDetailsRows NVARCHAR(MAX) = N'';

-- Server Details (single row)

SET @ServerDetailsRows =

N'<tr>' +

N'<td>' + CONVERT(NVARCHAR(20), @GetDate, 120) + N'</td>' +

N'<td>' + @HostName + N'</td>' +

N'<td>' + @ServerName + N'</td>' +

N'<td>' + @Edition + N'</td>' +

N'<td>' + @ProductVersion + N'</td>' +

N'<td>' + @IsClustered + N'</td>' +

N'<td>' + @CurrentNode + N'</td>' +

N'<td>' + CONVERT(NVARCHAR(20), @SQLRestart, 120) + N'</td>' +

N'<td>' + CONVERT(NVARCHAR(10), @UptimeHrs) + N'</td>' +

N'<td><span class="badge" style="background-color:' + @ServerStatusColor + N';">' + @ServerStatusText + N'</span></td>' +

N'</tr>';

-- Database Details (cursor over #DatabaseDetails)

DECLARE @DbName NVARCHAR(128), @DbRecovery NVARCHAR(60), @DbState NVARCHAR(60), @DbSize DECIMAL(12,2), @DbStatus NVARCHAR(20), @DbColor NVARCHAR(20);

DECLARE db_cur CURSOR LOCAL FAST_FORWARD FOR

SELECT DatabaseName, RecoveryModel, DbState, SizeGB, Status, StatusColor FROM #DatabaseDetails ORDER BY DatabaseName;

OPEN db_cur;

FETCH NEXT FROM db_cur INTO @DbName, @DbRecovery, @DbState, @DbSize, @DbStatus, @DbColor;

WHILE @@FETCH_STATUS = 0

BEGIN

SET @DatabaseDetailsRows = @DatabaseDetailsRows +

N'<tr>' +

N'<td>' + @DbName + N'</td>' +

N'<td>' + @DbRecovery + N'</td>' +

N'<td>' + CONVERT(NVARCHAR(20), @DbSize) + N'</td>' +

N'<td>' + @DbState + N'</td>' +

N'<td><span class="badge" style="background-color:' + @DbColor + N';">' + @DbStatus + N'</span></td>' +

N'</tr>';

FETCH NEXT FROM db_cur INTO @DbName, @DbRecovery, @DbState, @DbSize, @DbStatus, @DbColor;

END

CLOSE db_cur; DEALLOCATE db_cur;

-- Backup Details

DECLARE @BkName NVARCHAR(128), @BkRecovery NVARCHAR(60), @BkFull NVARCHAR(20), @BkDiff NVARCHAR(20), @BkLog NVARCHAR(20), @BkStatus NVARCHAR(20), @BkColor NVARCHAR(20);

DECLARE bk_cur CURSOR LOCAL FAST_FORWARD FOR

SELECT DatabaseName, RecoveryModel, FullAgeDisplay, DiffAgeDisplay, LogAgeDisplay, Status, StatusColor FROM #BackupDetails ORDER BY DatabaseName;

OPEN bk_cur;

FETCH NEXT FROM bk_cur INTO @BkName, @BkRecovery, @BkFull, @BkDiff, @BkLog, @BkStatus, @BkColor;