another color sql health check report

Here is the code.

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

SQL SERVER HEALTH CHECK REPORT (OUTLOOK COMPATIBLE & CUSTOM THEMED)

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

Fixes applied for Outlook / OWA:

1. Replaced <style> block and class attributes with fully inline CSS.

2. Wrapped entire document in a centered wrapper table (width 100%).

3. Replaced `border-spacing` with dedicated spacer columns for the 5 cards.

4. Applied custom background, header, and typography colors (Black text).

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

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,

@MonitoredJobsList NVARCHAR(MAX) = N'YourBackupJobName,YourMaintenanceJobName'

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

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,

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.create_date, 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

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

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 @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'

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

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

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 CHARINDEX(',' + j.name + ',', ',' + @MonitoredJobsList + ',') > 0;

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 @JobTotalCount INT, @JobFailedCount INT;

SELECT @JobTotalCount = COUNT(*), @JobFailedCount = SUM(CASE WHEN Status = N'CheckNow' THEN 1 ELSE 0 END)

FROM #JobDetails;

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

-- 5. Build HTML row fragments with Inline CSS

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

DECLARE @TdStyle NVARCHAR(255) = N'style="padding:8px 10px; border-bottom:1px solid #adadad; font-family:Segoe UI, Arial, sans-serif; font-size:13px; color:#000000;"';

DECLARE @TdAltStyle NVARCHAR(255) = N'style="padding:8px 10px; border-bottom:1px solid #adadad; font-family:Segoe UI, Arial, sans-serif; font-size:13px; color:#000000; background-color:#e2e2e2;"';

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 =

N'<tr>' +

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

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

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

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

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

N'<td ' + @TdStyle + N'><span style="color:#ffffff; background-color:' + @ServerStatusColor + N'; padding:3px 8px; font-size:12px; font-weight:bold; font-family:Segoe UI, Arial, sans-serif;">' + @ServerStatusText + N'</span></td>' +

N'</tr>';

-- Database Details

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

DECLARE @DbRowCounter INT = 0;

DECLARE db_cur CURSOR LOCAL FAST_FORWARD FOR

SELECT DatabaseName, 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 @DbRowCounter = @DbRowCounter + 1;

DECLARE @CurrentTdDb NVARCHAR(255) = CASE WHEN @DbRowCounter % 2 = 0 THEN @TdAltStyle ELSE @TdStyle END;

SET @DatabaseDetailsRows = @DatabaseDetailsRows +

N'<tr>' +

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

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

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

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

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

N'<td ' + @CurrentTdDb + N'><span style="color:#ffffff; background-color:' + @DbColor + N'; padding:3px 8px; font-size:12px; font-weight:bold; font-family:Segoe UI, Arial, sans-serif;">' + @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;

-- Backup Details

DECLARE @BkName NVARCHAR(128), @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 @BkRowCounter INT = 0;

DECLARE bk_cur CURSOR LOCAL FAST_FORWARD FOR

SELECT DatabaseName, 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 @BkRowCounter = @BkRowCounter + 1;

DECLARE @CurrentTdBk NVARCHAR(255) = CASE WHEN @BkRowCounter % 2 = 0 THEN @TdAltStyle ELSE @TdStyle END;

SET @BackupDetailsRows = @BackupDetailsRows +

N'<tr>' +

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

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

N'<td ' + @CurrentTdBk + N'>' + @BkFullDt + N'</td>' +

N'<td ' + @CurrentTdBk + N'>' + @BkFullAge + N'</td>' +

N'<td ' + @CurrentTdBk + N'>' + @BkDiffDt + N'</td>' +

N'<td ' + @CurrentTdBk + N'>' + @BkDiffAge + N'</td>' +

N'<td ' + @CurrentTdBk + N'>' + @BkLogDt + N'</td>' +

N'<td ' + @CurrentTdBk + N'>' + @BkLogAge + N'</td>' +

N'<td ' + @CurrentTdBk + N'><span style="color:#ffffff; background-color:' + @BkColor + N'; padding:3px 8px; font-size:12px; font-weight:bold; font-family:Segoe UI, Arial, sans-serif;">' + @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;

-- 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 @DkRowCounter INT = 0;

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 @DkRowCounter = @DkRowCounter + 1;

DECLARE @CurrentTdDk NVARCHAR(255) = CASE WHEN @DkRowCounter % 2 = 0 THEN @TdAltStyle ELSE @TdStyle END;

SET @DiskDetailsRows = @DiskDetailsRows +

N'<tr>' +

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

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

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

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

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

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

N'<td ' + @CurrentTdDk + N'><span style="color:#ffffff; background-color:' + @DkColor + N'; padding:3px 8px; font-size:12px; font-weight:bold; font-family:Segoe UI, Arial, sans-serif;">' + @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;

-- Job Details

DECLARE @JbName NVARCHAR(128), @JbEnabled NVARCHAR(10), @JbCurrentState NVARCHAR(20),

@JbLastRunStatus NVARCHAR(30), @JbLastRunDateTime NVARCHAR(20),

@JbStatus NVARCHAR(20), @JbColor NVARCHAR(20);

DECLARE @JbRowCounter INT = 0;

DECLARE job_cur CURSOR LOCAL FAST_FORWARD FOR

SELECT JobName,

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 @JbRowCounter = @JbRowCounter + 1;

DECLARE @CurrentTdJb NVARCHAR(255) = CASE WHEN @JbRowCounter % 2 = 0 THEN @TdAltStyle ELSE @TdStyle END;

SET @JobDetailsRows = @JobDetailsRows +

N'<tr>' +

N'<td ' + @CurrentTdJb + N'>' + @JbName + N'</td>' +

N'<td ' + @CurrentTdJb + N'>' + @JbEnabled + N'</td>' +

N'<td ' + @CurrentTdJb + N'>' + @JbCurrentState + N'</td>' +

N'<td ' + @CurrentTdJb + N'>' + @JbLastRunStatus + N'</td>' +

N'<td ' + @CurrentTdJb + N'>' + @JbLastRunDateTime + N'</td>' +

N'<td ' + @CurrentTdJb + N'><span style="color:#ffffff; background-color:' + @JbColor + N'; padding:3px 8px; font-size:12px; font-weight:bold; font-family:Segoe UI, Arial, sans-serif;">' + @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;

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

-- 6. Dashboard card status logic

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

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

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

-- 7. Assemble final Outlook-Compatible HTML

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

DECLARE @HTML NVARCHAR(MAX);

DECLARE @ThStyle NVARCHAR(255) = N'style="background-color:#adadad; color:#000000; text-align:left; padding:8px 10px; font-family:Segoe UI, Arial, sans-serif; font-size:13px; font-weight:bold; border-bottom:2px solid #000000;"';

SET @HTML = N'

<html>

<head>

</head>

<tr>

<tr>

<td>

<tr>

<h1 style="color:#000000; font-size:24px; margin:0 0 10px 0; font-family:Segoe UI, Arial, sans-serif;">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>

</td>

</tr>

</table>

<h2 style="color:#000000; font-size:16px; border-bottom:2px solid #000000; padding-bottom:5px; margin-top:20px; font-family:Segoe UI, Arial, sans-serif;">HEALTH SUMMARY DASHBOARD</h2>

<tr>

<div style="font-size:12px; font-weight:bold; color:#000000; font-family:Segoe UI, Arial, sans-serif;">SERVER STATUS</div>

<div style="font-size:18px; font-weight:bold; margin:6px 0 4px 0; color:' + @CardServerColor + N'; font-family:Segoe UI, Arial, sans-serif;">' + CASE WHEN @ServerIsHealthy = 1 THEN N'Running' ELSE N'Recently Restarted' END + N'</div>

<div style="font-size:12px; color:#000000; font-family:Segoe UI, Arial, sans-serif;">Uptime: ' + CONVERT(NVARCHAR(10), @UptimeHrs) + N' Hrs</div>

<div style="margin-top:8px;"><span style="color:#ffffff; background-color:' + @CardServerColor + N'; padding:3px 8px; font-size:11px; font-weight:bold; font-family:Segoe UI, Arial, sans-serif;">' + @CardServerStatus + N'</span></div>

</td>

<div style="font-size:12px; font-weight:bold; color:#000000; font-family:Segoe UI, Arial, sans-serif;">DATABASES</div>

<div style="font-size:18px; font-weight:bold; margin:6px 0 4px 0; color:' + @CardDbColor + N'; font-family:Segoe UI, Arial, sans-serif;">' + CONVERT(NVARCHAR(10), @DbOnlineCount) + N' / ' + CONVERT(NVARCHAR(10), @DbTotalCount) + N' Online</div>

<div style="font-size:12px; color:#000000; font-family:Segoe UI, Arial, sans-serif;">' + CONVERT(NVARCHAR(10), @DbTotalCount - @DbOnlineCount) + N' Offline / Suspect</div>

<div style="margin-top:8px;"><span style="color:#ffffff; background-color:' + @CardDbColor + N'; padding:3px 8px; font-size:11px; font-weight:bold; font-family:Segoe UI, Arial, sans-serif;">' + @CardDbStatus + N'</span></div>

</td>

<div style="font-size:12px; font-weight:bold; color:#000000; font-family:Segoe UI, Arial, sans-serif;">BACKUPS</div>

<div style="font-size:18px; font-weight:bold; margin:6px 0 4px 0; color:' + @CardBackupColor + N'; font-family:Segoe UI, Arial, sans-serif;">' + CASE WHEN ISNULL(@BackupOverdueCount,0) = 0 THEN N'All Successful' ELSE N'Attention Needed' END + N'</div>

<div style="font-size:12px; color:#000000; font-family:Segoe UI, Arial, sans-serif;">' + CONVERT(NVARCHAR(10), ISNULL(@BackupOverdueCount,0)) + N' Overdue</div>

<div style="margin-top:8px;"><span style="color:#ffffff; background-color:' + @CardBackupColor + N'; padding:3px 8px; font-size:11px; font-weight:bold; font-family:Segoe UI, Arial, sans-serif;">' + @CardBackupStatus + N'</span></div>

</td>

<div style="font-size:12px; font-weight:bold; color:#000000; font-family:Segoe UI, Arial, sans-serif;">DISK SPACE</div>

<div style="font-size:18px; font-weight:bold; margin:6px 0 4px 0; color:' + @CardDiskColor + N'; font-family:Segoe UI, Arial, sans-serif;">' + ISNULL(CONVERT(NVARCHAR(10), @DiskWorstPct), N'N/A') + N'% Free</div>

<div style="font-size:12px; color:#000000; font-family:Segoe UI, Arial, sans-serif;">Lowest Free Space</div>

<div style="margin-top:8px;"><span style="color:#ffffff; background-color:' + @CardDiskColor + N'; padding:3px 8px; font-size:11px; font-weight:bold; font-family:Segoe UI, Arial, sans-serif;">' + @CardDiskStatus + N'</span></div>

</td>

<div style="font-size:12px; font-weight:bold; color:#000000; font-family:Segoe UI, Arial, sans-serif;">AGENT JOBS</div>

<div style="font-size:18px; font-weight:bold; margin:6px 0 4px 0; color:' + @CardJobColor + N'; font-family:Segoe UI, Arial, sans-serif;">' + CASE WHEN ISNULL(@JobFailedCount,0) = 0 THEN N'All Successful' ELSE N'Attention Needed' END + N'</div>

<div style="font-size:12px; color:#000000; font-family:Segoe UI, Arial, sans-serif;">' + CONVERT(NVARCHAR(10), ISNULL(@JobFailedCount,0)) + N' Failed / Unknown</div>

<div style="margin-top:8px;"><span style="color:#ffffff; background-color:' + @CardJobColor + N'; padding:3px 8px; font-size:11px; font-weight:bold; font-family:Segoe UI, Arial, sans-serif;">' + @CardJobStatus + N'</span></div>

</td>

</tr>

</table>

<h2 style="color:#000000; font-size:16px; border-bottom:2px solid #000000; padding-bottom:5px; margin-top:25px; font-family:Segoe UI, Arial, sans-serif;">1. SERVER DETAILS</h2>

<tr>

<th ' + @ThStyle + N'>Hostname</th>

<th ' + @ThStyle + N'>Is Cluster</th>

<th ' + @ThStyle + N'>Current Node</th>

<th ' + @ThStyle + N'>SQL Restart</th>

<th ' + @ThStyle + N'>Uptime (Hrs)</th>

<th ' + @ThStyle + N'>Status</th>

</tr>

' + @ServerDetailsRows + N'

</table>

<h2 style="color:#000000; font-size:16px; border-bottom:2px solid #000000; padding-bottom:5px; margin-top:25px; font-family:Segoe UI, Arial, sans-serif;">2. DATABASE DETAILS</h2>

<tr>

<th ' + @ThStyle + N'>Database Name</th>

<th ' + @ThStyle + N'>Created Date</th>

<th ' + @ThStyle + N'>Recovery Model</th>

<th ' + @ThStyle + N'>Database Size (GB)</th>

<th ' + @ThStyle + N'>State</th>

<th ' + @ThStyle + N'>Status</th>

</tr>

' + @DatabaseDetailsRows + N'

</table>

<h2 style="color:#000000; font-size:16px; border-bottom:2px solid #000000; padding-bottom:5px; margin-top:25px; font-family:Segoe UI, Arial, sans-serif;">3. DATABASE BACKUP DETAILS</h2>

<tr>

<th ' + @ThStyle + N'>Database Name</th>

<th ' + @ThStyle + N'>Recovery Model</th>

<th ' + @ThStyle + N'>Full Backup</th>

<th ' + @ThStyle + N'>Full Backup Age</th>

<th ' + @ThStyle + N'>Diff Backup</th>

<th ' + @ThStyle + N'>Diff Backup Age</th>

<th ' + @ThStyle + N'>Log Backup</th>

<th ' + @ThStyle + N'>Log Backup Age</th>

<th ' + @ThStyle + N'>Status</th>

</tr>

' + @BackupDetailsRows + N'

</table>

<h2 style="color:#000000; font-size:16px; border-bottom:2px solid #000000; padding-bottom:5px; margin-top:25px; font-family:Segoe UI, Arial, sans-serif;">4. DISK DETAILS</h2>

<tr>

<th ' + @ThStyle + N'>Drive</th>

<th ' + @ThStyle + N'>Volume Label</th>

<th ' + @ThStyle + N'>Total (GB)</th>

<th ' + @ThStyle + N'>Status</th>

</tr>

' + @DiskDetailsRows + N'

</table>

<h2 style="color:#000000; font-size:16px; border-bottom:2px solid #000000; padding-bottom:5px; margin-top:25px; font-family:Segoe UI, Arial, sans-serif;">5. SQL AGENT JOBS DETAILS</h2>

<tr>

<th ' + @ThStyle + N'>Enabled</th>

<th ' + @ThStyle + N'>Current State</th>

<th ' + @ThStyle + N'>Last Run Status</th>

<th ' + @ThStyle + N'>Status</th>

</tr>

' + @JobDetailsRows + N'

</table>

<h2 style="color:#000000; font-size:16px; border-bottom:2px solid #000000; padding-bottom:5px; margin-top:25px; font-family:Segoe UI, Arial, sans-serif;">6. DISCLAIMER</h2>

This is an automated email please do not respond to this email

</div>

<h2 style="color:#000000; font-size:16px; border-bottom:2px solid #000000; padding-bottom:5px; margin-top:25px; font-family:Segoe UI, Arial, sans-serif;">7. SIGNATURE</h2>

<div>Regards,</div>

<div style="font-weight:bold; margin-top:4px;">Database Administration Team</div>

<div>Automated SQL Server Health Monitoring</div>

</div>

</td>

</tr>

</table>

</td>

</tr>

</table>

</body>

</html>';

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

-- 8. Send mail

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

EXEC msdb.dbo.sp_send_dbmail

@profile_name = @ProfileName,

@recipients = @Recipients,

@subject = @Subject,

@body = @HTML,

@body_format = 'HTML';

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

-- 9. 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

Labels

Technology

another color sql health check report

Post a Comment

Author Name

Contact Form