sql server health check via email

Here is one at 6:40

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

SQL SERVER HEALTH CHECK REPORT (WITH AGENT JOBS & ENHANCEMENTS)

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

Enhancements applied:

1. Health Summary Dashboard converted to a standard HTML table.

2. Added "Created Date" column to the DATABASE DETAILS table.

3. Fixed Outlook Web rendering issues (removed nested tables & border-radius).

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

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 (Added CreateDate)

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

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

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

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>' + @HostName + 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 style="color:#ffffff; padding:4px 10px; font-size:12px; font-weight:bold; display:inline-block; background-color:' + @ServerStatusColor + N';">' + @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 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 @DatabaseDetailsRows = @DatabaseDetailsRows +

N'<tr>' +

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

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

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

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

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

N'<td><span style="color:#ffffff; padding:4px 10px; font-size:12px; font-weight:bold; display:inline-block; background-color:' + @DbColor + N';">' + @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 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 @BackupDetailsRows = @BackupDetailsRows +

N'<tr>' +

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

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

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

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

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

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

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

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

N'<td><span style="color:#ffffff; padding:4px 10px; font-size:12px; font-weight:bold; display:inline-block; background-color:' + @BkColor + N';">' + @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 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 style="color:#ffffff; padding:4px 10px; font-size:12px; font-weight:bold; display:inline-block; 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;

-- 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 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 @JobDetailsRows = @JobDetailsRows +

N'<tr>' +

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

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

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

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

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

N'<td><span style="color:#ffffff; padding:4px 10px; font-size:12px; font-weight:bold; display:inline-block; background-color:' + @JbColor + N';">' + @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 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;

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

-- 7. Assemble final HTML

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

DECLARE @HTML NVARCHAR(MAX);

SET @HTML = N'

<html>

<head>

<style>

body { font-family: Segoe UI, Arial, sans-serif; background-color:#ffffff; margin:0; padding:0; color:#2c3e50; }

.container { max-width:1200px; margin:0 auto; padding:20px; background-color:#ffffff; }

.header-box { background-color:#0b3d91; color:#ffffff; text-align:center; padding:20px 15px; margin-bottom:10px; }

.header-box h1 { color:#ffffff; font-size:24px; margin:0 0 12px 0; }

.header-box .meta { color:#ffffff; font-size:13px; }

.header-box .meta span { margin:0 18px; }

h2.section-title { color:#0b3d91; font-size:16px; border-bottom:2px solid #0b3d91; padding-bottom:5px; margin-top:30px; margin-bottom:15px; }

table { border-collapse: collapse; width:100%; margin-top:10px; font-size:13px; }

th { background-color:#0b3d91; color:#ffffff; text-align:left; padding:8px 10px; border:1px solid #0b3d91; }

td { padding:8px 10px; border-bottom:1px solid #e0e0e0; }

tr:nth-child(even) { background-color:#f9fafc; }

.disclaimer-box { margin-top:30px; padding:12px 15px; background-color:#fdf6e3; font-size:12px; color:#7a5c00; text-align:center; font-style:italic; }

.signature-box { margin-top:15px; padding:15px; background-color:#eaf3fb; font-size:13px; color:#2c3e50; }

.signature-box .sig-name { font-weight:bold; color:#0b3d91; }

</style>

</head>

<body>

<h1>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>

<h2 class="section-title">HEALTH SUMMARY DASHBOARD</h2>

<tr>

<th style="background-color:#0b3d91; color:#ffffff; width:20%; border:1px solid #0b3d91; text-align:center;">SERVER STATUS</th>

<th style="background-color:#0b3d91; color:#ffffff; width:20%; border:1px solid #0b3d91; text-align:center;">DATABASES</th>

<th style="background-color:#0b3d91; color:#ffffff; width:20%; border:1px solid #0b3d91; text-align:center;">BACKUPS</th>

<th style="background-color:#0b3d91; color:#ffffff; width:20%; border:1px solid #0b3d91; text-align:center;">DISK SPACE</th>

<th style="background-color:#0b3d91; color:#ffffff; width:20%; border:1px solid #0b3d91; text-align:center;">AGENT JOBS</th>

</tr>

<tr>

<div style="font-size:18px; font-weight:bold; color:' + @CardServerColor + N'; margin-bottom:4px;">' + CASE WHEN @ServerIsHealthy = 1 THEN N'Running' ELSE N'Recently Restarted' END + N'</div>

<div style="font-size:12px; color:#777; margin-bottom:8px;">Uptime: ' + CONVERT(NVARCHAR(10), @UptimeHrs) + N' Hrs</div>

<span style="color:#ffffff; background-color:' + @CardServerColor + N'; padding:4px 10px; font-size:12px; font-weight:bold; display:inline-block;">' + @CardServerStatus + N'</span>

</td>

<div style="font-size:18px; font-weight:bold; color:' + @CardDbColor + N'; margin-bottom:4px;">' + CONVERT(NVARCHAR(10), @DbOnlineCount) + N' / ' + CONVERT(NVARCHAR(10), @DbTotalCount) + N' Online</div>

<div style="font-size:12px; color:#777; margin-bottom:8px;">' + CONVERT(NVARCHAR(10), @DbTotalCount - @DbOnlineCount) + N' Offline / Suspect</div>

<span style="color:#ffffff; background-color:' + @CardDbColor + N'; padding:4px 10px; font-size:12px; font-weight:bold; display:inline-block;">' + @CardDbStatus + N'</span>

</td>

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

<div style="font-size:12px; color:#777; margin-bottom:8px;">' + CONVERT(NVARCHAR(10), ISNULL(@BackupOverdueCount,0)) + N' Overdue</div>

<span style="color:#ffffff; background-color:' + @CardBackupColor + N'; padding:4px 10px; font-size:12px; font-weight:bold; display:inline-block;">' + @CardBackupStatus + N'</span>

</td>

<div style="font-size:18px; font-weight:bold; color:' + @CardDiskColor + N'; margin-bottom:4px;">' + ISNULL(CONVERT(NVARCHAR(10), @DiskWorstPct), N'N/A') + N'% Free</div>

<div style="font-size:12px; color:#777; margin-bottom:8px;">Lowest Free Space</div>

<span style="color:#ffffff; background-color:' + @CardDiskColor + N'; padding:4px 10px; font-size:12px; font-weight:bold; display:inline-block;">' + @CardDiskStatus + N'</span>

</td>

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

<div style="font-size:12px; color:#777; margin-bottom:8px;">' + CONVERT(NVARCHAR(10), ISNULL(@JobFailedCount,0)) + N' Failed / Unknown</div>

<span style="color:#ffffff; background-color:' + @CardJobColor + N'; padding:4px 10px; font-size:12px; font-weight:bold; display:inline-block;">' + @CardJobStatus + N'</span>

</td>

</tr>

</table>

<br />

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

<tr>

<th>Hostname</th><th>Is Cluster</th><th>Current Node</th><th>SQL Restart</th><th>Uptime (Hrs)</th><th>Status</th>

</tr>

' + @ServerDetailsRows + N'

</table>

<br />

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

<tr>

<th>Database Name</th><th>Created Date</th><th>Recovery Model</th><th>Database Size (GB)</th><th>State</th><th>Status</th>

</tr>

' + @DatabaseDetailsRows + N'

</table>

<br />

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

<tr>

<th>Database Name</th><th>Recovery Model</th><th>Full Backup</th><th>Full Backup Age</th><th>Diff Backup</th><th>Diff Backup Age</th><th>Log Backup</th><th>Log Backup Age</th><th>Status</th>

</tr>

' + @BackupDetailsRows + N'

</table>

<br />

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

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

<br />

<h2 class="section-title">5. SQL AGENT JOBS DETAILS</h2>

<tr>

<th>Job Name</th><th>Enabled</th><th>Current State</th><th>Last Run Status</th><th>Last Run Date/Time</th><th>Status</th>

</tr>

' + @JobDetailsRows + N'

</table>

<br />

<h2 class="section-title">6. DISCLAIMER</h2>

<div class="disclaimer-box">This is an automated mail please do not respond to this mail</div>

<h2 class="section-title">7. SIGNATURE</h2>

<div>Regards,</div>

<div class="sig-name">Database Administration Team</div>

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

</div>

</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

sql server health check via email

Post a Comment

Author Name

Contact Form