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
AS
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;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @BackupDetailsRows = @BackupDetailsRows +
N'<tr>' +
N'<td>' + @BkName + N'</td>' +
N'<td>' + @BkRecovery + N'</td>' +
N'<td>' + @BkFull + N'</td>' +
N'<td>' + @BkDiff + 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, @BkFull, @BkDiff, @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>
<meta charset="UTF-8">
<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; }
.header-bar { background-color:#0b1f3a; height:8px; width:100%; }
h1 { color:#0b3d91; font-size:26px; margin:20px 0 5px 0; }
.meta { color:#555; font-size:13px; margin-bottom:20px; }
.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 { border-collapse: collapse; width:100%; margin-top:10px; 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; }
.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:8px; 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:6px; font-size:12px; color:#555; }
</style>
</head>
<body>
<div class="header-bar"></div>
<div class="container">
<h1>SQL Server Health Check Report</h1>
<div class="meta">
<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="dashboard">
<div class="card" style="background-color:' + @CardServerBg + N';">
<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" style="background-color:' + @CardDbBg + N';">
<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" style="background-color:' + @CardBackupBg + N';">
<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" style="background-color:' + @CardDiskBg + N';">
<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>
</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>
<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>
<h2 class="section-title">3. DATABASE BACKUP DETAILS</h2>
<table>
<tr>
<th>Database Name</th><th>Recovery Model</th><th>Full Backup Age</th><th>Diff Backup Age</th><th>Log Backup Age</th><th>Status</th>
</tr>
' + @BackupDetailsRows + N'
</table>
<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 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
GO
/* ============================================================================
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).
============================================================================ */
*/
Post a Comment