DECLARE @html NVARCHAR(MAX) = '';
DECLARE @newline NVARCHAR(10) = CHAR(13) + CHAR(10);
-- HTML Header
SET @html = @html + '<html><head><style>
table { border-collapse: collapse; width: auto; max-width: 100%; }
th, td { border: 1px solid #ddd; padding: 8px; }
th { background-color: #f2f2f2; }
</style></head><body>' + @newline;
-- SQL Server Version and EOL Mapping
DECLARE @Servername NVARCHAR(128) = CAST(SERVERPROPERTY('ServerName') AS NVARCHAR);
DECLARE @version NVARCHAR(128) = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR);
DECLARE @edition NVARCHAR(128) = CAST(SERVERPROPERTY('Edition') AS NVARCHAR);
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 Support: 2016-04-12'
WHEN @@VERSION LIKE '%2008 R2%' THEN 'Mainstream: 2014-07-08, Extended Support: 2019-07-09'
WHEN @@VERSION LIKE '%2008%' THEN 'Mainstream: 2014-07-08, Extended Support: 2019-07-09'
WHEN @@VERSION LIKE '%2012%' THEN 'Mainstream: 2017-07-11, Extended Support: 2022-07-12'
WHEN @@VERSION LIKE '%2014%' THEN 'Mainstream: 2019-07-09, Extended Support: 2024-07-09'
WHEN @@VERSION LIKE '%2016%' THEN 'Mainstream: 2021-07-13, Extended Support: 2026-07-14'
WHEN @@VERSION LIKE '%2017%' THEN 'Mainstream: 2022-10-11, Extended Support: 2027-10-12'
WHEN @@VERSION LIKE '%2019%' THEN 'Mainstream: 2025-02-28, Extended Support: 2030-01-08'
WHEN @@VERSION LIKE '%2022%' THEN 'Mainstream: 2028-01-11, Extended Support: 2033-01-11'
ELSE 'Unknown/New Version'
END;
SET @html = @html + '<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 + '<tr><td>' + @Servername + '</td><td>' + @MajorVersion + '</td><td>' + @version + '</td><td>' + @edition + '</td><td>' + @eol + '</td></tr></table>' + @newline;
-- Uptime
SET @html = @html + '<h3>SQL Server Uptime</h3><table><tr><th>Start Time</th></tr>';
SELECT @html = @html + '<tr><td>' + CONVERT(NVARCHAR, sqlserver_start_time, 120) + '</td></tr>'
FROM sys.dm_os_sys_info;
SET @html = @html + '</table>' + @newline;
-- Offline Databases
SET @html = @html + '<h3>Offline Databases</h3>';
IF EXISTS (SELECT 1 FROM sys.databases WHERE state_desc = 'OFFLINE')
BEGIN
SET @html = @html + '<table><tr><th>Database</th><th>Status</th></tr>';
SELECT @html = @html + '<tr><td>' + name + '</td><td>' + state_desc + '</td></tr>'
FROM sys.databases
WHERE state_desc = 'OFFLINE';
SET @html = @html + '</table>' + @newline;
END
ELSE
BEGIN
SET @html = @html + '<p>All databases are online.</p>' + @newline;
END
-- Disk Space Usage in GB (2 decimal places)
SET @html = @html + '<h3>Disk Space Usage</h3><table><tr><th>Drive</th><th>Free Space (GB)</th></tr>';
CREATE TABLE #drives (drive CHAR(1), free_space_MB INT);
INSERT INTO #drives EXEC xp_fixeddrives;
SELECT @html = @html + '<tr><td>' + drive + '</td><td>' +
CONVERT(NVARCHAR, ROUND(CAST(free_space_MB AS FLOAT) / 1024, 2)) + '</td></tr>'
FROM #drives;
DROP TABLE #drives;
SET @html = @html + '</table>' + @newline;
-- Backup Status (Exclude tempdb and mirrored databases)
SET @html = @html + '<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 + '<table><tr><th>Database</th><th>Last Backup</th></tr>';
SELECT @html = @html + '<tr><td>' + d.name + '</td><td>' +
COALESCE(CONVERT(NVARCHAR, MAX(bs.backup_finish_date), 120), 'No Backup') + '</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 ISNULL(dm.mirroring_guid, '') = ''
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 + '</table>' + @newline;
END
ELSE
BEGIN
SET @html = @html + '<p>All backups successful.</p>' + @newline;
END
-- Mirroring Status
SET @html = @html + '<h3>Mirroring Status</h3>';
IF EXISTS (SELECT 1 FROM sys.database_mirroring WHERE mirroring_guid IS NOT NULL)
BEGIN
SET @html = @html + '<table><tr><th>Database</th><th>Mirroring State</th><th>Sync State</th></tr>';
SELECT @html = @html + '<tr><td>' + d.name + '</td><td>' +
ISNULL(dm.mirroring_state_desc, 'Unknown') + '</td><td>' +
ISNULL(dm.mirroring_safety_level_desc, 'Unknown') + '</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 + '</table>' + @newline;
END
ELSE
BEGIN
SET @html = @html + '<p>No mirroring configured.</p>' + @newline;
END
-- Replication Status
SET @html = @html + '<h3>Replication Status</h3>';
IF EXISTS (SELECT 1 FROM sys.databases WHERE name = 'distribution')
BEGIN
IF EXISTS (
SELECT 1
FROM distribution.sys.objects
WHERE name = 'MSreplication_monitordata' AND type = 'U'
)
BEGIN
CREATE TABLE #COUNTERS
(
[DATABASE] SYSNAME,
REPLICATEDTRANS INT,
REPRATESEC FLOAT,
REPLATENCY FLOAT,
LSN1 BINARY(10),
LSN2 BINARY(10)
)
INSERT INTO #COUNTERS EXEC sp_replcounters
SELECT DISTINCT m.Publisher_db, m.Publication, ma.Subscriber_db,
CASE [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'
END AS [Status],
CASE [Warning]
WHEN 0 THEN NULL
WHEN 1 THEN 'Expiration'
WHEN 2 THEN 'Latency'
ELSE 'MergeWarning'
END AS [Warning],
C.REPLICATEDTRANS AS 'Awaiting Transactions To Dist',
CONVERT(INT, c.REPRATESEC) AS 'Avrage Trans/Sec to Dist',
CONVERT(DECIMAL(10, 2), c.REPLATENCY) AS 'Avg. Latency to Dist/Sec',
CONVERT(DECIMAL(10, 2), mm.cur_latency) AS 'Avg. Latency to Subscriber'
INTO #MonitorRepl
FROM distribution.dbo.MSpublications m
INNER JOIN distribution.dbo.MSreplication_monitordata mm ON mm.publisher_db = m.publisher_db AND mm.publication_id = m.publication_id AND mm.agent_type = 3
INNER JOIN distribution.dbo.MSdistribution_agents ma ON ma.publisher_id = m.publisher_id AND ma.publication = mm.publication AND ma.job_id = mm.job_id
--INNER JOIN sys.servers s ON ma.subscriber_id = s.server_id
INNER JOIN #COUNTERS C ON m.publisher_db = C.[DATABASE]
ORDER BY m.publisher_db, m.publication, ma.subscriber_db
DROP TABLE #COUNTERS
SET @html = @html + '<table><tr><th>publisher_db</th><th>Publication</th><th>subscriber_db (sec)</th><th>Status</th></tr>';
SELECT @html = @html + '<tr><td>' + publisher_db + '</td><td>' + publication + '</td><td>' +
subscriber_db + '</td><td>' + Status + '</td></tr>'
FROM #MonitorRepl;
SET @html = @html + '</table>' + @newline;
DROP TABLE #MonitorRepl
END
ELSE
BEGIN
SET @html = @html + '<p>No replication activity found in distribution database.</p>' + @newline;
END
END
ELSE
BEGIN
SET @html = @html + '<p>No replication configured.</p>' + @newline;
END
-- Always On Availability Groups
SET @html = @html + '<h3>Always On Availability Groups</h3>';
IF EXISTS (SELECT 1 FROM sys.availability_groups)
BEGIN
SET @html = @html + '<table><tr>
<th>Availability Group</th>
<th>Replica Server</th>
<th>Role</th>
<th>Database</th>
<th>Sync State</th>
<th>Health</th>
</tr>';
SELECT @html = @html + '<tr><td>' + ag.name + '</td><td>' + ar.replica_server_name + '</td><td>' +
ars.role_desc + '</td><td>' + adc.database_name + '</td><td>' +
dhs.synchronization_state_desc + '</td><td>' + dhs.synchronization_health_desc + '</td></tr>'
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.dm_hadr_database_replica_states dhs ON ars.replica_id = dhs.replica_id
JOIN sys.availability_databases_cluster adc ON ag.group_id = adc.group_id
WHERE dhs.group_database_id = adc.group_database_id;
SET @html = @html + '</table>' + @newline;
END
ELSE
BEGIN
SET @html = @html + '<p>No Always On Availability Groups configured.</p>' + @newline;
END
-- HTML Footer
SET @html = @html + '</body></html>';
-- Send Email
declare @sub varchar(max)
SET @sub = 'Alert: SQL Server Health Check Report - ' + CAST(SERVERPROPERTY('MachineName') AS VARCHAR) + ''
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Default',
@recipients = 'DBA@mssqlserver.com',
@subject = @sub,
@body = @html,
@body_format = 'HTML';
No comments:
Post a Comment