/* =======================================================================
SQL Server Health Check – HTML Email (Final)
- DBA's Heart Beat
======================================================================= */
SET NOCOUNT ON;
DECLARE @html NVARCHAR(MAX) = N'';
DECLARE @newline NVARCHAR(10) = CHAR(13) + CHAR(10);
-- =========================
-- HTML Header + Styles
-- =========================
SET @html = @html + N'<html><head><style>
body { font-family: Segoe UI, Arial, sans-serif; font-size: 13px; color: #222; }
h3 { margin: 18px 0 8px; }
table { border-collapse: collapse; width: auto; max-width: 100%; }
th, td { border: 1px solid #ddd; padding: 6px 8px; text-align: left; }
th { background-color: #f2f2f2; }
tr.alert { color: #b00020; font-weight: 600; }
.small { color:#666; font-size:12px; }
</style></head><body>' + @newline;
-- =========================
-- SQL Server Version & EOL
-- =========================
DECLARE @Servername NVARCHAR(128) = CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128));
DECLARE @version NVARCHAR(128) = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128));
DECLARE @edition NVARCHAR(128) = CAST(SERVERPROPERTY('Edition') AS NVARCHAR(128));
DECLARE @eol NVARCHAR(256);
DECLARE @MajorVersion NVARCHAR(256);
SET @MajorVersion = CASE
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('productversion')) LIKE '8%' THEN 'SQL2000'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('productversion')) LIKE '9%' THEN 'SQL2005'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('productversion')) LIKE '10.0%' THEN 'SQL2008'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('productversion')) LIKE '10.5%' THEN 'SQL2008 R2'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('productversion')) LIKE '11%' THEN 'SQL2012'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('productversion')) LIKE '12%' THEN 'SQL2014'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('productversion')) LIKE '13%' THEN 'SQL2016'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('productversion')) LIKE '14%' THEN 'SQL2017'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('productversion')) LIKE '15%' THEN 'SQL2019'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('productversion')) LIKE '16%' THEN 'SQL2022'
ELSE 'Unknown/New Version'
END;
SET @eol = CASE
WHEN @@VERSION LIKE '%2005%' THEN 'Mainstream: 2011-04-12, Extended: 2016-04-12'
WHEN @@VERSION LIKE '%2008 R2%' THEN 'Mainstream: 2014-07-08, Extended: 2019-07-09'
WHEN @@VERSION LIKE '%2008%' THEN 'Mainstream: 2014-07-08, Extended: 2019-07-09'
WHEN @@VERSION LIKE '%2012%' THEN 'Mainstream: 2017-07-11, Extended: 2022-07-12'
WHEN @@VERSION LIKE '%2014%' THEN 'Mainstream: 2019-07-09, Extended: 2024-07-09'
WHEN @@VERSION LIKE '%2016%' THEN 'Mainstream: 2021-07-13, Extended: 2026-07-14'
WHEN @@VERSION LIKE '%2017%' THEN 'Mainstream: 2022-10-11, Extended: 2027-10-12'
WHEN @@VERSION LIKE '%2019%' THEN 'Mainstream: 2025-02-28, Extended: 2030-01-08'
WHEN @@VERSION LIKE '%2022%' THEN 'Mainstream: 2028-01-11, Extended: 2033-01-11'
ELSE 'Unknown/New Version'
END;
SET @html = @html + N'<h3>SQL Server Version and EOL</h3><table><tr>
<th>Instance Name</th><th>Version</th><th>PatchLevel</th><th>Edition</th><th>EOL Info</th></tr>';
SET @html = @html + N'<tr><td>' + @Servername + N'</td><td>' + @MajorVersion + N'</td><td>' +
@version + N'</td><td>' + @edition + N'</td><td>' + @eol + N'</td></tr></table>' + @newline;
-- =========================
-- Uptime (Start + Duration)
-- =========================
SET @html = @html + N'<h3>SQL Server Uptime</h3><table><tr><th>Start Time</th><th>Up Since</th></tr>';
DECLARE @start DATETIME = (SELECT sqlserver_start_time FROM sys.dm_os_sys_info);
DECLARE @now DATETIME = GETDATE();
DECLARE @diffSeconds BIGINT = DATEDIFF(SECOND, @start, @now);
DECLARE @days INT = @diffSeconds / 86400;
DECLARE @hours INT = (@diffSeconds % 86400) / 3600;
DECLARE @mins INT = (@diffSeconds % 3600) / 60;
DECLARE @secs INT = @diffSeconds % 60;
SELECT @html = @html +
N'<tr><td>' + CONVERT(NVARCHAR(19), @start, 120) + N'</td><td>' +
CAST(@days AS NVARCHAR(10)) + N' days ' +
CAST(@hours AS NVARCHAR(10)) + N' hrs ' +
CAST(@mins AS NVARCHAR(10)) + N' mins ' +
CAST(@secs AS NVARCHAR(10)) + N' secs</td></tr>';
SET @html = @html + N'</table>' + @newline;
-- =========================
-- Offline Databases
-- =========================
SET @html = @html + N'<h3>Offline Databases</h3>';
IF EXISTS (SELECT 1 FROM sys.databases WHERE state_desc = 'OFFLINE')
BEGIN
SET @html = @html + N'<table><tr><th>Database</th><th>Status</th></tr>';
SELECT @html = @html + N'<tr><td>' + name + N'</td><td>' + state_desc + N'</td></tr>'
FROM sys.databases WHERE state_desc = 'OFFLINE';
SET @html = @html + N'</table>' + @newline;
END
ELSE
BEGIN
SET @html = @html + N'<p>All databases are online.</p>' + @newline;
END
/* =======================================================================
Database Default Locations + All Disks (Fixed Drive Letters only)
WMI via OLE Automation – decimal math for Free%
======================================================================= */
-----------------------------
-- Database Default Locations
-----------------------------
DECLARE @DefaultData NVARCHAR(4000) = CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS NVARCHAR(4000));
DECLARE @DefaultLog NVARCHAR(4000) = CAST(SERVERPROPERTY('InstanceDefaultLogPath') AS NVARCHAR(4000));
DECLARE @DefaultBackup NVARCHAR(4000);
BEGIN TRY
SET @DefaultBackup = CAST(SERVERPROPERTY('InstanceDefaultBackupPath') AS NVARCHAR(4000));
END TRY
BEGIN CATCH
SET @DefaultBackup = NULL;
END CATCH;
IF @DefaultBackup IS NULL OR LTRIM(RTRIM(@DefaultBackup)) = ''
BEGIN
DECLARE @bk NVARCHAR(4000);
EXEC master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'BackupDirectory',
@bk OUTPUT,
'no_output';
SET @DefaultBackup = @bk;
END
SET @html = @html + N'<h3>Database Default Locations</h3>'
+ N'<table><tr><th>Setting</th><th>Path</th></tr>'
+ N'<tr><td>Default Data</td><td>' + ISNULL(@DefaultData,'') + N'</td></tr>'
+ N'<tr><td>Default Log</td><td>' + ISNULL(@DefaultLog,'') + N'</td></tr>'
+ N'<tr><td>Default Backup</td><td>' + ISNULL(@DefaultBackup,'') + N'</td></tr>'
+ N'</table>' + @newline;
---------------------------------------------
-- All Disks: Fixed Drive Letters only (no \\?\Volume)
---------------------------------------------
IF OBJECT_ID('tempdb..#AllDisks') IS NOT NULL DROP TABLE #AllDisks;
CREATE TABLE #AllDisks
(
DriveLetter NVARCHAR(10) NOT NULL, -- 'C:'
TotalBytes BIGINT NOT NULL,
FreeBytes BIGINT NOT NULL
);
DECLARE @hr INT = 0, @locator INT = NULL, @svc INT = NULL, @set INT = NULL, @item INT = NULL, @count INT = 0, @i INT = 0;
BEGIN TRY
-- Connect to WMI
EXEC @hr = sp_OACreate 'WbemScripting.SWbemLocator', @locator OUT;
IF @hr <> 0 BEGIN RAISERROR('Failed to create WMI locator (sp_OACreate).', 16, 1); RETURN; END;
EXEC @hr = sp_OAMethod @locator, 'ConnectServer', @svc OUT, '.', 'root\cimv2';
IF @hr <> 0 BEGIN RAISERROR('Failed to connect to WMI root\cimv2.', 16, 1); RETURN; END;
-- Query: Fixed disks with letters (DriveType=3)
EXEC @hr = sp_OAMethod @svc, 'ExecQuery', @set OUT,
'SELECT DeviceID, Size, FreeSpace FROM Win32_LogicalDisk WHERE DriveType=3';
IF @hr <> 0 BEGIN RAISERROR('WMI ExecQuery failed for Win32_LogicalDisk.', 16, 1); RETURN; END;
EXEC @hr = sp_OAGetProperty @set, 'Count', @count OUT;
SET @i = 0;
WHILE @i < ISNULL(@count, 0)
BEGIN
EXEC @hr = sp_OAMethod @set, 'ItemIndex', @item OUT, @i;
IF @hr <> 0 BREAK;
DECLARE @DeviceID NVARCHAR(50), @Size NUMERIC(38,0), @Free NUMERIC(38,0);
EXEC @hr = sp_OAGetProperty @item, 'DeviceID', @DeviceID OUT;
EXEC @hr = sp_OAGetProperty @item, 'Size', @Size OUT;
EXEC @hr = sp_OAGetProperty @item, 'FreeSpace', @Free OUT;
-- Insert only if Size is present (>0)
IF @Size IS NOT NULL AND @Size > 0
BEGIN
INSERT INTO #AllDisks(DriveLetter, TotalBytes, FreeBytes)
VALUES (UPPER(@DeviceID), CONVERT(BIGINT, @Size), CONVERT(BIGINT, @Free));
END
SET @i = @i + 1;
END
END TRY
BEGIN CATCH
DECLARE @err NVARCHAR(4000) = ERROR_MESSAGE();
RAISERROR('WMI/OLE disk collection failed: %s', 16, 1, @err);
END CATCH
-- Cleanup WMI objects
IF @set IS NOT NULL BEGIN EXEC sp_OADestroy @set; END;
IF @svc IS NOT NULL BEGIN EXEC sp_OADestroy @svc; END;
IF @locator IS NOT NULL BEGIN EXEC sp_OADestroy @locator; END;
-- Render All Disks (decimal math for Free%)
SET @html = @html + N'<h3>All Disks</h3>'
+ N'<table><tr>'
+ N'<th>Drive</th><th>Total (GB)</th><th>Free (GB)</th>'
+ N'<th>Used (GB)</th><th>Free (%)</th></tr>';
;WITH calc AS
(
SELECT
Drive = DriveLetter,
totalGB = CONVERT(DECIMAL(18,2), TotalBytes / 1024.0 / 1024 / 1024),
freeGB = CONVERT(DECIMAL(18,2), FreeBytes / 1024.0 / 1024 / 1024),
usedGB = CONVERT(DECIMAL(18,2), (TotalBytes - FreeBytes) / 1024.0 / 1024 / 1024)
FROM #AllDisks
),
calc2 AS
(
SELECT
Drive, totalGB, freeGB, usedGB,
freePct = CONVERT(DECIMAL(5,2), CASE WHEN totalGB > 0 THEN (freeGB * 100.00) / totalGB ELSE 0 END)
FROM calc
)
SELECT @html = @html +
CASE WHEN freePct < 10 THEN N'<tr class="alert">' ELSE N'<tr>' END +
N'<td>' + Drive + N'</td><td>' +
CONVERT(NVARCHAR(50), totalGB) + N'</td><td>' +
CONVERT(NVARCHAR(50), freeGB) + N'</td><td>' +
CONVERT(NVARCHAR(50), usedGB) + N'</td><td>' +
CONVERT(NVARCHAR(50), freePct) + N'</td></tr>'
FROM calc2
ORDER BY Drive;
SET @html = @html + N'</table>' + @newline;
DROP TABLE #AllDisks;
-- =========================
-- Backup Status (exclude tempdb & mirrored DBs)
-- =========================
SET @html = @html + N'<h3>Backup Status (Failed or No Backup in Past 2 Days)</h3>';
IF EXISTS (
SELECT 1
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset bs ON bs.database_name = d.name
LEFT JOIN sys.database_mirroring dm ON d.database_id = dm.database_id
WHERE d.name <> 'tempdb'
AND d.is_read_only = 0
AND dm.mirroring_guid IS NULL
GROUP BY d.name
HAVING MAX(bs.backup_finish_date) IS NULL
OR MAX(bs.backup_finish_date) < DATEADD(DAY, -2, GETDATE())
)
BEGIN
SET @html = @html + N'<table><tr><th>Database</th><th>Last Backup</th></tr>';
SELECT @html = @html + N'<tr><td>' + d.name + N'</td><td>' +
COALESCE(CONVERT(NVARCHAR(19), MAX(bs.backup_finish_date), 120), N'No Backup') + N'</td></tr>'
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset bs ON bs.database_name = d.name
LEFT JOIN sys.database_mirroring dm ON d.database_id = dm.database_id
WHERE d.name <> 'tempdb'
AND d.is_read_only = 0
AND dm.mirroring_guid IS NULL
GROUP BY d.name
HAVING MAX(bs.backup_finish_date) IS NULL
OR MAX(bs.backup_finish_date) < DATEADD(DAY, -2, GETDATE());
SET @html = @html + N'</table>' + @newline;
END
ELSE
BEGIN
SET @html = @html + N'<p>All backups successful (within last 2 days).</p>' + @newline;
END
-- =========================
-- Mirroring Status
-- =========================
SET @html = @html + N'<h3>Mirroring Status</h3>';
IF EXISTS (SELECT 1 FROM sys.database_mirroring WHERE mirroring_guid IS NOT NULL)
BEGIN
SET @html = @html + N'<table><tr><th>Database</th><th>Mirroring State</th><th>Safety Level</th></tr>';
SELECT @html = @html + N'<tr><td>' + d.name + N'</td><td>' +
ISNULL(dm.mirroring_state_desc, N'Unknown') + N'</td><td>' +
ISNULL(dm.mirroring_safety_level_desc, N'Unknown') + N'</td></tr>'
FROM sys.databases d
JOIN sys.database_mirroring dm ON d.database_id = dm.database_id
WHERE dm.mirroring_guid IS NOT NULL;
SET @html = @html + N'</table>' + @newline;
END
ELSE
BEGIN
SET @html = @html + N'<p>No mirroring configured.</p>' + @newline;
END
-- =========================
-- Replication Status (GUID-safe + resilient + idempotent)
-- =========================
SET @html = @html + N'<h3>Replication Status</h3>';
IF DB_ID('distribution') IS NOT NULL
BEGIN
IF OBJECT_ID('distribution.dbo.MSreplication_monitordata') IS NOT NULL
BEGIN
-- Clean up any leftovers from previous runs in this session
IF OBJECT_ID('tempdb..#DA') IS NOT NULL DROP TABLE #DA;
IF OBJECT_ID('tempdb..#MM') IS NOT NULL DROP TABLE #MM;
IF OBJECT_ID('tempdb..#JobLast') IS NOT NULL DROP TABLE #JobLast;
IF OBJECT_ID('tempdb..#MonitorRepl') IS NOT NULL DROP TABLE #MonitorRepl;
/* 1) Distribution Agents with publication + subscriber */
SELECT
p.publisher_db,
a.publication,
a.subscriber_db,
a.job_id,
job_id_str = UPPER(CONVERT(NVARCHAR(36), a.job_id))
INTO #DA
FROM distribution.dbo.MSdistribution_agents AS a
INNER JOIN distribution.dbo.MSpublications AS p
ON p.publisher_id = a.publisher_id
AND p.publication = a.publication;
/* 2) Monitor data: normalize job_id string (trim, uppercase, remove braces) */
SELECT
publisher_db = m.publisher_db,
publication = m.publication,
job_id_str = NULLIF(UPPER(REPLACE(REPLACE(LTRIM(RTRIM(m.job_id)), '{', ''), '}', '')), ''),
[Status] = m.[Status],
[Warning] = m.[Warning],
cur_latency = m.cur_latency
INTO #MM
FROM distribution.dbo.MSreplication_monitordata AS m
WHERE m.agent_type = 3 -- distribution agents
AND NULLIF(LTRIM(RTRIM(m.job_id)), '') IS NOT NULL;
/* 3) Last job outcome for each agent (fallback when MM has no rows) */
SELECT
j.job_id,
job_id_str = UPPER(CONVERT(NVARCHAR(36), j.job_id)),
last_status = ISNULL(h_last.run_status, -1) -- -1 = unknown
INTO #JobLast
FROM msdb.dbo.sysjobs AS j
INNER JOIN #DA AS da ON da.job_id = j.job_id
OUTER APPLY (
SELECT TOP (1) run_status
FROM msdb.dbo.sysjobhistory
WHERE job_id = j.job_id AND step_id = 0
ORDER BY run_date DESC, run_time DESC
) AS h_last;
/* 4) Final projection: prefer MM status; else fallback to job history; else Unknown */
SELECT DISTINCT
da.publisher_db,
da.publication,
da.subscriber_db,
Status =
COALESCE(
/* MM status mapping */
CASE mm.[Status]
WHEN 1 THEN 'Started'
WHEN 2 THEN 'Succeeded'
WHEN 3 THEN 'In Progress'
WHEN 4 THEN 'Idle'
WHEN 5 THEN 'Retrying'
WHEN 6 THEN 'Failed'
ELSE NULL
END,
/* Job history mapping (fallback) */
CASE jl.last_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retrying'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'In Progress'
ELSE 'Unknown'
END,
'Unknown'
)
INTO #MonitorRepl
FROM #DA AS da
LEFT JOIN #MM AS mm
ON (mm.job_id_str = da.job_id_str)
OR (mm.publisher_db = da.publisher_db AND mm.publication = da.publication)
LEFT JOIN #JobLast AS jl
ON jl.job_id = da.job_id;
/* 5) Render HTML */
SET @html = @html + N'<table><tr>'
+ N'<th>Publisher DB</th><th>Publication</th><th>Subscriber DB</th><th>Status</th>'
+ N'</tr>';
IF EXISTS (SELECT 1 FROM #MonitorRepl)
BEGIN
SELECT @html = @html + N'<tr><td>' + publisher_db + N'</td><td>' + publication
+ N'</td><td>' + subscriber_db + N'</td><td>' + Status + N'</td></tr>'
FROM #MonitorRepl
ORDER BY publisher_db, publication, subscriber_db;
END
ELSE
BEGIN
SET @html = @html + N'<tr><td colspan="4">No replication agent rows found.</td></tr>';
END
SET @html = @html + N'</table>' + @newline;
/* 6) Cleanup */
IF OBJECT_ID('tempdb..#MonitorRepl') IS NOT NULL DROP TABLE #MonitorRepl;
IF OBJECT_ID('tempdb..#JobLast') IS NOT NULL DROP TABLE #JobLast;
IF OBJECT_ID('tempdb..#MM') IS NOT NULL DROP TABLE #MM;
IF OBJECT_ID('tempdb..#DA') IS NOT NULL DROP TABLE #DA;
END
ELSE
BEGIN
SET @html = @html + N'<p>No replication activity object (MSreplication_monitordata) found in distribution database.</p>' + @newline;
END
END
ELSE
BEGIN
SET @html = @html + N'<p>No replication configured.</p>' + @newline;
END
-- =========================
-- Always On Availability Groups
-- =========================
SET @html = @html + N'<h3>Always On Availability Groups</h3>';
IF EXISTS (SELECT 1 FROM sys.availability_groups)
BEGIN
SET @html = @html + N'<table><tr>
<th>Availability Group</th>
<th>Replica Server</th>
<th>Role</th>
<th>Database</th>
<th>Sync State</th>
<th>Health</th>
</tr>';
;WITH ags AS (
SELECT DISTINCT
ag.name AS AGName,
ar.replica_server_name AS ReplicaServer,
ars.role_desc AS RoleDesc,
adc.database_name AS DBName,
dhs.synchronization_state_desc AS SyncState,
dhs.synchronization_health_desc AS Health
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar
ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states ars
ON ar.replica_id = ars.replica_id
JOIN sys.availability_databases_cluster adc
ON ag.group_id = adc.group_id
JOIN sys.dm_hadr_database_replica_states dhs
ON ars.replica_id = dhs.replica_id
AND dhs.group_database_id = adc.group_database_id
)
SELECT @html = @html + N'<tr><td>' + AGName + N'</td><td>' + ReplicaServer + N'</td><td>' +
RoleDesc + N'</td><td>' + DBName + N'</td><td>' + SyncState + N'</td><td>' + Health + N'</td></tr>'
FROM ags
ORDER BY AGName, ReplicaServer, DBName;
SET @html = @html + N'</table>' + @newline;
END
ELSE
BEGIN
SET @html = @html + N'<p>No Always On Availability Groups configured.</p>' + @newline;
END
-- =========================
-- HTML Footer
-- =========================
SET @html = @html + N'</body></html>';
-- =========================
-- Send Email
-- =========================
DECLARE @sub VARCHAR(MAX);
SET @sub = 'Alert: SQL Server Health Check Report - ' + CAST(SERVERPROPERTY('MachineName') AS VARCHAR(128));
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Default',
@recipients = 'DBA@sqlserver.com',
@subject = @sub,
@body = @html,
@body_format = 'HTML';
