SQL server health check report

Here is the script.

USE master;

IF OBJECT_ID('dbo.sp_Send_SQLHealthCheck_Report', 'P') IS NOT NULL

DROP PROCEDURE dbo.sp_Send_SQLHealthCheck_Report;

CREATE PROCEDURE dbo.sp_Send_SQLHealthCheck_Report

@MailProfile NVARCHAR(128) = 'your_mail_profile', -- Replace with your DB Mail Profile

@Recipients NVARCHAR(MAX) = 'dba-team@company.com' -- Replace with target email address

BEGIN

SET NOCOUNT ON;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

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

-- 1. REUSABLE INLINE CSS STYLES

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

DECLARE @TableStyle VARCHAR(250) = 'width: 100%; border-collapse: collapse; margin-bottom: 25px; font-family: Segoe UI, Arial, sans-serif; font-size: 12px; text-align: left;';

DECLARE @ThStyle VARCHAR(200) = 'background-color: #0f172a; color: #ffffff; padding: 9px 10px; border: 1px solid #0f172a; font-weight: 600;';

DECLARE @TdStyle VARCHAR(200) = 'padding: 8px 10px; border-bottom: 1px solid #e2e8f0; color: #333333;';

DECLARE @TdRightStyle VARCHAR(200)= 'padding: 8px 10px; border-bottom: 1px solid #e2e8f0; color: #333333; text-align: right;';

DECLARE @BadgeGreen VARCHAR(200) = 'background-color: #d1fae5; color: #065f46; font-weight: bold; border-radius: 12px; padding: 3px 10px; text-align: center; display: inline-block;';

DECLARE @BadgeYellow VARCHAR(200)= 'background-color: #fef3c7; color: #92400e; font-weight: bold; border-radius: 12px; padding: 3px 10px; text-align: center; display: inline-block;';

DECLARE @BadgeRed VARCHAR(200) = 'background-color: #fee2e2; color: #991b1b; font-weight: bold; border-radius: 12px; padding: 3px 10px; text-align: center; display: inline-block;';

DECLARE @GetDate DATETIME = GETDATE();

DECLARE @Subject NVARCHAR(256) = 'SQL Server Health Check Report - ' + @@SERVERNAME;

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

-- 2. DATA COLLECTION: SERVER DETAILS

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

DECLARE @Hostname NVARCHAR(128) = CAST(SERVERPROPERTY('MachineName') AS NVARCHAR(128));

DECLARE @InstanceName NVARCHAR(128) = @@SERVERNAME;

DECLARE @Edition NVARCHAR(128) = CAST(SERVERPROPERTY('Edition') AS NVARCHAR(128));

DECLARE @BuildNumber NVARCHAR(128) = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128));

DECLARE @IsCluster INT = CAST(SERVERPROPERTY('IsClustered') AS INT);

DECLARE @CurrentNode NVARCHAR(128) = ISNULL(CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS NVARCHAR(128)), 'N/A');

DECLARE @SQLRestart DATETIME;

DECLARE @UptimeHours INT;

SELECT @SQLRestart = sqlserver_start_time FROM sys.dm_os_sys_info;

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

DECLARE @ServerBadgeStyle VARCHAR(200) = CASE WHEN @UptimeHours < 24 THEN @BadgeRed ELSE @BadgeGreen END;

DECLARE @ServerStatusText VARCHAR(50) = CASE WHEN @UptimeHours < 24 THEN 'CheckNow' ELSE 'Relax' END;

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

-- 3. DATA COLLECTION: DATABASES & BACKUPS

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

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

SELECT

d.name AS DBName,

d.recovery_model_desc,

d.state_desc,

MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS LastFull,

DATEDIFF(DAY, MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END), GETDATE()) AS FullAgeDays,

MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END) AS LastDiff,

DATEDIFF(HOUR, MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END), GETDATE()) AS DiffAgeHours,

MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS LastLog,

DATEDIFF(MINUTE, MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END), GETDATE()) AS LogAgeMinutes

INTO #BackupInfo

FROM sys.databases d

LEFT JOIN msdb.dbo.backupset b ON d.name = b.database_name

WHERE d.name NOT IN ('tempdb')

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

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

-- 4. DATA COLLECTION: DISKS

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

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

CREATE TABLE #DiskInfo (

VolumeName VARCHAR(128),

TotalGB DECIMAL(10,2),

UsedGB DECIMAL(10,2),

FreeGB DECIMAL(10,2),

FreePct DECIMAL(5,2)

);

INSERT INTO #DiskInfo

SELECT DISTINCT

volume_mount_point,

CAST(total_bytes / 1073741824.0 AS DECIMAL(10,2)),

CAST((total_bytes - available_bytes) / 1073741824.0 AS DECIMAL(10,2)),

CAST(available_bytes / 1073741824.0 AS DECIMAL(10,2)),

CAST((available_bytes * 100.0 / total_bytes) AS DECIMAL(5,2))

FROM sys.master_files AS f

CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id);

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

-- 5. CALCULATE EXECUTIVE KPI COUNTS

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

DECLARE @OfflineOrRestoringCount INT = 0;

DECLARE @BackupAlertCount INT = 0;

DECLARE @DiskAlertCount INT = 0;

DECLARE @LowestDiskDrive VARCHAR(128) = '';

DECLARE @LowestDiskPct DECIMAL(5,2) = 100.0;

SELECT @OfflineOrRestoringCount = COUNT(*) FROM sys.databases WHERE state_desc <> 'ONLINE' AND name NOT IN ('tempdb');

SELECT @BackupAlertCount = COUNT(*)

FROM #BackupInfo

WHERE FullAgeDays > 7 OR FullAgeDays IS NULL

OR DiffAgeHours > 24

OR (recovery_model_desc <> 'SIMPLE' AND (LogAgeMinutes > 60 OR LogAgeMinutes IS NULL));

SELECT @DiskAlertCount = COUNT(*) FROM #DiskInfo WHERE FreePct < 25;

SELECT TOP 1 @LowestDiskDrive = VolumeName, @LowestDiskPct = FreePct

FROM #DiskInfo

ORDER BY FreePct ASC;

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

-- 6. BUILD HTML: EXECUTIVE SUMMARY CARDS (TOP SECTION)

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

DECLARE @SummaryHTML NVARCHAR(MAX) = N'

<div style="font-size: 22px;">' + CASE WHEN @UptimeHours < 24 THEN '⚠️' ELSE '✅' END + '</div>

<div>

<div style="font-size: 11px; text-transform: uppercase; font-weight: 700; color: #475569;">SQL Service</div>

<div style="font-size: 13px; font-weight: 700; color: ' + CASE WHEN @UptimeHours < 24 THEN '#dc2626' ELSE '#15803d' END + ';">' + CASE WHEN @UptimeHours < 24 THEN 'Rebooted < 24h' ELSE 'Running Normal' END + '</div>

</div>

<div style="flex: 1; border: 1px solid ' + CASE WHEN @OfflineOrRestoringCount > 0 THEN '#fde68a' ELSE '#bbf7d0' END + '; background-color: ' + CASE WHEN @OfflineOrRestoringCount > 0 THEN '#fffbeb' ELSE '#f0fdf4' END + '; border-radius: 8px; padding: 12px 15px; display: flex; align-items: center; gap: 12px;">

<div style="font-size: 22px;">' + CASE WHEN @OfflineOrRestoringCount > 0 THEN '⚠️' ELSE '✅' END + '</div>

<div>

<div style="font-size: 11px; text-transform: uppercase; font-weight: 700; color: #475569;">Database State</div>

<div style="font-size: 13px; font-weight: 700; color: ' + CASE WHEN @OfflineOrRestoringCount > 0 THEN '#b45309' ELSE '#15803d' END + ';">' + CASE WHEN @OfflineOrRestoringCount > 0 THEN CAST(@OfflineOrRestoringCount AS VARCHAR(5)) + ' DB Not Online' ELSE 'All Online' END + '</div>

</div>

<div style="flex: 1; border: 1px solid ' + CASE WHEN @BackupAlertCount > 0 THEN '#fecaca' ELSE '#bbf7d0' END + '; background-color: ' + CASE WHEN @BackupAlertCount > 0 THEN '#fef2f2' ELSE '#f0fdf4' END + '; border-radius: 8px; padding: 12px 15px; display: flex; align-items: center; gap: 12px;">

<div style="font-size: 22px;">' + CASE WHEN @BackupAlertCount > 0 THEN '🚨' ELSE '✅' END + '</div>

<div>

<div style="font-size: 11px; text-transform: uppercase; font-weight: 700; color: #475569;">Backup Status</div>

<div style="font-size: 13px; font-weight: 700; color: ' + CASE WHEN @BackupAlertCount > 0 THEN '#dc2626' ELSE '#15803d' END + ';">' + CASE WHEN @BackupAlertCount > 0 THEN CAST(@BackupAlertCount AS VARCHAR(5)) + ' Overdue' ELSE 'All Backups OK' END + '</div>

</div>

<div>

<div style="font-size: 11px; text-transform: uppercase; font-weight: 700; color: #475569;">Disk Space</div>

<div style="font-size: 13px; font-weight: 700; color: ' + CASE WHEN @LowestDiskPct < 15 THEN '#dc2626' WHEN @LowestDiskPct < 25 THEN '#b45309' ELSE '#15803d' END + ';">' + @LowestDiskDrive + ' (' + CAST(@LowestDiskPct AS VARCHAR(10)) + '% Free)</div>

</div>

</div>';

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

-- 7. BUILD HTML SECTIONS (1. SERVER, 2. DB, 3. BACKUPS, 4. DISK)

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

-- 1. Server Details

DECLARE @ServerHTML NVARCHAR(MAX) = N'

<div style="font-size: 15px; font-weight: 700; color: #0f172a; border-left: 4px solid #0284c7; padding-left: 8px; margin-bottom: 10px;">1. Server Details</div>

<thead>

<tr>

<th style="' + @ThStyle + '">GetDate</th>

<th style="' + @ThStyle + '">Hostname</th>

<th style="' + @ThStyle + '">SQL Instance</th>

<th style="' + @ThStyle + '">Edition</th>

<th style="' + @ThStyle + '">Build Number</th>

<th style="' + @ThStyle + '">Is Cluster</th>

<th style="' + @ThStyle + '">Current Node</th>

<th style="' + @ThStyle + '">SQL Restart</th>

<th style="' + @ThStyle + ' text-align: right;">Uptime (Hrs)</th>

<th style="' + @ThStyle + ' text-align: center;">Status</th>

</tr>

</thead>

<tbody>

<tr>

<td style="' + @TdStyle + '">' + CONVERT(VARCHAR(16), @GetDate, 120) + '</td>

<td style="' + @TdStyle + '">' + @Hostname + '</td>

<td style="' + @TdStyle + ' font-weight: 600;">' + @InstanceName + '</td>

<td style="' + @TdStyle + '">' + @Edition + '</td>

<td style="' + @TdStyle + '">' + @BuildNumber + '</td>

<td style="' + @TdStyle + '">' + CASE WHEN @IsCluster = 1 THEN 'Yes' ELSE 'No' END + '</td>

<td style="' + @TdStyle + '">' + @CurrentNode + '</td>

<td style="' + @TdStyle + '">' + CONVERT(VARCHAR(16), @SQLRestart, 120) + '</td>

<td style="' + @TdRightStyle + '">' + CAST(@UptimeHours AS VARCHAR(10)) + '</td>

<td style="' + @TdStyle + ' text-align: center;"><span style="' + @ServerBadgeStyle + '">' + @ServerStatusText + '</span></td>

</tr>

</tbody>

</table>';

-- 2. Database Details

DECLARE @DbHTML NVARCHAR(MAX) = N'

<div style="font-size: 15px; font-weight: 700; color: #0f172a; border-left: 4px solid #0284c7; padding-left: 8px; margin-bottom: 10px;">2. Database Details</div>

<thead>

<tr>

<th style="' + @ThStyle + '">Database</th>

<th style="' + @ThStyle + '">Recovery Model</th>

<th style="' + @ThStyle + '">State</th>

<th style="' + @ThStyle + ' text-align: center;">Status</th>

</tr>

</thead>

<tbody>';

SELECT @DbHTML = @DbHTML +

'<tr style="' + CASE WHEN state_desc <> 'ONLINE' THEN 'background-color: #fef2f2; border-bottom: 1px solid #fca5a5;' ELSE '' END + '">

<td style="' + @TdStyle + '">' + recovery_model_desc + '</td>

<td style="' + @TdStyle + ' font-weight: ' + CASE WHEN state_desc <> 'ONLINE' THEN '700; color: #dc2626;' ELSE '400;' END + '">' + state_desc + '</td>

<td style="' + @TdStyle + ' text-align: center;"><span style="' +

CASE WHEN state_desc = 'ONLINE' THEN @BadgeGreen ELSE @BadgeRed END + '">' +

CASE WHEN state_desc = 'ONLINE' THEN 'Relax' ELSE 'CheckNow' END + '</span></td>

</tr>'

FROM sys.databases

WHERE name NOT IN ('tempdb')

ORDER BY name;

SET @DbHTML = @DbHTML + '</tbody></table>';

-- 3. Database Backup Details

DECLARE @BackupHTML NVARCHAR(MAX) = N'

<div style="font-size: 15px; font-weight: 700; color: #0f172a; border-left: 4px solid #0284c7; padding-left: 8px; margin-bottom: 10px;">3. Database Backup Details</div>

<thead>

<tr>

<th style="' + @ThStyle + '">Database</th>

<th style="' + @ThStyle + ' text-align: center;">Status</th>

</tr>

</thead>

<tbody>';

SELECT @BackupHTML = @BackupHTML +

'<tr style="' + CASE WHEN FullAgeDays > 7 OR FullAgeDays IS NULL OR DiffAgeHours > 24 OR (recovery_model_desc <> 'SIMPLE' AND (LogAgeMinutes > 60 OR LogAgeMinutes IS NULL)) THEN 'background-color: #fef2f2; border-bottom: 1px solid #fca5a5;' ELSE '' END + '">

<td style="' + @TdStyle + ' font-weight: 600;">' + DBName + '</td>

<td style="' + @TdStyle + '">' + ISNULL(CONVERT(VARCHAR(16), LastFull, 120), '<span style="color: #94a3b8;">Never</span>') + '</td>

<td style="' + @TdRightStyle + '">' + ISNULL(CAST(FullAgeDays AS VARCHAR(10)) + ' Days', 'N/A') + '</td>

<td style="' + @TdStyle + '">' + ISNULL(CONVERT(VARCHAR(16), LastDiff, 120), '<span style="color: #94a3b8;">Never</span>') + '</td>

<td style="' + @TdRightStyle + '">' + ISNULL(CAST(DiffAgeHours AS VARCHAR(10)) + ' Hrs', 'N/A') + '</td>

<td style="' + @TdStyle + '">' + ISNULL(CONVERT(VARCHAR(16), LastLog, 120), '<span style="color: #94a3b8;">Never</span>') + '</td>

<td style="' + @TdRightStyle + '">' +

CASE

WHEN recovery_model_desc = 'SIMPLE' THEN 'N/A'

WHEN LogAgeMinutes IS NULL THEN 'N/A'

WHEN LogAgeMinutes < 60 THEN CAST(LogAgeMinutes AS VARCHAR(10)) + ' Min'

ELSE CAST(LogAgeMinutes / 60 AS VARCHAR(10)) + ' Hrs'

END + '</td>

<td style="' + @TdStyle + ' text-align: center;"><span style="' +

CASE

WHEN FullAgeDays > 7 OR FullAgeDays IS NULL THEN @BadgeRed

WHEN DiffAgeHours > 24 THEN @BadgeRed

WHEN recovery_model_desc <> 'SIMPLE' AND (LogAgeMinutes > 60 OR LogAgeMinutes IS NULL) THEN @BadgeRed

ELSE @BadgeGreen

END + '">' +

CASE

WHEN FullAgeDays > 7 OR FullAgeDays IS NULL THEN 'CheckNow'

WHEN DiffAgeHours > 24 THEN 'CheckNow'

WHEN recovery_model_desc <> 'SIMPLE' AND (LogAgeMinutes > 60 OR LogAgeMinutes IS NULL) THEN 'CheckNow'

ELSE 'Relax'

END + '</span></td>

</tr>'

FROM #BackupInfo

ORDER BY DBName;

SET @BackupHTML = @BackupHTML + '</tbody></table>';

-- 4. Disk Details (With Dynamic Visual Bar Chart)

DECLARE @DiskHTML NVARCHAR(MAX) = N'

<div style="font-size: 15px; font-weight: 700; color: #0f172a; border-left: 4px solid #0284c7; padding-left: 8px; margin-bottom: 10px;">4. Disk Details</div>

<thead>

<tr>

<th style="' + @ThStyle + '">Drive</th>

<th style="' + @ThStyle + ' text-align: right;">Total (GB)</th>

<th style="' + @ThStyle + ' width: 180px;">Free % Visual Bar</th>

<th style="' + @ThStyle + ' text-align: center;">Status</th>

</tr>

</thead>

<tbody>';

SELECT @DiskHTML = @DiskHTML +

'<tr style="' + CASE WHEN FreePct < 15 THEN 'background-color: #fef2f2; border-bottom: 1px solid #fca5a5;' WHEN FreePct < 25 THEN 'background-color: #fffbeb; border-bottom: 1px solid #fde68a;' ELSE '' END + '">

<td style="' + @TdStyle + ' font-weight: 600;">' + VolumeName + '</td>

<td style="' + @TdRightStyle + '">' + CAST(TotalGB AS VARCHAR(20)) + '</td>

<td style="' + @TdRightStyle + '">' + CAST(UsedGB AS VARCHAR(20)) + '</td>

<td style="' + @TdRightStyle + ' font-weight: ' + CASE WHEN FreePct < 25 THEN '700;' ELSE '400;' END + '">' + CAST(FreeGB AS VARCHAR(20)) + '</td>

<div style="width: ' + CAST(FreePct AS VARCHAR(10)) + '%; background-color: ' +

CASE WHEN FreePct < 15 THEN '#ef4444' WHEN FreePct < 25 THEN '#f59e0b' ELSE '#10b981' END +

'; height: 100%;"></div>

</div>

<span style="font-weight: 700; min-width: 35px; color: ' +

CASE WHEN FreePct < 15 THEN '#dc2626' WHEN FreePct < 25 THEN '#b45309' ELSE '#059669' END +

';">' + CAST(FreePct AS VARCHAR(10)) + '%</span>

</div>

</td>

<td style="' + @TdStyle + ' text-align: center;"><span style="' +

CASE WHEN FreePct < 15 THEN @BadgeRed WHEN FreePct < 25 THEN @BadgeYellow ELSE @BadgeGreen END + '">' +

CASE WHEN FreePct < 25 THEN 'CheckNow' ELSE 'Relax' END + '</span></td>

</tr>'

FROM #DiskInfo

ORDER BY VolumeName;

SET @DiskHTML = @DiskHTML + '</tbody></table>';

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

-- 8. ASSEMBLE HTML DOCUMENT AND SEND EMAIL

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

DECLARE @FullHTMLBody NVARCHAR(MAX);

SET @FullHTMLBody = N'<!DOCTYPE html><html><body style="font-family: Segoe UI, Helvetica, Arial, sans-serif; background-color: #f8fafc; margin: 0; padding: 20px;">' +

N'<div style="max-width: 1000px; margin: auto; background-color: #ffffff; border-radius: 12px; border: 1px solid #e2e8f0; padding: 25px; box-shadow: 0 4px 6px -1px rgba(0, 0, 0, 0.05);">' +

N'<div style="text-align: center; border-bottom: 2px solid #f1f5f9; padding-bottom: 15px; margin-bottom: 20px;">' +

N'<div style="font-size: 24px; color: #0f172a; font-weight: 800; letter-spacing: -0.5px;">SQL Server Health Check Report</div>' +

N'<div style="font-size: 13px; color: #64748b; margin-top: 4px;">Generated on: <b>' + CONVERT(VARCHAR(19), @GetDate, 120) + N'</b>  |  SQL Instance: <b style="color: #0f172a;">' + @InstanceName + N'</b></div>' +

N'</div>' +

@SummaryHTML +

@ServerHTML +

@DbHTML +

@BackupHTML +

@DiskHTML +

N'<div style="text-align: center; color: #94a3b8; font-size: 11px; margin-top: 20px; border-top: 1px solid #f1f5f9; padding-top: 15px;">This is an automated system health notification. Please do not reply directly to this email.</div>' +

N'</div></body></html>';

EXEC msdb.dbo.sp_send_dbmail

@profile_name = @MailProfile,

@recipients = @Recipients,

@subject = @Subject,

@body = @FullHTMLBody,

@body_format = 'HTML';

-- Clean Up Temp Tables

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

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

END;

Labels

Technology

SQL server health check report

Post a Comment

Author Name

Contact Form