/* ============================================================================
SQL SERVER HEALTH CHECK REPORT (OUTLOOK WEB / OWA COMPATIBLE)
FIX NOTES
1. ROOT CAUSE of merged sections / missing cards: the old code built @HTML as
N'literal' + @nvarchar128Var + N'literal' ... The left-most part of that
expression is NVARCHAR(4000), so everything before the first NVARCHAR(MAX)
variable was silently TRUNCATED at 4000 chars. Now @HTML starts as
NVARCHAR(MAX) and is appended section by section.
2. Layout is 100% nested tables (no <h2>, margins, or <div> layout) with
inline styles + bgcolor/width attributes, which Outlook Web/Desktop honour.
3. HTML-encodes names; shows a 'No data' row for empty tables.
4. SQL Agent jobs filtered to '%DBA_Utility%'.
============================================================================ */
CREATE OR ALTER PROCEDURE dbo.usp_SQLServerHealthCheckReport
@ProfileName NVARCHAR(128) = N'DBMailProfile',
@Recipients NVARCHAR(MAX) = N'dba-team@company.com',
@Subject NVARCHAR(255) = NULL,
@FullBackupDaysThreshold INT = 7,
@DiffBackupHoursThreshold INT = 24,
@LogBackupMinutesThreshold INT = 60,
@LowDiskCriticalPct DECIMAL(5,2) = 15.00,
@LowDiskWarningPct DECIMAL(5,2) = 25.00
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 @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);
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.create_date AS CreateDate,
d.recovery_model_desc AS RecoveryModel,
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.create_date, d.recovery_model_desc, 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
----------------------------------------------------------------------
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
AND d.name <> N'tempdb'
AND d.state_desc = 'ONLINE';
ALTER TABLE #BackupDetails ADD
FullBackupDisplay NVARCHAR(20), DiffBackupDisplay NVARCHAR(20), LogBackupDisplay NVARCHAR(20),
FullAgeDisplay NVARCHAR(20), DiffAgeDisplay NVARCHAR(20), LogAgeDisplay NVARCHAR(20),
Status NVARCHAR(20), StatusColor NVARCHAR(20);
UPDATE b
SET
FullBackupDisplay = CASE WHEN LastFull IS NULL THEN N'Never' ELSE CONVERT(NVARCHAR(20), LastFull, 120) END,
DiffBackupDisplay = CASE WHEN LastDiff IS NULL THEN N'Never' ELSE CONVERT(NVARCHAR(20), LastDiff, 120) END,
LogBackupDisplay = 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 @BackupOverdueCount INT;
SELECT @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'
WHEN FreePct < @LowDiskWarningPct THEN N'#d68910'
ELSE N'#1e7e34'
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. SQL AGENT JOB DETAILS (Filtered for DBA_Utility)
----------------------------------------------------------------------
IF OBJECT_ID('tempdb..#JobDetails') IS NOT NULL DROP TABLE #JobDetails;
SELECT
j.name AS JobName,
j.enabled AS IsEnabled,
CASE
WHEN ja.start_execution_date IS NOT NULL AND ja.stop_execution_date IS NULL THEN N'Running'
ELSE N'Idle'
END AS CurrentState,
CASE
WHEN jh.run_status = 0 THEN N'Failed'
WHEN jh.run_status = 1 THEN N'Succeeded'
WHEN jh.run_status = 3 THEN N'Cancelled'
ELSE N'Never Run / Unknown'
END AS LastRunStatus,
CASE
WHEN jh.run_date IS NULL OR jh.run_date = 0 THEN NULL
ELSE CAST(
CAST(jh.run_date AS VARCHAR(8)) + ' ' +
STUFF(STUFF(RIGHT('000000' + CAST(jh.run_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
AS DATETIME)
END AS LastRunDateTime
INTO #JobDetails
FROM msdb.dbo.sysjobs j
OUTER APPLY (
SELECT TOP 1 run_status, run_date, run_time
FROM msdb.dbo.sysjobhistory
WHERE job_id = j.job_id AND step_id = 0
ORDER BY run_date DESC, run_time DESC
) jh
OUTER APPLY (
SELECT TOP 1 start_execution_date, stop_execution_date
FROM msdb.dbo.sysjobactivity
WHERE job_id = j.job_id
ORDER BY start_execution_date DESC
) ja
WHERE j.name LIKE '%DBA_Utility%';
ALTER TABLE #JobDetails ADD Status NVARCHAR(20), StatusColor NVARCHAR(20);
UPDATE #JobDetails
SET Status = CASE
WHEN LastRunStatus IN (N'Failed', N'Never Run / Unknown') THEN N'CheckNow'
ELSE N'Relax'
END,
StatusColor = CASE
WHEN LastRunStatus = N'Failed' THEN N'#c0392b'
WHEN LastRunStatus = N'Never Run / Unknown' THEN N'#d68910'
ELSE N'#1e7e34'
END;
DECLARE @JobFailedCount INT;
SELECT @JobFailedCount = SUM(CASE WHEN Status = N'CheckNow' THEN 1 ELSE 0 END) FROM #JobDetails;
----------------------------------------------------------------------
-- 5. Reusable inline style fragments (Outlook-safe)
----------------------------------------------------------------------
DECLARE @Font NVARCHAR(100) = N'font-family:Segoe UI,Arial,sans-serif;';
DECLARE @TH NVARCHAR(400) = N'background-color:#0b3d91;color:#ffffff;text-align:left;padding:8px;border-right:1px solid #2f5fb3;font-size:13px;' + @Font;
DECLARE @TD NVARCHAR(400) = N'padding:8px;border-bottom:1px solid #dddddd;border-right:1px solid #dddddd;font-size:13px;color:#2c3e50;' + @Font;
-- Rounded-corner wrapper: outer table clips (radius + overflow:hidden); inner table uses separate borders.
-- Outlook Web/new Outlook render the radius; classic Outlook desktop shows square corners (safe fallback).
DECLARE @WO NVARCHAR(600) = N'<table width="100%" cellpadding="0" cellspacing="0" border="0" style="border-collapse:separate;border-spacing:0;border:1px solid #cccccc;border-radius:8px;overflow:hidden;"><tr><td style="padding:0;"><table width="100%" cellpadding="0" cellspacing="0" border="0" style="border-collapse:separate;border-spacing:0;margin:0 -1px -1px 0;">';
DECLARE @WC NVARCHAR(100) = N'</table></td></tr></table>';
DECLARE @SEC NVARCHAR(400) = N'padding:0 0 5px 0;border-bottom:2px solid #0b3d91;color:#0b3d91;font-size:16px;font-weight:bold;' + @Font;
DECLARE @GAP NVARCHAR(300) = N'<table width="100%" cellpadding="0" cellspacing="0" border="0"><tr><td height="20" style="height:20px;font-size:1px;line-height:1px;"> </td></tr></table>';
----------------------------------------------------------------------
-- 6. Build HTML row fragments
----------------------------------------------------------------------
DECLARE @ServerDetailsRows NVARCHAR(MAX) = N'';
DECLARE @DatabaseDetailsRows NVARCHAR(MAX) = N'';
DECLARE @BackupDetailsRows NVARCHAR(MAX) = N'';
DECLARE @DiskDetailsRows NVARCHAR(MAX) = N'';
DECLARE @JobDetailsRows NVARCHAR(MAX) = N'';
-- Server Details
SET @ServerDetailsRows = @ServerDetailsRows +
N'<tr>' +
N'<td style="' + @TD + N'">' + @HostName + N'</td>' +
N'<td style="' + @TD + N'">' + @IsClustered + N'</td>' +
N'<td style="' + @TD + N'">' + ISNULL(@CurrentNode, N'') + N'</td>' +
N'<td style="' + @TD + N'">' + CONVERT(NVARCHAR(20), @SQLRestart, 120) + N'</td>' +
N'<td style="' + @TD + N'">' + CONVERT(NVARCHAR(10), @UptimeHrs) + N'</td>' +
N'<td style="' + @TD + N'"><span style="color:#ffffff;background-color:' + @ServerStatusColor + N';font-weight:bold;padding:4px 10px;"> ' + @ServerStatusText + N' </span></td>' +
N'</tr>';
-- Database Details
DECLARE @DbName NVARCHAR(300), @DbCreateDate DATETIME, @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 REPLACE(REPLACE(DatabaseName,N'&',N'&'),N'<',N'<'), CreateDate, RecoveryModel, DbState, SizeGB, Status, StatusColor
FROM #DatabaseDetails ORDER BY DatabaseName;
OPEN db_cur;
FETCH NEXT FROM db_cur INTO @DbName, @DbCreateDate, @DbRecovery, @DbState, @DbSize, @DbStatus, @DbColor;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @DatabaseDetailsRows = @DatabaseDetailsRows +
N'<tr>' +
N'<td style="' + @TD + N'">' + @DbName + N'</td>' +
N'<td style="' + @TD + N'">' + CONVERT(NVARCHAR(20), @DbCreateDate, 120) + N'</td>' +
N'<td style="' + @TD + N'">' + @DbRecovery + N'</td>' +
N'<td style="' + @TD + N'">' + ISNULL(CONVERT(NVARCHAR(20), @DbSize), N'0.00') + N'</td>' +
N'<td style="' + @TD + N'">' + @DbState + N'</td>' +
N'<td style="' + @TD + N'"><span style="color:#ffffff;background-color:' + @DbColor + N';font-weight:bold;padding:4px 10px;"> ' + @DbStatus + N' </span></td>' +
N'</tr>';
FETCH NEXT FROM db_cur INTO @DbName, @DbCreateDate, @DbRecovery, @DbState, @DbSize, @DbStatus, @DbColor;
END
CLOSE db_cur; DEALLOCATE db_cur;
IF @DatabaseDetailsRows = N'' SET @DatabaseDetailsRows = N'<tr><td colspan="6" style="' + @TD + N'text-align:center;">No data</td></tr>';
-- Backup Details
DECLARE @BkName NVARCHAR(300), @BkRecovery NVARCHAR(60), @BkFullDt NVARCHAR(20), @BkFullAge NVARCHAR(20),
@BkDiffDt NVARCHAR(20), @BkDiffAge NVARCHAR(20), @BkLogDt NVARCHAR(20), @BkLogAge NVARCHAR(20),
@BkStatus NVARCHAR(20), @BkColor NVARCHAR(20);
DECLARE bk_cur CURSOR LOCAL FAST_FORWARD FOR
SELECT REPLACE(REPLACE(DatabaseName,N'&',N'&'),N'<',N'<'), RecoveryModel, FullBackupDisplay, FullAgeDisplay, DiffBackupDisplay, DiffAgeDisplay, LogBackupDisplay, LogAgeDisplay, Status, StatusColor
FROM #BackupDetails ORDER BY DatabaseName;
OPEN bk_cur;
FETCH NEXT FROM bk_cur INTO @BkName, @BkRecovery, @BkFullDt, @BkFullAge, @BkDiffDt, @BkDiffAge, @BkLogDt, @BkLogAge, @BkStatus, @BkColor;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @BackupDetailsRows = @BackupDetailsRows +
N'<tr>' +
N'<td style="' + @TD + N'">' + @BkName + N'</td>' +
N'<td style="' + @TD + N'">' + @BkRecovery + N'</td>' +
N'<td style="' + @TD + N'">' + @BkFullDt + N'</td>' +
N'<td style="' + @TD + N'">' + @BkFullAge + N'</td>' +
N'<td style="' + @TD + N'">' + @BkDiffDt + N'</td>' +
N'<td style="' + @TD + N'">' + @BkDiffAge + N'</td>' +
N'<td style="' + @TD + N'">' + @BkLogDt + N'</td>' +
N'<td style="' + @TD + N'">' + @BkLogAge + N'</td>' +
N'<td style="' + @TD + N'"><span style="color:#ffffff;background-color:' + @BkColor + N';font-weight:bold;padding:4px 10px;"> ' + @BkStatus + N' </span></td>' +
N'</tr>';
FETCH NEXT FROM bk_cur INTO @BkName, @BkRecovery, @BkFullDt, @BkFullAge, @BkDiffDt, @BkDiffAge, @BkLogDt, @BkLogAge, @BkStatus, @BkColor;
END
CLOSE bk_cur; DEALLOCATE bk_cur;
IF @BackupDetailsRows = N'' SET @BackupDetailsRows = N'<tr><td colspan="9" style="' + @TD + N'text-align:center;">No user databases found</td></tr>';
-- Disk Details
DECLARE @DkDrive NVARCHAR(10), @DkLabel NVARCHAR(300), @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, REPLACE(REPLACE(VolumeLabel,N'&',N'&'),N'<',N'<'), 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 style="' + @TD + N'">' + @DkDrive + N'</td>' +
N'<td style="' + @TD + N'">' + @DkLabel + N'</td>' +
N'<td style="' + @TD + N'">' + CONVERT(NVARCHAR(20), @DkTotal) + N'</td>' +
N'<td style="' + @TD + N'">' + CONVERT(NVARCHAR(20), @DkUsed) + N'</td>' +
N'<td style="' + @TD + N'">' + CONVERT(NVARCHAR(20), @DkFree) + N'</td>' +
N'<td style="' + @TD + N'">' + CONVERT(NVARCHAR(20), @DkPct) + N'%</td>' +
N'<td style="' + @TD + N'"><span style="color:#ffffff;background-color:' + @DkColor + N';font-weight:bold;padding:4px 10px;"> ' + @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;
IF @DiskDetailsRows = N'' SET @DiskDetailsRows = N'<tr><td colspan="7" style="' + @TD + N'text-align:center;">No data</td></tr>';
-- Job Details
DECLARE @JbName NVARCHAR(400), @JbEnabled NVARCHAR(10), @JbCurrentState NVARCHAR(20),
@JbLastRunStatus NVARCHAR(30), @JbLastRunDateTime NVARCHAR(20),
@JbStatus NVARCHAR(20), @JbColor NVARCHAR(20);
DECLARE job_cur CURSOR LOCAL FAST_FORWARD FOR
SELECT REPLACE(REPLACE(JobName,N'&',N'&'),N'<',N'<'),
CASE WHEN IsEnabled = 1 THEN N'Yes' ELSE N'No' END,
CurrentState, LastRunStatus,
ISNULL(CONVERT(NVARCHAR(20), LastRunDateTime, 120), N'Never'),
Status, StatusColor
FROM #JobDetails ORDER BY JobName;
OPEN job_cur;
FETCH NEXT FROM job_cur INTO @JbName, @JbEnabled, @JbCurrentState, @JbLastRunStatus, @JbLastRunDateTime, @JbStatus, @JbColor;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @JobDetailsRows = @JobDetailsRows +
N'<tr>' +
N'<td style="' + @TD + N'">' + @JbName + N'</td>' +
N'<td style="' + @TD + N'">' + @JbEnabled + N'</td>' +
N'<td style="' + @TD + N'">' + @JbCurrentState + N'</td>' +
N'<td style="' + @TD + N'">' + @JbLastRunStatus + N'</td>' +
N'<td style="' + @TD + N'">' + @JbLastRunDateTime + N'</td>' +
N'<td style="' + @TD + N'"><span style="color:#ffffff;background-color:' + @JbColor + N';font-weight:bold;padding:4px 10px;"> ' + @JbStatus + N' </span></td>' +
N'</tr>';
FETCH NEXT FROM job_cur INTO @JbName, @JbEnabled, @JbCurrentState, @JbLastRunStatus, @JbLastRunDateTime, @JbStatus, @JbColor;
END
CLOSE job_cur; DEALLOCATE job_cur;
IF @JobDetailsRows = N'' SET @JobDetailsRows = N'<tr><td colspan="6" style="' + @TD + N'text-align:center;">No jobs matching ''%DBA_Utility%'' found</td></tr>';
----------------------------------------------------------------------
-- 7. 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) = CASE WHEN @DiskAnyCritical = 1 OR @DiskAnyWarning = 1 THEN N'#fdecea' ELSE N'#eafaf1' END;
DECLARE @CardJobStatus NVARCHAR(20) = CASE WHEN ISNULL(@JobFailedCount,0) = 0 THEN N'HEALTHY' ELSE N'WARNING' END;
DECLARE @CardJobColor NVARCHAR(20) = CASE WHEN ISNULL(@JobFailedCount,0) = 0 THEN N'#1e7e34' ELSE N'#c0392b' END;
DECLARE @CardJobBg NVARCHAR(20) = CASE WHEN ISNULL(@JobFailedCount,0) = 0 THEN N'#eafaf1' ELSE N'#fdecea' END;
DECLARE @CardTH NVARCHAR(400) = N'background-color:#0b3d91;color:#ffffff;text-align:center;padding:10px;border-right:1px solid #2f5fb3;font-size:13px;font-weight:bold;' + @Font;
DECLARE @CardTD NVARCHAR(200) = N'vertical-align:top;text-align:center;padding:15px;border-bottom:1px solid #dddddd;border-right:1px solid #dddddd;' + @Font;
----------------------------------------------------------------------
-- 8. Assemble final HTML.
-- @HTML is NVARCHAR(MAX) and is APPENDED to (never a single giant
-- literal expression) so nothing is truncated at 4000 chars.
----------------------------------------------------------------------
DECLARE @HTML NVARCHAR(MAX) = N'';
-- 8.1 Document + header banner
SET @HTML = @HTML + N'<html><head><meta charset="UTF-8"></head>'
+ N'<body style="margin:0;padding:0;background-color:#ffffff;color:#2c3e50;' + @Font + N'">'
+ N'<table width="100%" cellpadding="0" cellspacing="0" border="0" bgcolor="#ffffff"><tr><td align="center">'
+ N'<table width="1000" cellpadding="0" cellspacing="0" border="0" style="width:100%;max-width:1000px;"><tr><td style="padding:20px;">'
+ N'<table width="100%" cellpadding="0" cellspacing="0" border="0" bgcolor="#0b3d91" style="background-color:#0b3d91;"><tr>'
+ N'<td align="center" style="padding:20px 15px;color:#ffffff;' + @Font + N'">'
+ N'<span style="color:#ffffff;font-size:24px;font-weight:bold;">SQL Server Health Check Report</span><br><br>'
+ N'<span style="color:#ffffff;font-size:13px;"><b>Server:</b> ' + @ServerName + N' | '
+ N'<b>Generated:</b> ' + CONVERT(NVARCHAR(20), @GetDate, 120) + N' | '
+ N'<b>SQL Version:</b> ' + @SQLVersionFull + N'</span>'
+ N'</td></tr></table>'
+ @GAP;
-- 8.2 Health summary dashboard
SET @HTML = @HTML
+ N'<table width="100%" cellpadding="0" cellspacing="0" border="0"><tr><td style="' + @SEC + N'">HEALTH SUMMARY DASHBOARD</td></tr></table>'
+ N'<table width="100%" cellpadding="0" cellspacing="0" border="0"><tr><td height="10" style="height:10px;font-size:1px;line-height:1px;"> </td></tr></table>'
+ @WO
+ N'<tr>'
+ N'<th width="20%" bgcolor="#0b3d91" style="' + @CardTH + N'border-top-left-radius:7px;">SERVER STATUS</th>'
+ N'<th width="20%" bgcolor="#0b3d91" style="' + @CardTH + N'">DATABASES</th>'
+ N'<th width="20%" bgcolor="#0b3d91" style="' + @CardTH + N'">BACKUPS</th>'
+ N'<th width="20%" bgcolor="#0b3d91" style="' + @CardTH + N'">DISK SPACE</th>'
+ N'<th width="20%" bgcolor="#0b3d91" style="' + @CardTH + N'border-top-right-radius:7px;">AGENT JOBS</th>'
+ N'</tr><tr>';
-- card: server
SET @HTML = @HTML
+ N'<td width="20%" bgcolor="' + @CardServerBg + N'" style="background-color:' + @CardServerBg + N';' + @CardTD + N'">'
+ N'<span style="font-size:16px;font-weight:bold;color:' + @CardServerColor + N';">' + CASE WHEN @ServerIsHealthy = 1 THEN N'Running' ELSE N'Recently Restarted' END + N'</span><br>'
+ N'<span style="font-size:12px;color:#777777;">Uptime: ' + CONVERT(NVARCHAR(10), @UptimeHrs) + N' Hrs</span><br><br>'
+ N'<span style="color:#ffffff;background-color:' + @CardServerColor + N';font-size:12px;font-weight:bold;padding:4px 10px;"> ' + @CardServerStatus + N' </span>'
+ N'</td>';
-- card: databases
SET @HTML = @HTML
+ N'<td width="20%" bgcolor="' + @CardDbBg + N'" style="background-color:' + @CardDbBg + N';' + @CardTD + N'">'
+ N'<span style="font-size:16px;font-weight:bold;color:' + @CardDbColor + N';">' + CONVERT(NVARCHAR(10), @DbOnlineCount) + N' / ' + CONVERT(NVARCHAR(10), @DbTotalCount) + N' Online</span><br>'
+ N'<span style="font-size:12px;color:#777777;">' + CONVERT(NVARCHAR(10), @DbTotalCount - @DbOnlineCount) + N' Offline / Suspect</span><br><br>'
+ N'<span style="color:#ffffff;background-color:' + @CardDbColor + N';font-size:12px;font-weight:bold;padding:4px 10px;"> ' + @CardDbStatus + N' </span>'
+ N'</td>';
-- card: backups
SET @HTML = @HTML
+ N'<td width="20%" bgcolor="' + @CardBackupBg + N'" style="background-color:' + @CardBackupBg + N';' + @CardTD + N'">'
+ N'<span style="font-size:16px;font-weight:bold;color:' + @CardBackupColor + N';">' + CASE WHEN ISNULL(@BackupOverdueCount,0) = 0 THEN N'All Successful' ELSE N'Attention Needed' END + N'</span><br>'
+ N'<span style="font-size:12px;color:#777777;">' + CONVERT(NVARCHAR(10), ISNULL(@BackupOverdueCount,0)) + N' Overdue</span><br><br>'
+ N'<span style="color:#ffffff;background-color:' + @CardBackupColor + N';font-size:12px;font-weight:bold;padding:4px 10px;"> ' + @CardBackupStatus + N' </span>'
+ N'</td>';
-- card: disk
SET @HTML = @HTML
+ N'<td width="20%" bgcolor="' + @CardDiskBg + N'" style="background-color:' + @CardDiskBg + N';' + @CardTD + N'">'
+ N'<span style="font-size:16px;font-weight:bold;color:' + @CardDiskColor + N';">' + ISNULL(CONVERT(NVARCHAR(10), @DiskWorstPct), N'N/A') + N'% Free</span><br>'
+ N'<span style="font-size:12px;color:#777777;">Lowest Free Space</span><br><br>'
+ N'<span style="color:#ffffff;background-color:' + @CardDiskColor + N';font-size:12px;font-weight:bold;padding:4px 10px;"> ' + @CardDiskStatus + N' </span>'
+ N'</td>';
-- card: jobs
SET @HTML = @HTML
+ N'<td width="20%" bgcolor="' + @CardJobBg + N'" style="background-color:' + @CardJobBg + N';' + @CardTD + N'">'
+ N'<span style="font-size:16px;font-weight:bold;color:' + @CardJobColor + N';">' + CASE WHEN ISNULL(@JobFailedCount,0) = 0 THEN N'All Successful' ELSE N'Attention Needed' END + N'</span><br>'
+ N'<span style="font-size:12px;color:#777777;">' + CONVERT(NVARCHAR(10), ISNULL(@JobFailedCount,0)) + N' Failed / Unknown</span><br><br>'
+ N'<span style="color:#ffffff;background-color:' + @CardJobColor + N';font-size:12px;font-weight:bold;padding:4px 10px;"> ' + @CardJobStatus + N' </span>'
+ N'</td>';
SET @HTML = @HTML + N'</tr>' + @WC + @GAP;
-- 8.3 Section 1: Server details
SET @HTML = @HTML
+ N'<table width="100%" cellpadding="0" cellspacing="0" border="0"><tr><td style="' + @SEC + N'">1. SERVER DETAILS</td></tr></table>'
+ N'<table width="100%" cellpadding="0" cellspacing="0" border="0"><tr><td height="10" style="height:10px;font-size:1px;line-height:1px;"> </td></tr></table>'
+ @WO
+ N'<tr>'
+ N'<th align="left" bgcolor="#0b3d91" style="' + @TH + N'border-top-left-radius:7px;">Hostname</th>'
+ N'<th align="left" bgcolor="#0b3d91" style="' + @TH + N'">Is Cluster</th>'
+ N'<th align="left" bgcolor="#0b3d91" style="' + @TH + N'">Current Node</th>'
+ N'<th align="left" bgcolor="#0b3d91" style="' + @TH + N'">SQL Restart</th>'
+ N'<th align="left" bgcolor="#0b3d91" style="' + @TH + N'">Uptime (Hrs)</th>'
+ N'<th align="left" bgcolor="#0b3d91" style="' + @TH + N'border-top-right-radius:7px;">Status</th>'
+ N'</tr>' + @ServerDetailsRows + @WC + @GAP;
-- 8.4 Section 2: Database details
SET @HTML = @HTML
+ N'<table width="100%" cellpadding="0" cellspacing="0" border="0"><tr><td style="' + @SEC + N'">2. DATABASE DETAILS</td></tr></table>'
+ N'<table width="100%" cellpadding="0" cellspacing="0" border="0"><tr><td height="10" style="height:10px;font-size:1px;line-height:1px;"> </td></tr></table>'
+ @WO
+ N'<tr>'
+ N'<th align="left" bgcolor="#0b3d91" style="' + @TH + N'border-top-left-radius:7px;">Database Name</th>'
+ N'<th align="left" bgcolor="#0b3d91" style="' + @TH + N'">Created Date</th>'
+ N'<th align="left" bgcolor="#0b3d91" style="' + @TH + N'">Recovery Model</th>'
+ N'<th align="left" bgcolor="#0b3d91" style="' + @TH + N'">Size (GB)</th>'
+ N'<th align="left" bgcolor="#0b3d91" style="' + @TH + N'">State</th>'
+ N'<th align="left" bgcolor="#0b3d91" style="' + @TH + N'border-top-right-radius:7px;">Status</th>'
+ N'</tr>' + @DatabaseDetailsRows + @WC + @GAP;
-- 8.5 Section 3: Backup details
SET @HTML = @HTML
+ N'<table width="100%" cellpadding="0" cellspacing="0" border="0"><tr><td style="' + @SEC + N'">3. DATABASE BACKUP DETAILS</td></tr></table>'
+ N'<table width="100%" cellpadding="0" cellspacing="0" border="0"><tr><td height="10" style="height:10px;font-size:1px;line-height:1px;"> </td></tr></table>'
+ @WO
+ N'<tr>'
+ N'<th align="left" bgcolor="#0b3d91" style="' + @TH + N'border-top-left-radius:7px;">Database Name</th>'
+ N'<th align="left" bgcolor="#0b3d91" style="' + @TH + N'">Recovery Model</th>'
+ N'<th align="left" bgcolor="#0b3d91" style="' + @TH + N'">Full Backup</th>'
+ N'<th align="left" bgcolor="#0b3d91" style="' + @TH + N'">Full Age</th>'
+ N'<th align="left" bgcolor="#0b3d91" style="' + @TH + N'">Diff Backup</th>'
+ N'<th align="left" bgcolor="#0b3d91" style="' + @TH + N'">Diff Age</th>'
+ N'<th align="left" bgcolor="#0b3d91" style="' + @TH + N'">Log Backup</th>'
+ N'<th align="left" bgcolor="#0b3d91" style="' + @TH + N'">Log Age</th>'
+ N'<th align="left" bgcolor="#0b3d91" style="' + @TH + N'border-top-right-radius:7px;">Status</th>'
+ N'</tr>' + @BackupDetailsRows + @WC + @GAP;
-- 8.6 Section 4: Disk details
SET @HTML = @HTML
+ N'<table width="100%" cellpadding="0" cellspacing="0" border="0"><tr><td style="' + @SEC + N'">4. DISK DETAILS</td></tr></table>'
+ N'<table width="100%" cellpadding="0" cellspacing="0" border="0"><tr><td height="10" style="height:10px;font-size:1px;line-height:1px;"> </td></tr></table>'
+ @WO
+ N'<tr>'
+ N'<th align="left" bgcolor="#0b3d91" style="' + @TH + N'border-top-left-radius:7px;">Drive</th>'
+ N'<th align="left" bgcolor="#0b3d91" style="' + @TH + N'">Volume Label</th>'
+ N'<th align="left" bgcolor="#0b3d91" style="' + @TH + N'">Total (GB)</th>'
+ N'<th align="left" bgcolor="#0b3d91" style="' + @TH + N'">Used (GB)</th>'
+ N'<th align="left" bgcolor="#0b3d91" style="' + @TH + N'">Free (GB)</th>'
+ N'<th align="left" bgcolor="#0b3d91" style="' + @TH + N'">Free %</th>'
+ N'<th align="left" bgcolor="#0b3d91" style="' + @TH + N'border-top-right-radius:7px;">Status</th>'
+ N'</tr>' + @DiskDetailsRows + @WC + @GAP;
-- 8.7 Section 5: Agent jobs
SET @HTML = @HTML
+ N'<table width="100%" cellpadding="0" cellspacing="0" border="0"><tr><td style="' + @SEC + N'">5. SQL AGENT JOBS DETAILS</td></tr></table>'
+ N'<table width="100%" cellpadding="0" cellspacing="0" border="0"><tr><td height="10" style="height:10px;font-size:1px;line-height:1px;"> </td></tr></table>'
+ @WO
+ N'<tr>'
+ N'<th align="left" bgcolor="#0b3d91" style="' + @TH + N'border-top-left-radius:7px;">Job Name</th>'
+ N'<th align="left" bgcolor="#0b3d91" style="' + @TH + N'">Enabled</th>'
+ N'<th align="left" bgcolor="#0b3d91" style="' + @TH + N'">Current State</th>'
+ N'<th align="left" bgcolor="#0b3d91" style="' + @TH + N'">Last Run Status</th>'
+ N'<th align="left" bgcolor="#0b3d91" style="' + @TH + N'">Last Run Date/Time</th>'
+ N'<th align="left" bgcolor="#0b3d91" style="' + @TH + N'border-top-right-radius:7px;">Status</th>'
+ N'</tr>' + @JobDetailsRows + @WC + @GAP;
-- 8.8 Disclaimer + signature + close
SET @HTML = @HTML
+ N'<table width="100%" cellpadding="0" cellspacing="0" border="0"><tr><td style="' + @SEC + N'">6. DISCLAIMER</td></tr></table>'
+ N'<table width="100%" cellpadding="0" cellspacing="0" border="0"><tr><td height="10" style="height:10px;font-size:1px;line-height:1px;"> </td></tr></table>'
+ N'<table width="100%" cellpadding="0" cellspacing="0" border="0" bgcolor="#fdf6e3" style="border:1px solid #e0e0e0;"><tr>'
+ N'<td align="center" style="padding:12px 15px;font-size:12px;color:#7a5c00;font-style:italic;' + @Font + N'">This is an automated mail please do not respond to this mail</td>'
+ N'</tr></table>'
+ @GAP
+ N'<table width="100%" cellpadding="0" cellspacing="0" border="0"><tr><td style="' + @SEC + N'">7. SIGNATURE</td></tr></table>'
+ N'<table width="100%" cellpadding="0" cellspacing="0" border="0"><tr><td height="10" style="height:10px;font-size:1px;line-height:1px;"> </td></tr></table>'
+ N'<table width="100%" cellpadding="0" cellspacing="0" border="0" bgcolor="#eaf3fb" style="border:1px solid #e0e0e0;"><tr>'
+ N'<td style="padding:15px;font-size:13px;color:#2c3e50;' + @Font + N'">'
+ N'Regards,<br><b style="color:#0b3d91;">Database Administration Team</b><br>Automated SQL Server Health Monitoring'
+ N'</td></tr></table>'
+ N'</td></tr></table></td></tr></table></body></html>';
----------------------------------------------------------------------
-- 9. Send mail
----------------------------------------------------------------------
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @ProfileName,
@recipients = @Recipients,
@subject = @Subject,
@body = @HTML,
@body_format = 'HTML';
----------------------------------------------------------------------
-- 10. 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;
IF OBJECT_ID('tempdb..#JobDetails') IS NOT NULL DROP TABLE #JobDetails;
END
GO
Post a Comment