```sql
/* ============================================================================
SQL SERVER HEALTH CHECK REPORT
OUTLOOK WEB COMPATIBLE VERSION
----------------------------------------------------------------------------
Main changes:
1. Fully table-based HTML email layout.
2. Inline CSS only.
3. No CSS classes / external styles.
4. No DIV/SPAN dependency for dashboard cards.
5. NULL-safe HTML generation.
6. HTML encoding for server/database/job names.
7. Health Summary displayed as 5 table-based cards.
8. Server Details table always receives a row.
9. Empty result sets display "No data found".
============================================================================ */
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'
AS
BEGIN
SET NOCOUNT ON;
--------------------------------------------------------------------------
-- 0. COMMON VARIABLES
--------------------------------------------------------------------------
DECLARE @GetDate DATETIME = GETDATE();
DECLARE @HostName NVARCHAR(128) =
ISNULL(CONVERT(NVARCHAR(128), SERVERPROPERTY('MachineName')), N'N/A');
DECLARE @ServerName NVARCHAR(128) =
ISNULL(CONVERT(NVARCHAR(128), SERVERPROPERTY('ServerName')), N'N/A');
DECLARE @Edition NVARCHAR(128) =
ISNULL(CONVERT(NVARCHAR(128), SERVERPROPERTY('Edition')), N'N/A');
DECLARE @ProductVersion NVARCHAR(128) =
ISNULL(CONVERT(NVARCHAR(128), SERVERPROPERTY('ProductVersion')), N'N/A');
DECLARE @IsClusteredInt INT =
ISNULL(CONVERT(INT, SERVERPROPERTY('IsClustered')), 0);
DECLARE @IsClustered NVARCHAR(10) =
CASE
WHEN @IsClusteredInt = 1 THEN N'Yes'
ELSE N'No'
END;
DECLARE @CurrentNode NVARCHAR(128) =
ISNULL(
CONVERT(NVARCHAR(128),
SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
),
@HostName
);
DECLARE @SQLRestart DATETIME;
DECLARE @UptimeHrs INT = 0;
DECLARE @SQLVersionFull NVARCHAR(400) =
ISNULL(@@VERSION, N'N/A');
SELECT
@SQLRestart = sqlserver_start_time
FROM sys.dm_os_sys_info;
IF @SQLRestart IS NOT NULL
BEGIN
SET @UptimeHrs =
DATEDIFF(HOUR, @SQLRestart, @GetDate);
END
ELSE
BEGIN
SET @UptimeHrs = 0;
END;
SET @SQLVersionFull =
LTRIM(
RTRIM(
LEFT(
@SQLVersionFull,
CHARINDEX(
CHAR(10),
@SQLVersionFull + CHAR(10)
) - 1
)
)
);
IF NULLIF(@SQLVersionFull, N'') IS NULL
SET @SQLVersionFull = N'N/A';
IF @Subject IS NULL
BEGIN
SET @Subject =
N'SQL Server Health Check Report - '
+ @ServerName
+ N' - '
+ CONVERT(NVARCHAR(20), @GetDate, 120);
END;
--------------------------------------------------------------------------
-- 1. SERVER STATUS
--------------------------------------------------------------------------
DECLARE @ServerStatusText NVARCHAR(20);
DECLARE @ServerStatusColor NVARCHAR(20);
DECLARE @ServerIsHealthy BIT;
IF @UptimeHrs < 24
BEGIN
SET @ServerStatusText = N'CHECK NOW';
SET @ServerStatusColor = N'#C0392B';
SET @ServerIsHealthy = 0;
END
ELSE
BEGIN
SET @ServerStatusText = N'RELAX';
SET @ServerStatusColor = N'#1E7E34';
SET @ServerIsHealthy = 1;
END;
--------------------------------------------------------------------------
-- 2. 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;
DECLARE @DbTotalCount INT = 0;
DECLARE @DbOnlineCount INT = 0;
SELECT
@DbTotalCount = COUNT(*),
@DbOnlineCount =
ISNULL(
SUM(
CASE
WHEN DbState = 'ONLINE' THEN 1
ELSE 0
END
),
0
)
FROM #DatabaseDetails;
--------------------------------------------------------------------------
-- 3. 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 = 0;
DECLARE @BackupOverdueCount INT = 0;
SELECT
@BackupTotalCount = COUNT(*),
@BackupOverdueCount =
ISNULL(
SUM(
CASE
WHEN Status = 'CheckNow'
THEN 1
ELSE 0
END
),
0
)
FROM #BackupDetails;
--------------------------------------------------------------------------
-- 4. DISK DETAILS
--------------------------------------------------------------------------
IF OBJECT_ID('tempdb..#DiskDetails') IS NOT NULL
DROP TABLE #DiskDetails;
SELECT DISTINCT
ISNULL(vs.volume_mount_point, N'N/A') 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 mf
CROSS APPLY
sys.dm_os_volume_stats(
mf.database_id,
mf.file_id
) 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);
DECLARE @DiskAnyWarning BIT = 0;
DECLARE @DiskAnyCritical BIT = 0;
SELECT
@DiskWorstPct = MIN(FreePct)
FROM #DiskDetails;
IF EXISTS
(
SELECT 1
FROM #DiskDetails
WHERE FreePct < @LowDiskCriticalPct
)
BEGIN
SET @DiskAnyCritical = 1;
END
ELSE IF EXISTS
(
SELECT 1
FROM #DiskDetails
WHERE FreePct < @LowDiskWarningPct
)
BEGIN
SET @DiskAnyWarning = 1;
END;
--------------------------------------------------------------------------
-- 5. 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(
N',' + j.name + N',',
N',' + ISNULL(@MonitoredJobsList, N'') + N','
) > 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 = 0;
DECLARE @JobFailedCount INT = 0;
SELECT
@JobTotalCount = COUNT(*),
@JobFailedCount =
ISNULL(
SUM(
CASE
WHEN Status = N'CheckNow'
THEN 1
ELSE 0
END
),
0
)
FROM #JobDetails;
--------------------------------------------------------------------------
-- 6. HTML ROW VARIABLES
--------------------------------------------------------------------------
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'';
--------------------------------------------------------------------------
-- 7. SERVER DETAILS ROW
--
-- IMPORTANT:
-- Every value uses ISNULL.
-- This prevents one NULL value from making the entire HTML row NULL.
--------------------------------------------------------------------------
SET @ServerDetailsRows =
N'<tr>' +
N'<td style="border:1px solid #D9E2F3;padding:8px;">'
+ ISNULL(
(SELECT
(SELECT @HostName FOR XML PATH(''), TYPE)
).value('.', 'NVARCHAR(MAX)'),
N'N/A'
)
+ N'</td>' +
N'<td style="border:1px solid #D9E2F3;padding:8px;text-align:center;">'
+ ISNULL(@IsClustered, N'N/A')
+ N'</td>' +
N'<td style="border:1px solid #D9E2F3;padding:8px;">'
+ ISNULL(@CurrentNode, N'N/A')
+ N'</td>' +
N'<td style="border:1px solid #D9E2F3;padding:8px;">'
+ ISNULL(
CONVERT(NVARCHAR(20), @SQLRestart, 120),
N'N/A'
)
+ N'</td>' +
N'<td style="border:1px solid #D9E2F3;padding:8px;text-align:center;">'
+ ISNULL(
CONVERT(NVARCHAR(10), @UptimeHrs),
N'0'
)
+ N'</td>' +
N'<td style="border:1px solid #D9E2F3;padding:8px;text-align:center;">'
+ N'<b style="color:#FFFFFF;background-color:'
+ ISNULL(@ServerStatusColor, N'#C0392B')
+ N';padding:4px 8px;">'
+ ISNULL(@ServerStatusText, N'CHECK NOW')
+ N'</b>'
+ N'</td>' +
N'</tr>';
--------------------------------------------------------------------------
-- 8. DATABASE DETAILS ROWS
--------------------------------------------------------------------------
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 style="border:1px solid #D9E2F3;padding:8px;">'
+ ISNULL(@DbName, N'N/A')
+ N'</td>' +
N'<td style="border:1px solid #D9E2F3;padding:8px;">'
+ ISNULL(
CONVERT(NVARCHAR(20), @DbCreateDate, 120),
N'N/A'
)
+ N'</td>' +
N'<td style="border:1px solid #D9E2F3;padding:8px;">'
+ ISNULL(@DbRecovery, N'N/A')
+ N'</td>' +
N'<td style="border:1px solid #D9E2F3;padding:8px;text-align:right;">'
+ ISNULL(
CONVERT(NVARCHAR(20), @DbSize),
N'0'
)
+ N'</td>' +
N'<td style="border:1px solid #D9E2F3;padding:8px;">'
+ ISNULL(@DbState, N'N/A')
+ N'</td>' +
N'<td style="border:1px solid #D9E2F3;padding:8px;text-align:center;">'
+ N'<b style="color:#FFFFFF;background-color:'
+ ISNULL(@DbColor, N'#C0392B')
+ N';padding:4px 8px;">'
+ ISNULL(@DbStatus, N'CHECK NOW')
+ N'</b>'
+ N'</td>' +
N'</tr>';
FETCH NEXT FROM db_cur
INTO
@DbName,
@DbCreateDate,
@DbRecovery,
@DbState,
@DbSize,
@DbStatus,
@DbColor;
END;
CLOSE db_cur;
DEALLOCATE db_cur;
IF NULLIF(@DatabaseDetailsRows, N'') IS NULL
BEGIN
SET @DatabaseDetailsRows =
N'<tr><td colspan="6" style="padding:12px;text-align:center;color:#777;">'
+ N'No database information found.'
+ N'</td></tr>';
END;
--------------------------------------------------------------------------
-- 9. BACKUP DETAILS ROWS
--------------------------------------------------------------------------
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 style="border:1px solid #D9E2F3;padding:8px;">'
+ ISNULL(@BkName, N'N/A')
+ N'</td>' +
N'<td style="border:1px solid #D9E2F3;padding:8px;">'
+ ISNULL(@BkRecovery, N'N/A')
+ N'</td>' +
N'<td style="border:1px solid #D9E2F3;padding:8px;">'
+ ISNULL(@BkFullDt, N'Never')
+ N'</td>' +
N'<td style="border:1px solid #D9E2F3;padding:8px;">'
+ ISNULL(@BkFullAge, N'Never')
+ N'</td>' +
N'<td style="border:1px solid #D9E2F3;padding:8px;">'
+ ISNULL(@BkDiffDt, N'Never')
+ N'</td>' +
N'<td style="border:1px solid #D9E2F3;padding:8px;">'
+ ISNULL(@BkDiffAge, N'Never')
+ N'</td>' +
N'<td style="border:1px solid #D9E2F3;padding:8px;">'
+ ISNULL(@BkLogDt, N'N/A')
+ N'</td>' +
N'<td style="border:1px solid #D9E2F3;padding:8px;">'
+ ISNULL(@BkLogAge, N'N/A')
+ N'</td>' +
N'<td style="border:1px solid #D9E2F3;padding:8px;text-align:center;">'
+ N'<b style="color:#FFFFFF;background-color:'
+ ISNULL(@BkColor, N'#C0392B')
+ N';padding:4px 8px;">'
+ ISNULL(@BkStatus, N'CHECK NOW')
+ N'</b>'
+ N'</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 NULLIF(@BackupDetailsRows, N'') IS NULL
BEGIN
SET @BackupDetailsRows =
N'<tr><td colspan="9" style="padding:12px;text-align:center;color:#777;">'
+ N'No backup information found.'
+ N'</td></tr>';
END;
--------------------------------------------------------------------------
-- 10. DISK DETAILS ROWS
--------------------------------------------------------------------------
DECLARE
@DkDrive NVARCHAR(128),
@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 style="border:1px solid #D9E2F3;padding:8px;">'
+ ISNULL(@DkDrive, N'N/A')
+ N'</td>' +
N'<td style="border:1px solid #D9E2F3;padding:8px;">'
+ ISNULL(@DkLabel, N'')
+ N'</td>' +
N'<td style="border:1px solid #D9E2F3;padding:8px;text-align:right;">'
+ ISNULL(CONVERT(NVARCHAR(20), @DkTotal), N'0')
+ N'</td>' +
N'<td style="border:1px solid #D9E2F3;padding:8px;text-align:right;">'
+ ISNULL(CONVERT(NVARCHAR(20), @DkUsed), N'0')
+ N'</td>' +
N'<td style="border:1px solid #D9E2F3;padding:8px;text-align:right;">'
+ ISNULL(CONVERT(NVARCHAR(20), @DkFree), N'0')
+ N'</td>' +
N'<td style="border:1px solid #D9E2F3;padding:8px;text-align:right;">'
+ ISNULL(CONVERT(NVARCHAR(20), @DkPct), N'0')
+ N'%</td>' +
N'<td style="border:1px solid #D9E2F3;padding:8px;text-align:center;">'
+ N'<b style="color:#FFFFFF;background-color:'
+ ISNULL(@DkColor, N'#C0392B')
+ N';padding:4px 8px;">'
+ ISNULL(@DkStatus, N'CHECK NOW')
+ N'</b>'
+ N'</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 NULLIF(@DiskDetailsRows, N'') IS NULL
BEGIN
SET @DiskDetailsRows =
N'<tr><td colspan="7" style="padding:12px;text-align:center;color:#777;">'
+ N'No disk information found.'
+ N'</td></tr>';
END;
--------------------------------------------------------------------------
-- 11. SQL AGENT JOB ROWS
--------------------------------------------------------------------------
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 style="border:1px solid #D9E2F3;padding:8px;">'
+ ISNULL(@JbName, N'N/A')
+ N'</td>' +
N'<td style="border:1px solid #D9E2F3;padding:8px;text-align:center;">'
+ ISNULL(@JbEnabled, N'No')
+ N'</td>' +
N'<td style="border:1px solid #D9E2F3;padding:8px;">'
+ ISNULL(@JbCurrentState, N'Idle')
+ N'</td>' +
N'<td style="border:1px solid #D9E2F3;padding:8px;">'
+ ISNULL(@JbLastRunStatus, N'Unknown')
+ N'</td>' +
N'<td style="border:1px solid #D9E2F3;padding:8px;">'
+ ISNULL(@JbLastRunDateTime, N'Never')
+ N'</td>' +
N'<td style="border:1px solid #D9E2F3;padding:8px;text-align:center;">'
+ N'<b style="color:#FFFFFF;background-color:'
+ ISNULL(@JbColor, N'#C0392B')
+ N';padding:4px 8px;">'
+ ISNULL(@JbStatus, N'CHECK NOW')
+ N'</b>'
+ N'</td>' +
N'</tr>';
FETCH NEXT FROM job_cur
INTO
@JbName,
@JbEnabled,
@JbCurrentState,
@JbLastRunStatus,
@JbLastRunDateTime,
@JbStatus,
@JbColor;
END;
CLOSE job_cur;
DEALLOCATE job_cur;
IF NULLIF(@JobDetailsRows, N'') IS NULL
BEGIN
SET @JobDetailsRows =
N'<tr><td colspan="6" style="padding:12px;text-align:center;color:#777;">'
+ N'No monitored SQL Agent jobs found.'
+ N'</td></tr>';
END;
--------------------------------------------------------------------------
-- 12. DASHBOARD CARD VALUES
--------------------------------------------------------------------------
DECLARE @CardServerStatus NVARCHAR(20);
DECLARE @CardServerColor NVARCHAR(20);
DECLARE @CardServerBg NVARCHAR(20);
SET @CardServerStatus =
CASE
WHEN @ServerIsHealthy = 1
THEN N'HEALTHY'
ELSE N'WARNING'
END;
SET @CardServerColor =
CASE
WHEN @ServerIsHealthy = 1
THEN N'#1E7E34'
ELSE N'#C0392B'
END;
SET @CardServerBg =
CASE
WHEN @ServerIsHealthy = 1
THEN N'#EAF7EF'
ELSE N'#FDECEA'
END;
DECLARE @CardDbStatus NVARCHAR(20);
DECLARE @CardDbColor NVARCHAR(20);
DECLARE @CardDbBg NVARCHAR(20);
SET @CardDbStatus =
CASE
WHEN @DbOnlineCount = @DbTotalCount
THEN N'HEALTHY'
ELSE N'WARNING'
END;
SET @CardDbColor =
CASE
WHEN @DbOnlineCount = @DbTotalCount
THEN N'#1E7E34'
ELSE N'#C0392B'
END;
SET @CardDbBg =
CASE
WHEN @DbOnlineCount = @DbTotalCount
THEN N'#EAF7EF'
ELSE N'#FDECEA'
END;
DECLARE @CardBackupStatus NVARCHAR(20);
DECLARE @CardBackupColor NVARCHAR(20);
DECLARE @CardBackupBg NVARCHAR(20);
SET @CardBackupStatus =
CASE
WHEN ISNULL(@BackupOverdueCount, 0) = 0
THEN N'HEALTHY'
ELSE N'WARNING'
END;
SET @CardBackupColor =
CASE
WHEN ISNULL(@BackupOverdueCount, 0) = 0
THEN N'#1E7E34'
ELSE N'#C0392B'
END;
SET @CardBackupBg =
CASE
WHEN ISNULL(@BackupOverdueCount, 0) = 0
THEN N'#EAF7EF'
ELSE N'#FDECEA'
END;
DECLARE @CardDiskStatus NVARCHAR(20);
DECLARE @CardDiskColor NVARCHAR(20);
DECLARE @CardDiskBg NVARCHAR(20);
SET @CardDiskStatus =
CASE
WHEN @DiskAnyCritical = 1
OR @DiskAnyWarning = 1
THEN N'WARNING'
ELSE N'HEALTHY'
END;
SET @CardDiskColor =
CASE
WHEN @DiskAnyCritical = 1
OR @DiskAnyWarning = 1
THEN N'#C0392B'
ELSE N'#1E7E34'
END;
SET @CardDiskBg =
CASE
WHEN @DiskAnyCritical = 1
OR @DiskAnyWarning = 1
THEN N'#FDECEA'
ELSE N'#EAF7EF'
END;
DECLARE @CardJobStatus NVARCHAR(20);
DECLARE @CardJobColor NVARCHAR(20);
DECLARE @CardJobBg NVARCHAR(20);
SET @CardJobStatus =
CASE
WHEN ISNULL(@JobFailedCount, 0) = 0
THEN N'HEALTHY'
ELSE N'WARNING'
END;
SET @CardJobColor =
CASE
WHEN ISNULL(@JobFailedCount, 0) = 0
THEN N'#1E7E34'
ELSE N'#C0392B'
END;
SET @CardJobBg =
CASE
WHEN ISNULL(@JobFailedCount, 0) = 0
THEN N'#EAF7EF'
ELSE N'#FDECEA'
END;
--------------------------------------------------------------------------
-- 13. FINAL HTML
--
-- IMPORTANT:
-- Everything is table-based and inline styled for Outlook Web.
--------------------------------------------------------------------------
DECLARE @HTML NVARCHAR(MAX);
SET @HTML =
N'<html>' +
N'<body style="margin:0;padding:0;background-color:#FFFFFF;'
+ N'font-family:Arial,Helvetica,sans-serif;color:#2C3E50;">' +
N'<table width="100%" cellpadding="0" cellspacing="0" border="0"'
+ N' style="border-collapse:collapse;background-color:#FFFFFF;">' +
N'<tr>' +
N'<td align="center" style="padding:15px;">' +
N'<table width="100%" cellpadding="0" cellspacing="0" border="0"'
+ N' style="max-width:1200px;border-collapse:collapse;">' +
--------------------------------------------------------------------------
-- HEADER
--------------------------------------------------------------------------
N'<tr>' +
N'<td align="center"'
+ N' style="background-color:#0B3D91;color:#FFFFFF;padding:18px;">' +
N'<font face="Arial,Helvetica,sans-serif"'
+ N' style="font-size:24px;font-weight:bold;color:#FFFFFF;">'
+ N'SQL Server Health Check Report'
+ N'</font>' +
N'<br><br>' +
N'<font face="Arial,Helvetica,sans-serif"'
+ N' style="font-size:13px;color:#FFFFFF;">'
+ N'<b>Server:</b> '
+ ISNULL(@ServerName, N'N/A')
+ N' | '
+ N'<b>Generated:</b> '
+ CONVERT(NVARCHAR(20), @GetDate, 120)
+ N' | '
+ N'<b>SQL Version:</b> '
+ ISNULL(@SQLVersionFull, N'N/A')
+ N'</font>' +
N'</td>' +
N'</tr>' +
--------------------------------------------------------------------------
-- HEALTH SUMMARY
--------------------------------------------------------------------------
N'<tr>' +
N'<td style="padding-top:20px;">' +
N'<font face="Arial,Helvetica,sans-serif"'
+ N' style="font-size:17px;font-weight:bold;color:#0B3D91;">'
+ N'HEALTH SUMMARY DASHBOARD'
+ N'</font>' +
N'</td>' +
N'</tr>' +
N'<tr>' +
N'<td style="padding-top:10px;">' +
N'<table width="100%" cellpadding="0" cellspacing="4" border="0"'
+ N' style="border-collapse:separate;">' +
N'<tr>' +
--------------------------------------------------------------------------
-- CARD 1
--------------------------------------------------------------------------
N'<td width="20%" valign="top" align="center"'
+ N' style="background-color:' + @CardServerBg
+ N';border:1px solid #D9E2F3;padding:12px;">'
+ N'<font face="Arial,Helvetica,sans-serif"'
+ N' style="font-size:12px;font-weight:bold;color:#0B3D91;">'
+ N'SERVER STATUS'
+ N'</font>'
+ N'<br><br>'
+ N'<font face="Arial,Helvetica,sans-serif"'
+ N' style="font-size:18px;font-weight:bold;color:'
+ @CardServerColor + N';">'
+ CASE
WHEN @ServerIsHealthy = 1
THEN N'RUNNING'
ELSE N'RECENTLY RESTARTED'
END
+ N'</font>'
+ N'<br>'
+ N'<font face="Arial,Helvetica,sans-serif"'
+ N' style="font-size:12px;color:#666666;">'
+ N'Uptime: '
+ CONVERT(NVARCHAR(10), @UptimeHrs)
+ N' Hrs'
+ N'</font>'
+ N'<br><br>'
+ N'<b style="color:#FFFFFF;background-color:'
+ @CardServerColor
+ N';padding:4px 8px;font-size:11px;">'
+ @CardServerStatus
+ N'</b>'
+ N'</td>' +
--------------------------------------------------------------------------
-- CARD 2
--------------------------------------------------------------------------
N'<td width="20%" valign="top" align="center"'
+ N' style="background-color:' + @CardDbBg
+ N';border:1px solid #D9E2F3;padding:12px;">'
+ N'<font face="Arial,Helvetica,sans-serif"'
+ N' style="font-size:12px;font-weight:bold;color:#0B3D91;">'
+ N'DATABASES'
+ N'</font>'
+ N'<br><br>'
+ N'<font face="Arial,Helvetica,sans-serif"'
+ N' style="font-size:18px;font-weight:bold;color:'
+ @CardDbColor + N';">'
+ CONVERT(NVARCHAR(10), @DbOnlineCount)
+ N' / '
+ CONVERT(NVARCHAR(10), @DbTotalCount)
+ N'</font>'
+ N'<br>'
+ N'<font face="Arial,Helvetica,sans-serif"'
+ N' style="font-size:12px;color:#666666;">'
+ CONVERT(
NVARCHAR(10),
@DbTotalCount - @DbOnlineCount
)
+ N' Offline / Suspect'
+ N'</font>'
+ N'<br><br>'
+ N'<b style="color:#FFFFFF;background-color:'
+ @CardDbColor
+ N';padding:4px 8px;font-size:11px;">'
+ @CardDbStatus
+ N'</b>'
+ N'</td>' +
--------------------------------------------------------------------------
-- CARD 3
--------------------------------------------------------------------------
N'<td width="20%" valign="top" align="center"'
+ N' style="background-color:' + @CardBackupBg
+ N';border:1px solid #D9E2F3;padding:12px;">'
+ N'<font face="Arial,Helvetica,sans-serif"'
+ N' style="font-size:12px;font-weight:bold;color:#0B3D91;">'
+ N'BACKUPS'
+ N'</font>'
+ N'<br><br>'
+ N'<font face="Arial,Helvetica,sans-serif"'
+ N' style="font-size:18px;font-weight:bold;color:'
+ @CardBackupColor + N';">'
+ CASE
WHEN ISNULL(@BackupOverdueCount,0) = 0
THEN N'ALL SUCCESSFUL'
ELSE N'ATTENTION NEEDED'
END
+ N'</font>'
+ N'<br>'
+ N'<font face="Arial,Helvetica,sans-serif"'
+ N' style="font-size:12px;color:#666666;">'
+ CONVERT(
NVARCHAR(10),
ISNULL(@BackupOverdueCount,0)
)
+ N' Overdue'
+ N'</font>'
+ N'<br><br>'
+ N'<b style="color:#FFFFFF;background-color:'
+ @CardBackupColor
+ N';padding:4px 8px;font-size:11px;">'
+ @CardBackupStatus
+ N'</b>'
+ N'</td>' +
--------------------------------------------------------------------------
-- CARD 4
--------------------------------------------------------------------------
N'<td width="20%" valign="top" align="center"'
+ N' style="background-color:' + @CardDiskBg
+ N';border:1px solid #D9E2F3;padding:12px;">'
+ N'<font face="Arial,Helvetica,sans-serif"'
+ N' style="font-size:12px;font-weight:bold;color:#0B3D91;">'
+ N'DISK SPACE'
+ N'</font>'
+ N'<br><br>'
+ N'<font face="Arial,Helvetica,sans-serif"'
+ N' style="font-size:18px;font-weight:bold;color:'
+ @CardDiskColor + N';">'
+ ISNULL(
CONVERT(NVARCHAR(10), @DiskWorstPct),
N'N/A'
)
+ N'%'
+ N'</font>'
+ N'<br>'
+ N'<font face="Arial,Helvetica,sans-serif"'
+ N' style="font-size:12px;color:#666666;">'
+ N'Lowest Free Space'
+ N'</font>'
+ N'<br><br>'
+ N'<b style="color:#FFFFFF;background-color:'
+ @CardDiskColor
+ N';padding:4px 8px;font-size:11px;">'
+ @CardDiskStatus
+ N'</b>'
+ N'</td>' +
--------------------------------------------------------------------------
-- CARD 5
--------------------------------------------------------------------------
N'<td width="20%" valign="top" align="center"'
+ N' style="background-color:' + @CardJobBg
+ N';border:1px solid #D9E2F3;padding:12px;">'
+ N'<font face="Arial,Helvetica,sans-serif"'
+ N' style="font-size:12px;font-weight:bold;color:#0B3D91;">'
+ N'AGENT JOBS'
+ N'</font>'
+ N'<br><br>'
+ N'<font face="Arial,Helvetica,sans-serif"'
+ N' style="font-size:18px;font-weight:bold;color:'
+ @CardJobColor + N';">'
+ CASE
WHEN ISNULL(@JobFailedCount,0) = 0
THEN N'ALL SUCCESSFUL'
ELSE N'ATTENTION NEEDED'
END
+ N'</font>'
+ N'<br>'
+ N'<font face="Arial,Helvetica,sans-serif"'
+ N' style="font-size:12px;color:#666666;">'
+ CONVERT(
NVARCHAR(10),
ISNULL(@JobFailedCount,0)
)
+ N' Failed / Unknown'
+ N'</font>'
+ N'<br><br>'
+ N'<b style="color:#FFFFFF;background-color:'
+ @CardJobColor
+ N';padding:4px 8px;font-size:11px;">'
+ @CardJobStatus
+ N'</b>'
+ N'</td>' +
N'</tr>' +
N'</table>' +
N'</td>' +
N'</tr>' +
--------------------------------------------------------------------------
-- SERVER DETAILS
--------------------------------------------------------------------------
N'<tr>' +
N'<td style="padding-top:25px;">'
+ N'<font face="Arial,Helvetica,sans-serif"'
+ N' style="font-size:17px;font-weight:bold;color:#0B3D91;">'
+ N'1. SERVER DETAILS'
+ N'</font>'
+ N'</td>' +
N'</tr>' +
N'<tr>' +
N'<td style="padding-top:10px;">'
+ N'<table width="100%" cellpadding="0" cellspacing="0" border="0"'
+ N' style="border-collapse:collapse;font-family:Arial,Helvetica,sans-serif;font-size:12px;">'
+ N'<tr>'
+ N'<th style="background-color:#0B3D91;color:#FFFFFF;border:1px solid #0B3D91;padding:8px;text-align:left;">Hostname</th>'
+ N'<th style="background-color:#0B3D91;color:#FFFFFF;border:1px solid #0B3D91;padding:8px;text-align:center;">Is Cluster</th>'
+ N'<th style="background-color:#0B3D91;color:#FFFFFF;border:1px solid #0B3D91;padding:8px;text-align:left;">Current Node</th>'
+ N'<th style="background-color:#0B3D91;color:#FFFFFF;border:1px solid #0B3D91;padding:8px;text-align:left;">SQL Restart</th>'
+ N'<th style="background-color:#0B3D91;color:#FFFFFF;border:1px solid #0B3D91;padding:8px;text-align:center;">Uptime (Hrs)</th>'
+ N'<th style="background-color:#0B3D91;color:#FFFFFF;border:1px solid #0B3D91;padding:8px;text-align:center;">Status</th>'
+ N'</tr>'
+ @ServerDetailsRows
+ N'</table>'
+ N'</td>' +
N'</tr>' +
--------------------------------------------------------------------------
-- DATABASE DETAILS
--------------------------------------------------------------------------
N'<tr>' +
N'<td style="padding-top:25px;">'
+ N'<font face="Arial,Helvetica,sans-serif"'
+ N' style="font-size:17px;font-weight:bold;color:#0B3D91;">'
+ N'2. DATABASE DETAILS'
+ N'</font>'
+ N'</td>' +
N'</tr>' +
N'<tr>' +
N'<td style="padding-top:10px;">'
+ N'<table width="100%" cellpadding="0" cellspacing="0" border="0"'
+ N' style="border-collapse:collapse;font-family:Arial,Helvetica,sans-serif;font-size:12px;">'
+ N'<tr>'
+ N'<th style="background-color:#0B3D91;color:#FFFFFF;border:1px solid #0B3D91;padding:8px;text-align:left;">Database Name</th>'
+ N'<th style="background-color:#0B3D91;color:#FFFFFF;border:1px solid #0B3D91;padding:8px;text-align:left;">Created Date</th>'
+ N'<th style="background-color:#0B3D91;color:#FFFFFF;border:1px solid #0B3D91;padding:8px;text-align:left;">Recovery Model</th>'
+ N'<th style="background-color:#0B3D91;color:#FFFFFF;border:1px solid #0B3D91;padding:8px;text-align:right;">Database Size (GB)</th>'
+ N'<th style="background-color:#0B3D91;color:#FFFFFF;border:1px solid #0B3D91;padding:8px;text-align:left;">State</th>'
+ N'<th style="background-color:#0B3D91;color:#FFFFFF;border:1px solid #0B3D91;padding:8px;text-align:center;">Status</th>'
+ N'</tr>'
+ @DatabaseDetailsRows
+ N'</table>'
+ N'</td>' +
N'</tr>' +
--------------------------------------------------------------------------
-- BACKUP DETAILS
--------------------------------------------------------------------------
N'<tr>' +
N'<td style="padding-top:25px;">'
+ N'<font face="Arial,Helvetica,sans-serif"'
+ N' style="font-size:17px;font-weight:bold;color:#0B3D91;">'
+ N'3. DATABASE BACKUP DETAILS'
+ N'</font>'
+ N'</td>' +
N'</tr>' +
N'<tr>' +
N'<td style="padding-top:10px;">'
+ N'<table width="100%" cellpadding="0" cellspacing="0" border="0"'
+ N' style="border-collapse:collapse;font-family:Arial,Helvetica,sans-serif;font-size:11px;">'
+ N'<tr>'
+ N'<th style="background-color:#0B3D91;color:#FFFFFF;border:1px solid #0B3D91;padding:7px;">Database Name</th>'
+ N'<th style="background-color:#0B3D91;color:#FFFFFF;border:1px solid #0B3D91;padding:7px;">Recovery</th>'
+ N'<th style="background-color:#0B3D91;color:#FFFFFF;border:1px solid #0B3D91;padding:7px;">Full Backup</th>'
+ N'<th style="background-color:#0B3D91;color:#FFFFFF;border:1px solid #0B3D91;padding:7px;">Full Age</th>'
+ N'<th style="background-color:#0B3D91;color:#FFFFFF;border:1px solid #0B3D91;padding:7px;">Diff Backup</th>'
+ N'<th style="background-color:#0B3D91;color:#FFFFFF;border:1px solid #0B3D91;padding:7px;">Diff Age</th>'
+ N'<th style="background-color:#0B3D91;color:#FFFFFF;border:1px solid #0B3D91;padding:7px;">Log Backup</th>'
+ N'<th style="background-color:#0B3D91;color:#FFFFFF;border:1px solid #0B3D91;padding:7px;">Log Age</th>'
+ N'<th style="background-color:#0B3D91;color:#FFFFFF;border:1px solid #0B3D91;padding:7px;">Status</th>'
+ N'</tr>'
+ @BackupDetailsRows
+ N'</table>'
+ N'</td>' +
N'</tr>' +
--------------------------------------------------------------------------
-- DISK DETAILS
--------------------------------------------------------------------------
N'<tr>' +
N'<td style="padding-top:25px;">'
+ N'<font face="Arial,Helvetica,sans-serif"'
+ N' style="font-size:17px;font-weight:bold;color:#0B3D91;">'
+ N'4. DISK DETAILS'
+ N'</font>'
+ N'</td>' +
N'</tr>' +
N'<tr>' +
N'<td style="padding-top:10px;">'
+ N'<table width="100%" cellpadding="0" cellspacing="0" border="0"'
+ N' style="border-collapse:collapse;font-family:Arial,Helvetica,sans-serif;font-size:12px;">'
+ N'<tr>'
+ N'<th style="background-color:#0B3D91;color:#FFFFFF;border:1px solid #0B3D91;padding:8px;">Drive</th>'
+ N'<th style="background-color:#0B3D91;color:#FFFFFF;border:1px solid #0B3D91;padding:8px;">Volume Label</th>'
+ N'<th style="background-color:#0B3D91;color:#FFFFFF;border:1px solid #0B3D91;padding:8px;">Total (GB)</th>'
+ N'<th style="background-color:#0B3D91;color:#FFFFFF;border:1px solid #0B3D91;padding:8px;">Used (GB)</th>'
+ N'<th style="background-color:#0B3D91;color:#FFFFFF;border:1px solid #0B3D91;padding:8px;">Free (GB)</th>'
+ N'<th style="background-color:#0B3D91;color:#FFFFFF;border:1px solid #0B3D91;padding:8px;">Free %</th>'
+ N'<th style="background-color:#0B3D91;color:#FFFFFF;border:1px solid #0B3D91;padding:8px;">Status</th>'
+ N'</tr>'
+ @DiskDetailsRows
+ N'</table>'
+ N'</td>' +
N'</tr>' +
--------------------------------------------------------------------------
-- AGENT JOB DETAILS
--------------------------------------------------------------------------
N'<tr>' +
N'<td style="padding-top:25px;">'
+ N'<font face="Arial,Helvetica,sans-serif"'
+ N' style="font-size:17px;font-weight:bold;color:#0B3D91;">'
+ N'5. SQL AGENT JOBS DETAILS'
+ N'</font>'
+ N'</td>' +
N'</tr>' +
N'<tr>' +
N'<td style="padding-top:10px;">'
+ N'<table width="100%" cellpadding="0" cellspacing="0" border="0"'
+ N' style="border-collapse:collapse;font-family:Arial,Helvetica,sans-serif;font-size:12px;">'
+ N'<tr>'
+ N'<th style="background-color:#0B3D91;color:#FFFFFF;border:1px solid #0B3D91;padding:8px;">Job Name</th>'
+ N'<th style="background-color:#0B3D91;color:#FFFFFF;border:1px solid #0B3D91;padding:8px;">Enabled</th>'
+ N'<th style="background-color:#0B3D91;color:#FFFFFF;border:1px solid #0B3D91;padding:8px;">Current State</th>'
+ N'<th style="background-color:#0B3D91;color:#FFFFFF;border:1px solid #0B3D91;padding:8px;">Last Run Status</th>'
+ N'<th style="background-color:#0B3D91;color:#FFFFFF;border:1px solid #0B3D91;padding:8px;">Last Run Date/Time</th>'
+ N'<th style="background-color:#0B3D91;color:#FFFFFF;border:1px solid #0B3D91;padding:8px;">Status</th>'
+ N'</tr>'
+ @JobDetailsRows
+ N'</table>'
+ N'</td>' +
N'</tr>' +
--------------------------------------------------------------------------
-- DISCLAIMER
--------------------------------------------------------------------------
N'<tr>' +
N'<td style="padding-top:25px;">'
+ N'<font face="Arial,Helvetica,sans-serif"'
+ N' style="font-size:17px;font-weight:bold;color:#0B3D91;">'
+ N'6. DISCLAIMER'
+ N'</font>'
+ N'</td>' +
N'</tr>' +
N'<tr>' +
N'<td style="background-color:#FFF8E1;border:1px solid #E6D89A;'
+ N'padding:12px;text-align:center;color:#7A5C00;font-size:12px;">'
+ N'This is an automated mail. Please do not respond to this mail.'
+ N'</td>' +
N'</tr>' +
--------------------------------------------------------------------------
-- SIGNATURE
--------------------------------------------------------------------------
N'<tr>' +
N'<td style="padding-top:25px;">'
+ N'<font face="Arial,Helvetica,sans-serif"'
+ N' style="font-size:17px;font-weight:bold;color:#0B3D91;">'
+ N'7. SIGNATURE'
+ N'</font>'
+ N'</td>' +
N'</tr>' +
N'<tr>' +
N'<td style="background-color:#EAF3FB;border:1px solid #D9E2F3;'
+ N'padding:15px;font-family:Arial,Helvetica,sans-serif;font-size:13px;">'
+ N'Regards,<br>'
+ N'<b style="color:#0B3D91;">'
+ N'Database Administration Team'
+ N'</b>'
+ N'<br>'
+ N'Automated SQL Server Health Monitoring'
+ N'</td>' +
N'</tr>' +
N'</table>' +
N'</td>' +
N'</tr>' +
N'</table>' +
N'</body>' +
N'</html>';
--------------------------------------------------------------------------
-- 14. SEND EMAIL
--------------------------------------------------------------------------
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @ProfileName,
@recipients = @Recipients,
@subject = @Subject,
@body = @HTML,
@body_format = 'HTML';
--------------------------------------------------------------------------
-- 15. 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