MS SQL health check report updated

Check here.

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. SERVER & DATABASE METADATA

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

DECLARE @GetDate DATETIME = GETDATE();

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

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);

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

-- 2. TEMP TABLES FOR DATA COLLECTION

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

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

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

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

-- Database State & Size

SELECT

d.name,

d.recovery_model_desc,

d.compatibility_level,

CAST(SUM(mf.size * 8.0 / 1024 / 1024) AS DECIMAL(10,2)) AS SizeGB,

d.state_desc,

d.database_id

INTO #DatabaseInfo

FROM sys.databases d

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

WHERE d.name NOT IN ('tempdb')

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

-- Backup Information

SELECT

d.name AS DBName,

d.recovery_model_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 #DatabaseInfo d

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

GROUP BY d.name, d.recovery_model_desc;

-- Disk Space

SELECT DISTINCT

volume_mount_point AS VolumeName,

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

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

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

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

INTO #DiskInfo

FROM sys.master_files AS f

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

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

-- 3. EXECUTIVE SUMMARY KPIs

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

DECLARE @TotalDBs INT = (SELECT COUNT(*) FROM #DatabaseInfo);

DECLARE @OnlineDBs INT = (SELECT COUNT(*) FROM #DatabaseInfo WHERE state_desc = 'ONLINE');

DECLARE @OfflineCount INT = @TotalDBs - @OnlineDBs;

DECLARE @BackupAlertCount INT = 0;

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));

DECLARE @LowestDiskDrive NVARCHAR(128) = '';

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

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

FROM #DiskInfo ORDER BY FreePct ASC;

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

-- 4. HTML CSS STYLES & BUILD STRINGS

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

DECLARE @CSS NVARCHAR(MAX) = N'

<style>

body { font-family: "Segoe UI", Arial, sans-serif; background-color: #f8fafc; margin: 0; padding: 20px; }

.container { max-width: 1150px; 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); }

.header { background-color: #1e3a8a; border-radius: 12px 12px 0 0; padding: 20px; margin: -25px -25px 20px -25px; text-align: center; }

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

.header .sub { font-size: 13px; color: #bfdbfe; margin-top: 6px; }

.header .sub b { color: #ffffff; }

/* Dashboard Cards */

.dashboard { display: flex; gap: 15px; margin-bottom: 25px; flex-wrap: wrap; }

.card { flex: 1; min-width: 220px; border-radius: 12px; padding: 18px; border: 1px solid; position: relative; background-color: #ffffff; }

.card-green { background-color: #f0fdf4; border-color: #bbf7d0; }

.card-blue { background-color: #eff6ff; border-color: #bfdbfe; }

.card-orange { background-color: #fffbeb; border-color: #fde68a; }

.card-red { background-color: #fef2f2; border-color: #fecaca; }

.card-header-row { display: flex; align-items: center; gap: 15px; margin-bottom: 15px; }

.card-icon { width: 56px; height: 56px; border-radius: 50%; display: flex; align-items: center; justify-content: center; color: white; font-size: 26px; flex-shrink: 0; }

.icon-green { background-color: #22c55e; }

.icon-blue { background-color: #3b82f6; }

.icon-orange { background-color: #f59e0b; }

.icon-red { background-color: #ef4444; }

.card-header-text { display: flex; flex-direction: column; }

.card-header-title { font-size: 12px; text-transform: uppercase; font-weight: 800; color: #1e293b; letter-spacing: 0.5px; margin-bottom: 2px; }

.card-header-value { font-size: 22px; font-weight: 700; margin: 0; line-height: 1.2; }

.card-footer-row { border-top: 1px solid rgba(0,0,0,0.08); padding-top: 12px; text-align: center; }

.card-sub { font-size: 12px; color: #64748b; margin-bottom: 8px; }

.card-badge { display: inline-block; padding: 4px 16px; border-radius: 20px; font-weight: 700; font-size: 12px; }

.badge-green { background-color: #dcfce7; color: #166534; }

.badge-red { background-color: #fee2e2; color: #991b1b; }

.badge-orange { background-color: #fef3c7; color: #92400e; }

.section-title { font-size: 15px; font-weight: 700; color: #0f172a; border-left: 4px solid #3b82f6; padding-left: 10px; margin-bottom: 12px; margin-top: 25px; }

table { width: 100%; border-collapse: separate; border-spacing: 0; font-size: 12px; margin-bottom: 15px; border: 1px solid #1e3a8a; border-radius: 8px; overflow: hidden; }

th { background-color: #1e3a8a; color: #ffffff; padding: 10px 12px; border: none; font-weight: 600; text-align: left; }

td { padding: 9px 12px; border-bottom: 1px solid #e2e8f0; color: #334155; }

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

.text-center { text-align: center; }

.text-right { text-align: right; }

.status-badge { display: inline-block; padding: 3px 14px; border-radius: 20px; font-weight: 600; font-size: 11px; }

.status-ok { background-color: #dcfce7; color: #166534; }

.status-warn { background-color: #fef3c7; color: #92400e; }

.status-err { background-color: #fee2e2; color: #991b1b; }

.footer { margin-top: 20px; padding: 12px 15px; background-color: #eff6ff; border: 1px solid #bfdbfe; border-radius: 8px; color: #1e3a8a; font-size: 12px; display: flex; flex-direction: column; align-items: flex-start; gap: 8px; }

.footer .disclaimer { display: flex; align-items: center; gap: 8px; width: 100%; }

.footer .signature { margin-top: 4px; font-weight: 600; color: #0f172a; }

@media (max-width: 700px) { .dashboard { flex-direction: column; } }

</style>';

-- Build Dashboard Cards

DECLARE @DashboardHTML NVARCHAR(MAX) = N'

<div class="card-header-title">SQL Service</div>

<div class="card-header-value" style="color:#15803d;">Running</div>

</div>

<div class="card-badge badge-green"><i class="fas fa-check-circle" style="margin-right:4px;"></i>HEALTHY</div>

</div>

<div class="card-header-title">Databases</div>

<div class="card-header-value" style="color:#1d4ed8;">' + CAST(@OnlineDBs AS VARCHAR(10)) + ' / ' + CAST(@TotalDBs AS VARCHAR(10)) + ' Online</div>

</div>

<div class="card-sub">' + CAST(@OfflineCount AS VARCHAR(10)) + ' Offline / Suspect</div>

<div class="card-badge badge-green"><i class="fas fa-check-circle" style="margin-right:4px;"></i>HEALTHY</div>

</div>

<div class="card-header-title">Backups</div>

<div class="card-header-value" style="color:#b45309;">' + CASE WHEN @BackupAlertCount = 0 THEN 'All Successful' ELSE CAST(@BackupAlertCount AS VARCHAR(10)) + ' Overdue' END + '</div>

</div>

<div class="card-sub">' + CAST(@BackupAlertCount AS VARCHAR(10)) + ' Overdue</div>

<div class="card-badge ' + CASE WHEN @BackupAlertCount = 0 THEN 'badge-green' ELSE 'badge-orange' END + '"><i class="fas ' + CASE WHEN @BackupAlertCount = 0 THEN 'fa-check-circle' ELSE 'fa-exclamation-triangle' END + '" style="margin-right:4px;"></i>' + CASE WHEN @BackupAlertCount = 0 THEN 'HEALTHY' ELSE 'WARNING' END + '</div>

</div>

<div class="card-header-title">Disk Space</div>

<div class="card-header-value" style="color:#dc2626;">' + @LowestDiskDrive + ' ' + CAST(@LowestDiskPct AS VARCHAR(10)) + '% Free</div>

</div>

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

<div class="card-badge badge-orange"><i class="fas fa-exclamation-triangle" style="margin-right:4px;"></i>WARNING</div>

</div>

</div>';

-- 1. Server Details Table

DECLARE @ServerHTML NVARCHAR(MAX) = N'

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

<table>

<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 class="text-right">Uptime (Hrs)</th><th class="text-center">Status</th>

</tr></thead>

<td>' + CONVERT(VARCHAR(19), @GetDate, 120) + '</td>

<td>' + @Hostname + '</td>

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

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

<td>' + @BuildNumber + '</td>

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

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

<td>' + CONVERT(VARCHAR(16), @SQLRestart, 120) + '</td>

<td class="text-right">' + CAST(@UptimeHours AS VARCHAR(10)) + '</td>

<td class="text-center"><span class="status-badge status-ok">Relax</span></td>

</tr></tbody>

</table>';

-- 2. Database Details Table

DECLARE @DbHTML NVARCHAR(MAX) = N'

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

<table>

<th>Database Name</th><th>Recovery Model</th><th>Compatibility Level</th><th class="text-right">Database Size (GB)</th><th>State</th><th class="text-center">Status</th>

</tr></thead>

<tbody>';

SELECT @DbHTML = @DbHTML +

'<tr>' +

'<td style="font-weight:600;">' + name + '</td>' +

'<td>' + recovery_model_desc + '</td>' +

'<td>' + CAST(compatibility_level AS VARCHAR) + '</td>' +

'<td class="text-right">' + CAST(SizeGB AS VARCHAR) + '</td>' +

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

'<td class="text-center"><span class="status-badge ' + CASE WHEN state_desc = 'ONLINE' THEN 'status-ok' ELSE 'status-err' END + '">' + CASE WHEN state_desc = 'ONLINE' THEN 'Relax' ELSE 'CheckNow' END + '</span></td>' +

'</tr>'

FROM #DatabaseInfo

ORDER BY name;

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

-- 3. Database Backup Details

DECLARE @BackupHTML NVARCHAR(MAX) = N'

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

<table>

<th>Database Name</th><th>Recovery Model</th><th>Last Full Backup</th><th class="text-right">Full Age</th>

<th>Last Diff Backup</th><th class="text-right">Diff Age</th>

<th>Last Log Backup</th><th class="text-right">Log Age</th><th class="text-center">Status</th>

</tr></thead>

<tbody>';

SELECT @BackupHTML = @BackupHTML +

'<tr>' +

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

'<td>' + recovery_model_desc + '</td>' +

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

'<td class="text-right">' + ISNULL(CAST(FullAgeDays AS VARCHAR(10)) + ' Days', 'N/A') + '</td>' +

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

'<td class="text-right">' + ISNULL(CAST(DiffAgeHours AS VARCHAR(10)) + ' Hrs', 'N/A') + '</td>' +

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

'<td class="text-right">' +

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 class="text-center"><span class="status-badge ' +

CASE WHEN FullAgeDays > 7 OR FullAgeDays IS NULL OR DiffAgeHours > 24 OR (recovery_model_desc <> 'SIMPLE' AND (LogAgeMinutes > 60 OR LogAgeMinutes IS NULL)) THEN 'status-err' ELSE 'status-ok' END + '">' +

CASE WHEN FullAgeDays > 7 OR FullAgeDays IS NULL OR DiffAgeHours > 24 OR (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 Table (UPDATED: Removed Progress Bar)

DECLARE @DiskHTML NVARCHAR(MAX) = N'

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

<table>

<th>Drive</th><th class="text-right">Total (GB)</th><th class="text-right">Used (GB)</th><th class="text-right">Free (GB)</th>

<th class="text-center">Free %</th><th class="text-center">Status</th>

</tr></thead>

<tbody>';

SELECT @DiskHTML = @DiskHTML +

'<tr>' +

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

'<td class="text-right">' + CAST(TotalGB AS VARCHAR(20)) + '</td>' +

'<td class="text-right">' + CAST(UsedGB AS VARCHAR(20)) + '</td>' +

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

'<td class="text-center" style="font-weight:700;color:' + CASE WHEN FreePct < 15 THEN '#dc2626' WHEN FreePct < 25 THEN '#b45309' ELSE '#1e293b' END + ';">' + CAST(FreePct AS VARCHAR(10)) + '%</td>' +

'<td class="text-center"><span class="status-badge ' +

CASE WHEN FreePct < 15 THEN 'status-err' WHEN FreePct < 25 THEN 'status-warn' ELSE 'status-ok' END + '">' +

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

'</tr>'

FROM #DiskInfo

ORDER BY VolumeName;

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

-- Footer with Signature

DECLARE @FooterHTML NVARCHAR(MAX) = N'

This is an automated email. Please do not reply.

</div>

Thanks & regards,<br>

Siva Padala, MS SQL DBA

</div>

</div>';

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

-- 5. ASSEMBLE COMPLETE HTML DOCUMENT

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

DECLARE @FullHTMLBody NVARCHAR(MAX) = N'

<!DOCTYPE html>

<html>

<body>

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

Server: <b>' + @InstanceName + '</b>  | 

Generated: ' + CONVERT(VARCHAR(19), @GetDate, 120) + '  | 

SQL Version: ' + @Edition + ' (' + @BuildNumber + ')

</div>

' + @DashboardHTML + '

' + @ServerHTML + '

' + @DbHTML + '

' + @BackupHTML + '

' + @DiskHTML + '

' + @FooterHTML + '

</div>

</body>

</html>';

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

-- 6. SEND EMAIL

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

EXEC msdb.dbo.sp_send_dbmail

@profile_name = @MailProfile,

@recipients = @Recipients,

@subject = @Subject,

@body = @FullHTMLBody,

@body_format = 'HTML';

-- Cleanup

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

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

MS SQL health check report updated

Post a Comment

Author Name

Contact Form