Latest Post

July 25, 2026
Here it is 

/*

USE master;
GO
IF OBJECT_ID('dbo.sp_Send_SQLHealthCheck_Report', 'P') IS NOT NULL
DROP PROCEDURE dbo.sp_Send_SQLHealthCheck_Report;
GO
CREATE PROCEDURE dbo.sp_Send_SQLHealthCheck_Report
@MailProfile NVARCHAR(128) = 'your_mail_profile', -- Replace with your DB Mail Profile
@Recipients  NVARCHAR(MAX) = 'dba-team@company.com' -- Replace with target email address
AS
BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

--------------------------------------------------------------------------------
-- 1. SERVER & DATABASE METADATA
--------------------------------------------------------------------------------
DECLARE @GetDate DATETIME = GETDATE();
DECLARE @Subject NVARCHAR(256) = 'SQL Server Health Check Report - ' + @@SERVERNAME;
DECLARE @Hostname NVARCHAR(128) = CAST(SERVERPROPERTY('MachineName') AS NVARCHAR(128));
DECLARE @InstanceName NVARCHAR(128) = @@SERVERNAME;
DECLARE @Edition NVARCHAR(128) = CAST(SERVERPROPERTY('Edition') AS NVARCHAR(128));
DECLARE @BuildNumber NVARCHAR(128) = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128));
DECLARE @IsCluster INT = CAST(SERVERPROPERTY('IsClustered') AS INT);
DECLARE @CurrentNode NVARCHAR(128) = ISNULL(CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS NVARCHAR(128)), 'N/A');
DECLARE @SQLRestart DATETIME;
DECLARE @UptimeHours INT;

SELECT @SQLRestart = sqlserver_start_time FROM sys.dm_os_sys_info;
SET @UptimeHours = DATEDIFF(HOUR, @SQLRestart, @GetDate);

--------------------------------------------------------------------------------
-- 2. TEMP TABLES FOR DATA COLLECTION
--------------------------------------------------------------------------------
IF OBJECT_ID('tempdb..#DatabaseInfo') IS NOT NULL DROP TABLE #DatabaseInfo;
IF OBJECT_ID('tempdb..#BackupInfo') IS NOT NULL DROP TABLE #BackupInfo;
IF OBJECT_ID('tempdb..#DiskInfo') IS NOT NULL DROP TABLE #DiskInfo;

-- Database State & Size
SELECT 
    d.name,
    d.recovery_model_desc,
    d.compatibility_level,
    CAST(SUM(mf.size * 8.0 / 1024 / 1024) AS DECIMAL(10,2)) AS SizeGB,
    d.state_desc,
    d.database_id
INTO #DatabaseInfo
FROM sys.databases d
LEFT JOIN sys.master_files mf ON d.database_id = mf.database_id
WHERE d.name NOT IN ('tempdb')
GROUP BY d.name, d.recovery_model_desc, d.compatibility_level, d.state_desc, d.database_id;

-- Backup Information
SELECT 
    d.name AS DBName,
    d.recovery_model_desc,
    MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS LastFull,
    DATEDIFF(DAY, MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END), GETDATE()) AS FullAgeDays,
    MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END) AS LastDiff,
    DATEDIFF(HOUR, MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END), GETDATE()) AS DiffAgeHours,
    MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS LastLog,
    DATEDIFF(MINUTE, MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END), GETDATE()) AS LogAgeMinutes
INTO #BackupInfo
FROM #DatabaseInfo d
LEFT JOIN msdb.dbo.backupset b ON d.name = b.database_name
GROUP BY d.name, d.recovery_model_desc;

-- Disk Space
SELECT DISTINCT
    volume_mount_point AS VolumeName,
    CAST(total_bytes / 1073741824.0 AS DECIMAL(10,2)) AS TotalGB,
    CAST((total_bytes - available_bytes) / 1073741824.0 AS DECIMAL(10,2)) AS UsedGB,
    CAST(available_bytes / 1073741824.0 AS DECIMAL(10,2)) AS FreeGB,
    CAST((available_bytes * 100.0 / total_bytes) AS DECIMAL(5,2)) AS FreePct
INTO #DiskInfo
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id);

--------------------------------------------------------------------------------
-- 3. EXECUTIVE SUMMARY KPIs
--------------------------------------------------------------------------------
DECLARE @TotalDBs INT = (SELECT COUNT(*) FROM #DatabaseInfo);
DECLARE @OnlineDBs INT = (SELECT COUNT(*) FROM #DatabaseInfo WHERE state_desc = 'ONLINE');
DECLARE @OfflineCount INT = @TotalDBs - @OnlineDBs;
DECLARE @BackupAlertCount INT = 0;

SELECT @BackupAlertCount = COUNT(*) 
FROM #BackupInfo 
WHERE FullAgeDays > 7 OR FullAgeDays IS NULL 
   OR DiffAgeHours > 24 
   OR (recovery_model_desc <> 'SIMPLE' AND (LogAgeMinutes > 60 OR LogAgeMinutes IS NULL));

DECLARE @LowestDiskDrive NVARCHAR(128) = '';
DECLARE @LowestDiskPct DECIMAL(5,2) = 100.0;

SELECT TOP 1 @LowestDiskDrive = VolumeName, @LowestDiskPct = FreePct 
FROM #DiskInfo ORDER BY FreePct ASC;

--------------------------------------------------------------------------------
-- 4. HTML CSS STYLES & BUILD STRINGS
--------------------------------------------------------------------------------
-- Note: Removed Flexbox and external Font Awesome link for Outlook compatibility
DECLARE @CSS NVARCHAR(MAX) = N'
<style>
    body { font-family: "Segoe UI", Arial, sans-serif; background-color: #f8fafc; margin: 0; padding: 20px; }
    .container { max-width: 1150px; margin: auto; background-color: #ffffff; border-radius: 12px; border: 1px solid #e2e8f0; padding: 25px; box-shadow: 0 4px 6px -1px rgba(0,0,0,0.05); }
    .header { background-color: #1e3a8a; border-radius: 12px 12px 0 0; padding: 20px; margin: -25px -25px 20px -25px; text-align: center; }
    .header h1 { font-size: 26px; color: #ffffff; font-weight: 800; margin: 0; }
    .header .sub { font-size: 13px; color: #bfdbfe; margin-top: 6px; }
    .header .sub b { color: #ffffff; }
    
    /* Card Styles */
    .card { border-radius: 12px; padding: 18px; border: 1px solid; background-color: #ffffff; width: 100%; box-sizing: border-box; }
    .card-green { background-color: #f0fdf4; border-color: #bbf7d0; }
    .card-blue { background-color: #eff6ff; border-color: #bfdbfe; }
    .card-orange { background-color: #fffbeb; border-color: #fde68a; }
    .card-red { background-color: #fef2f2; border-color: #fecaca; }
    
    .card-icon-circle { width: 50px; height: 50px; border-radius: 50%; text-align: center; line-height: 50px; font-size: 24px; color: white; display: inline-block; vertical-align: middle; margin-right: 10px; }
    .icon-green { background-color: #22c55e; }
    .icon-blue { background-color: #3b82f6; }
    .icon-orange { background-color: #f59e0b; }
    .icon-red { background-color: #ef4444; }
    
    .card-header-text { display: inline-block; vertical-align: middle; }
    .card-header-title { font-size: 12px; text-transform: uppercase; font-weight: 800; color: #1e293b; letter-spacing: 0.5px; margin: 0 0 2px 0; }
    .card-header-value { font-size: 22px; font-weight: 700; margin: 0; line-height: 1.2; }
    
    .card-footer-row { border-top: 1px solid rgba(0,0,0,0.08); padding-top: 12px; text-align: center; margin-top: 15px; }
    .card-sub { font-size: 12px; color: #64748b; margin-bottom: 8px; }
    
    .card-badge { display: inline-block; padding: 4px 16px; border-radius: 20px; font-weight: 700; font-size: 12px; }
    .badge-green { background-color: #dcfce7; color: #166534; }
    .badge-red { background-color: #fee2e2; color: #991b1b; }
    .badge-orange { background-color: #fef3c7; color: #92400e; }
    
    .section-title { font-size: 15px; font-weight: 700; color: #0f172a; border-left: 4px solid #3b82f6; padding-left: 10px; margin-bottom: 12px; margin-top: 25px; }
    table { width: 100%; border-collapse: separate; border-spacing: 0; font-size: 12px; margin-bottom: 15px; border: 1px solid #1e3a8a; border-radius: 8px; overflow: hidden; }
    th { background-color: #1e3a8a; color: #ffffff; padding: 10px 12px; border: none; font-weight: 600; text-align: left; }
    td { padding: 9px 12px; border-bottom: 1px solid #e2e8f0; color: #334155; }
    tr:last-child td { border-bottom: none; }
    .text-center { text-align: center; }
    .text-right { text-align: right; }
    .status-badge { display: inline-block; padding: 3px 14px; border-radius: 20px; font-weight: 600; font-size: 11px; }
    .status-ok { background-color: #dcfce7; color: #166534; }
    .status-warn { background-color: #fef3c7; color: #92400e; }
    .status-err { background-color: #fee2e2; color: #991b1b; }
    .footer { margin-top: 20px; padding: 12px 15px; background-color: #eff6ff; border: 1px solid #bfdbfe; border-radius: 8px; color: #1e3a8a; font-size: 12px; }
    .footer .signature { margin-top: 10px; font-weight: 600; color: #0f172a; }
</style>';

-- Build Dashboard Cards using TABLE layout for Outlook compatibility
-- Using Unicode Emojis instead of Font Awesome
DECLARE @DashboardHTML NVARCHAR(MAX) = N'
<table width="100%" cellpadding="10" cellspacing="0" border="0">
<tr>
    <td width="25%" valign="top">
        <div class="card card-green">
            <div>
                <div class="card-icon-circle icon-green">&#128187;</div>
                <div class="card-header-text">
                    <div class="card-header-title">SQL Service</div>
                    <div class="card-header-value" style="color:#15803d;">Running</div>
                </div>
            </div>
            <div class="card-footer-row">
                <div class="card-badge badge-green">&#10004; HEALTHY</div>
            </div>
        </div>
    </td>
    <td width="25%" valign="top">
        <div class="card card-blue">
            <div>
                <div class="card-icon-circle icon-blue">&#128450;</div>
                <div class="card-header-text">
                    <div class="card-header-title">Databases</div>
                    <div class="card-header-value" style="color:#1d4ed8;">' + CAST(@OnlineDBs AS VARCHAR(10)) + ' / ' + CAST(@TotalDBs AS VARCHAR(10)) + ' Online</div>
                </div>
            </div>
            <div class="card-footer-row">
                <div class="card-sub">' + CAST(@OfflineCount AS VARCHAR(10)) + ' Offline / Suspect</div>
                <div class="card-badge badge-green">&#10004; HEALTHY</div>
            </div>
        </div>
    </td>
    <td width="25%" valign="top">
        <div class="card card-orange">
            <div>
                <div class="card-icon-circle icon-orange">&#128230;</div>
                <div class="card-header-text">
                    <div class="card-header-title">Backups</div>
                    <div class="card-header-value" style="color:#b45309;">' + CASE WHEN @BackupAlertCount = 0 THEN 'All Successful' ELSE CAST(@BackupAlertCount AS VARCHAR(10)) + ' Overdue' END + '</div>
                </div>
            </div>
            <div class="card-footer-row">
                <div class="card-sub">' + CAST(@BackupAlertCount AS VARCHAR(10)) + ' Overdue</div>
                <div class="card-badge ' + CASE WHEN @BackupAlertCount = 0 THEN 'badge-green' ELSE 'badge-orange' END + '">' + CASE WHEN @BackupAlertCount = 0 THEN '&#10004; HEALTHY' ELSE '&#9888; WARNING' END + '</div>
            </div>
        </div>
    </td>
    <td width="25%" valign="top">
        <div class="card card-red">
            <div>
                <div class="card-icon-circle icon-red">&#128190;</div>
                <div class="card-header-text">
                    <div class="card-header-title">Disk Space</div>
                    <div class="card-header-value" style="color:#dc2626;">' + @LowestDiskDrive + ' ' + CAST(@LowestDiskPct AS VARCHAR(10)) + '% Free</div>
                </div>
            </div>
            <div class="card-footer-row">
                <div class="card-sub">Lowest Free Space</div>
                <div class="card-badge badge-orange">&#9888; WARNING</div>
            </div>
        </div>
    </td>
</tr>
</table>';

-- 1. Server Details Table
DECLARE @ServerHTML NVARCHAR(MAX) = N'
<div class="section-title">1. SERVER DETAILS</div>
<table>
    <thead><tr>
        <th>GetDate</th><th>Hostname</th><th>SQL Instance</th><th>Edition</th><th>Build Number</th>
        <th>Is Cluster</th><th>Current Node</th><th>SQL Restart</th><th class="text-right">Uptime (Hrs)</th><th class="text-center">Status</th>
    </tr></thead>
    <tbody><tr>
        <td>' + CONVERT(VARCHAR(19), @GetDate, 120) + '</td>
        <td>' + @Hostname + '</td>
        <td style="font-weight:600;">' + @InstanceName + '</td>
        <td>' + @Edition + '</td>
        <td>' + @BuildNumber + '</td>
        <td>' + CASE WHEN @IsCluster = 1 THEN 'Yes' ELSE 'No' END + '</td>
        <td>' + @CurrentNode + '</td>
        <td>' + CONVERT(VARCHAR(16), @SQLRestart, 120) + '</td>
        <td class="text-right">' + CAST(@UptimeHours AS VARCHAR(10)) + '</td>
        <td class="text-center"><span class="status-badge status-ok">Relax</span></td>
    </tr></tbody>
</table>';

-- 2. Database Details Table
DECLARE @DbHTML NVARCHAR(MAX) = N'
<div class="section-title">2. DATABASE DETAILS</div>
<table>
    <thead><tr>
        <th>Database Name</th><th>Recovery Model</th><th>Compatibility Level</th><th class="text-right">Database Size (GB)</th><th>State</th><th class="text-center">Status</th>
    </tr></thead>
    <tbody>';

SELECT @DbHTML = @DbHTML + 
    '<tr>' +
    '<td style="font-weight:600;">' + name + '</td>' +
    '<td>' + recovery_model_desc + '</td>' +
    '<td>' + CAST(compatibility_level AS VARCHAR) + '</td>' +
    '<td class="text-right">' + CAST(SizeGB AS VARCHAR) + '</td>' +
    '<td>' + CASE WHEN state_desc = 'ONLINE' THEN state_desc ELSE '<span style="color:#dc2626;font-weight:700;">' + state_desc + '</span>' END + '</td>' +
    '<td class="text-center"><span class="status-badge ' + CASE WHEN state_desc = 'ONLINE' THEN 'status-ok' ELSE 'status-err' END + '">' + CASE WHEN state_desc = 'ONLINE' THEN 'Relax' ELSE 'CheckNow' END + '</span></td>' +
    '</tr>'
FROM #DatabaseInfo
ORDER BY name;

SET @DbHTML = @DbHTML + '</tbody></table>';

-- 3. Database Backup Details
DECLARE @BackupHTML NVARCHAR(MAX) = N'
<div class="section-title">3. DATABASE BACKUP DETAILS</div>
<table>
    <thead><tr>
        <th>Database Name</th><th>Recovery Model</th><th>Last Full Backup</th><th class="text-right">Full Age</th>
        <th>Last Diff Backup</th><th class="text-right">Diff Age</th>
        <th>Last Log Backup</th><th class="text-right">Log Age</th><th class="text-center">Status</th>
    </tr></thead>
    <tbody>';

SELECT @BackupHTML = @BackupHTML + 
    '<tr>' +
    '<td style="font-weight:600;">' + DBName + '</td>' +
    '<td>' + recovery_model_desc + '</td>' +
    '<td>' + ISNULL(CONVERT(VARCHAR(16), LastFull, 120), '<span style="color:#94a3b8;">Never</span>') + '</td>' +
    '<td class="text-right">' + ISNULL(CAST(FullAgeDays AS VARCHAR(10)) + ' Days', 'N/A') + '</td>' +
    '<td>' + ISNULL(CONVERT(VARCHAR(16), LastDiff, 120), '<span style="color:#94a3b8;">Never</span>') + '</td>' +
    '<td class="text-right">' + ISNULL(CAST(DiffAgeHours AS VARCHAR(10)) + ' Hrs', 'N/A') + '</td>' +
    '<td>' + ISNULL(CONVERT(VARCHAR(16), LastLog, 120), '<span style="color:#94a3b8;">Never</span>') + '</td>' +
    '<td class="text-right">' + 
        CASE WHEN recovery_model_desc = 'SIMPLE' THEN 'N/A'
             WHEN LogAgeMinutes IS NULL THEN 'N/A'
             WHEN LogAgeMinutes < 60 THEN CAST(LogAgeMinutes AS VARCHAR(10)) + ' Min'
             ELSE CAST(LogAgeMinutes / 60 AS VARCHAR(10)) + ' Hrs'
        END + '</td>' +
    '<td class="text-center"><span class="status-badge ' + 
        CASE WHEN FullAgeDays > 7 OR FullAgeDays IS NULL OR DiffAgeHours > 24 OR (recovery_model_desc <> 'SIMPLE' AND (LogAgeMinutes > 60 OR LogAgeMinutes IS NULL)) THEN 'status-err' ELSE 'status-ok' END + '">' +
        CASE WHEN FullAgeDays > 7 OR FullAgeDays IS NULL OR DiffAgeHours > 24 OR (recovery_model_desc <> 'SIMPLE' AND (LogAgeMinutes > 60 OR LogAgeMinutes IS NULL)) THEN 'CheckNow' ELSE 'Relax' END + '</span></td>' +
    '</tr>'
FROM #BackupInfo
ORDER BY DBName;

SET @BackupHTML = @BackupHTML + '</tbody></table>';

-- 4. Disk Details Table
DECLARE @DiskHTML NVARCHAR(MAX) = N'
<div class="section-title">4. DISK DETAILS</div>
<table>
    <thead><tr>
        <th>Drive</th><th class="text-right">Total (GB)</th><th class="text-right">Used (GB)</th><th class="text-right">Free (GB)</th>
        <th class="text-center">Free %</th><th class="text-center">Status</th>
    </tr></thead>
    <tbody>';

SELECT @DiskHTML = @DiskHTML + 
    '<tr>' +
    '<td style="font-weight:600;">' + VolumeName + '</td>' +
    '<td class="text-right">' + CAST(TotalGB AS VARCHAR(20)) + '</td>' +
    '<td class="text-right">' + CAST(UsedGB AS VARCHAR(20)) + '</td>' +
    '<td class="text-right" style="font-weight:' + CASE WHEN FreePct < 25 THEN '700' ELSE '400' END + ';">' + CAST(FreeGB AS VARCHAR(20)) + '</td>' +
    '<td class="text-center" style="font-weight:700;color:' + CASE WHEN FreePct < 15 THEN '#dc2626' WHEN FreePct < 25 THEN '#b45309' ELSE '#1e293b' END + ';">' + CAST(FreePct AS VARCHAR(10)) + '%</td>' +
    '<td class="text-center"><span class="status-badge ' + 
        CASE WHEN FreePct < 15 THEN 'status-err' WHEN FreePct < 25 THEN 'status-warn' ELSE 'status-ok' END + '">' +
        CASE WHEN FreePct < 25 THEN 'CheckNow' ELSE 'Relax' END + '</span></td>' +
    '</tr>'
FROM #DiskInfo
ORDER BY VolumeName;

SET @DiskHTML = @DiskHTML + '</tbody></table>';

-- Footer with Signature
DECLARE @FooterHTML NVARCHAR(MAX) = N'
<div class="footer">
    <div style="font-size: 12px; color: #1e3a8a;">
        <i>&#9432;</i> This is an automated email. Please do not reply.
    </div>
    <div class="signature">
        Thanks &amp; regards,<br>
        Your Name
    </div>
</div>';

--------------------------------------------------------------------------------
-- 5. ASSEMBLE COMPLETE HTML DOCUMENT
--------------------------------------------------------------------------------
DECLARE @FullHTMLBody NVARCHAR(MAX) = N'
<!DOCTYPE html>
<html>
<head>' + @CSS + '</head>
<body>
    <div class="container">
        <div class="header">
            <h1>SQL Server Health Check Report</h1>
            <div class="sub">
                Server: <b>' + @InstanceName + '</b> &nbsp;|&nbsp; 
                Generated: ' + CONVERT(VARCHAR(19), @GetDate, 120) + ' &nbsp;|&nbsp; 
                SQL Version: ' + @Edition + ' (' + @BuildNumber + ')
            </div>
        </div>
        ' + @DashboardHTML + '
        ' + @ServerHTML + '
        ' + @DbHTML + '
        ' + @BackupHTML + '
        ' + @DiskHTML + '
        ' + @FooterHTML + '
    </div>
</body>
</html>';

--------------------------------------------------------------------------------
-- 6. SEND EMAIL
--------------------------------------------------------------------------------
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = @MailProfile,
    @recipients   = @Recipients,
    @subject      = @Subject,
    @body         = @FullHTMLBody,
    @body_format  = 'HTML';

-- Cleanup
IF OBJECT_ID('tempdb..#DatabaseInfo') IS NOT NULL DROP TABLE #DatabaseInfo;
IF OBJECT_ID('tempdb..#BackupInfo') IS NOT NULL DROP TABLE #BackupInfo;
IF OBJECT_ID('tempdb..#DiskInfo') IS NOT NULL DROP TABLE #DiskInfo;

END;
GO


*/
Read more »

July 25, 2026

 Check here. 




USE master;
GO
IF OBJECT_ID('dbo.sp_Send_SQLHealthCheck_Report', 'P') IS NOT NULL
DROP PROCEDURE dbo.sp_Send_SQLHealthCheck_Report;
GO
CREATE PROCEDURE dbo.sp_Send_SQLHealthCheck_Report
@MailProfile NVARCHAR(128) = 'your_mail_profile', -- Replace with your DB Mail Profile
@Recipients  NVARCHAR(MAX) = 'dba-team@company.com' -- Replace with target email address
AS
BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

--------------------------------------------------------------------------------
-- 1. SERVER & DATABASE METADATA
--------------------------------------------------------------------------------
DECLARE @GetDate DATETIME = GETDATE();
DECLARE @Subject NVARCHAR(256) = 'SQL Server Health Check Report - ' + @@SERVERNAME;
DECLARE @Hostname NVARCHAR(128) = CAST(SERVERPROPERTY('MachineName') AS NVARCHAR(128));
DECLARE @InstanceName NVARCHAR(128) = @@SERVERNAME;
DECLARE @Edition NVARCHAR(128) = CAST(SERVERPROPERTY('Edition') AS NVARCHAR(128));
DECLARE @BuildNumber NVARCHAR(128) = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128));
DECLARE @IsCluster INT = CAST(SERVERPROPERTY('IsClustered') AS INT);
DECLARE @CurrentNode NVARCHAR(128) = ISNULL(CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS NVARCHAR(128)), 'N/A');
DECLARE @SQLRestart DATETIME;
DECLARE @UptimeHours INT;

SELECT @SQLRestart = sqlserver_start_time FROM sys.dm_os_sys_info;
SET @UptimeHours = DATEDIFF(HOUR, @SQLRestart, @GetDate);

--------------------------------------------------------------------------------
-- 2. TEMP TABLES FOR DATA COLLECTION
--------------------------------------------------------------------------------
IF OBJECT_ID('tempdb..#DatabaseInfo') IS NOT NULL DROP TABLE #DatabaseInfo;
IF OBJECT_ID('tempdb..#BackupInfo') IS NOT NULL DROP TABLE #BackupInfo;
IF OBJECT_ID('tempdb..#DiskInfo') IS NOT NULL DROP TABLE #DiskInfo;

-- Database State & Size
SELECT 
    d.name,
    d.recovery_model_desc,
    d.compatibility_level,
    CAST(SUM(mf.size * 8.0 / 1024 / 1024) AS DECIMAL(10,2)) AS SizeGB,
    d.state_desc,
    d.database_id
INTO #DatabaseInfo
FROM sys.databases d
LEFT JOIN sys.master_files mf ON d.database_id = mf.database_id
WHERE d.name NOT IN ('tempdb')
GROUP BY d.name, d.recovery_model_desc, d.compatibility_level, d.state_desc, d.database_id;

-- Backup Information
SELECT 
    d.name AS DBName,
    d.recovery_model_desc,
    MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS LastFull,
    DATEDIFF(DAY, MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END), GETDATE()) AS FullAgeDays,
    MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END) AS LastDiff,
    DATEDIFF(HOUR, MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END), GETDATE()) AS DiffAgeHours,
    MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS LastLog,
    DATEDIFF(MINUTE, MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END), GETDATE()) AS LogAgeMinutes
INTO #BackupInfo
FROM #DatabaseInfo d
LEFT JOIN msdb.dbo.backupset b ON d.name = b.database_name
GROUP BY d.name, d.recovery_model_desc;

-- Disk Space
SELECT DISTINCT
    volume_mount_point AS VolumeName,
    CAST(total_bytes / 1073741824.0 AS DECIMAL(10,2)) AS TotalGB,
    CAST((total_bytes - available_bytes) / 1073741824.0 AS DECIMAL(10,2)) AS UsedGB,
    CAST(available_bytes / 1073741824.0 AS DECIMAL(10,2)) AS FreeGB,
    CAST((available_bytes * 100.0 / total_bytes) AS DECIMAL(5,2)) AS FreePct
INTO #DiskInfo
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id);

--------------------------------------------------------------------------------
-- 3. EXECUTIVE SUMMARY KPIs
--------------------------------------------------------------------------------
DECLARE @TotalDBs INT = (SELECT COUNT(*) FROM #DatabaseInfo);
DECLARE @OnlineDBs INT = (SELECT COUNT(*) FROM #DatabaseInfo WHERE state_desc = 'ONLINE');
DECLARE @OfflineCount INT = @TotalDBs - @OnlineDBs;
DECLARE @BackupAlertCount INT = 0;

SELECT @BackupAlertCount = COUNT(*) 
FROM #BackupInfo 
WHERE FullAgeDays > 7 OR FullAgeDays IS NULL 
   OR DiffAgeHours > 24 
   OR (recovery_model_desc <> 'SIMPLE' AND (LogAgeMinutes > 60 OR LogAgeMinutes IS NULL));

DECLARE @LowestDiskDrive NVARCHAR(128) = '';
DECLARE @LowestDiskPct DECIMAL(5,2) = 100.0;

SELECT TOP 1 @LowestDiskDrive = VolumeName, @LowestDiskPct = FreePct 
FROM #DiskInfo ORDER BY FreePct ASC;

--------------------------------------------------------------------------------
-- 4. HTML CSS STYLES & BUILD STRINGS
--------------------------------------------------------------------------------
DECLARE @CSS NVARCHAR(MAX) = N'
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
<style>
    body { font-family: "Segoe UI", Arial, sans-serif; background-color: #f8fafc; margin: 0; padding: 20px; }
    .container { max-width: 1150px; margin: auto; background-color: #ffffff; border-radius: 12px; border: 1px solid #e2e8f0; padding: 25px; box-shadow: 0 4px 6px -1px rgba(0,0,0,0.05); }
    .header { background-color: #1e3a8a; border-radius: 12px 12px 0 0; padding: 20px; margin: -25px -25px 20px -25px; text-align: center; }
    .header h1 { font-size: 26px; color: #ffffff; font-weight: 800; margin: 0; }
    .header .sub { font-size: 13px; color: #bfdbfe; margin-top: 6px; }
    .header .sub b { color: #ffffff; }
    
    /* Dashboard Cards */
    .dashboard { display: flex; gap: 15px; margin-bottom: 25px; flex-wrap: wrap; }
    .card { flex: 1; min-width: 220px; border-radius: 12px; padding: 18px; border: 1px solid; position: relative; background-color: #ffffff; }
    .card-green { background-color: #f0fdf4; border-color: #bbf7d0; }
    .card-blue { background-color: #eff6ff; border-color: #bfdbfe; }
    .card-orange { background-color: #fffbeb; border-color: #fde68a; }
    .card-red { background-color: #fef2f2; border-color: #fecaca; }
    
    .card-header-row { display: flex; align-items: center; gap: 15px; margin-bottom: 15px; }
    .card-icon { width: 56px; height: 56px; border-radius: 50%; display: flex; align-items: center; justify-content: center; color: white; font-size: 26px; flex-shrink: 0; }
    .icon-green { background-color: #22c55e; }
    .icon-blue { background-color: #3b82f6; }
    .icon-orange { background-color: #f59e0b; }
    .icon-red { background-color: #ef4444; }
    
    .card-header-text { display: flex; flex-direction: column; }
    .card-header-title { font-size: 12px; text-transform: uppercase; font-weight: 800; color: #1e293b; letter-spacing: 0.5px; margin-bottom: 2px; }
    .card-header-value { font-size: 22px; font-weight: 700; margin: 0; line-height: 1.2; }
    
    .card-footer-row { border-top: 1px solid rgba(0,0,0,0.08); padding-top: 12px; text-align: center; }
    .card-sub { font-size: 12px; color: #64748b; margin-bottom: 8px; }
    
    .card-badge { display: inline-block; padding: 4px 16px; border-radius: 20px; font-weight: 700; font-size: 12px; }
    .badge-green { background-color: #dcfce7; color: #166534; }
    .badge-red { background-color: #fee2e2; color: #991b1b; }
    .badge-orange { background-color: #fef3c7; color: #92400e; }
    
    .section-title { font-size: 15px; font-weight: 700; color: #0f172a; border-left: 4px solid #3b82f6; padding-left: 10px; margin-bottom: 12px; margin-top: 25px; }
    table { width: 100%; border-collapse: separate; border-spacing: 0; font-size: 12px; margin-bottom: 15px; border: 1px solid #1e3a8a; border-radius: 8px; overflow: hidden; }
    th { background-color: #1e3a8a; color: #ffffff; padding: 10px 12px; border: none; font-weight: 600; text-align: left; }
    td { padding: 9px 12px; border-bottom: 1px solid #e2e8f0; color: #334155; }
    tr:last-child td { border-bottom: none; }
    .text-center { text-align: center; }
    .text-right { text-align: right; }
    .status-badge { display: inline-block; padding: 3px 14px; border-radius: 20px; font-weight: 600; font-size: 11px; }
    .status-ok { background-color: #dcfce7; color: #166534; }
    .status-warn { background-color: #fef3c7; color: #92400e; }
    .status-err { background-color: #fee2e2; color: #991b1b; }
    .footer { margin-top: 20px; padding: 12px 15px; background-color: #eff6ff; border: 1px solid #bfdbfe; border-radius: 8px; color: #1e3a8a; font-size: 12px; display: flex; flex-direction: column; align-items: flex-start; gap: 8px; }
    .footer .disclaimer { display: flex; align-items: center; gap: 8px; width: 100%; }
    .footer .signature { margin-top: 4px; font-weight: 600; color: #0f172a; }
    @media (max-width: 700px) { .dashboard { flex-direction: column; } }
</style>';

-- Build Dashboard Cards
DECLARE @DashboardHTML NVARCHAR(MAX) = N'
<div class="dashboard">
    <div class="card card-green">
        <div class="card-header-row">
            <div class="card-icon icon-green"><i class="fas fa-desktop"></i></div>
            <div class="card-header-text">
                <div class="card-header-title">SQL Service</div>
                <div class="card-header-value" style="color:#15803d;">Running</div>
            </div>
        </div>
        <div class="card-footer-row">
            <div class="card-badge badge-green"><i class="fas fa-check-circle" style="margin-right:4px;"></i>HEALTHY</div>
        </div>
    </div>
    <div class="card card-blue">
        <div class="card-header-row">
            <div class="card-icon icon-blue"><i class="fas fa-database"></i></div>
            <div class="card-header-text">
                <div class="card-header-title">Databases</div>
                <div class="card-header-value" style="color:#1d4ed8;">' + CAST(@OnlineDBs AS VARCHAR(10)) + ' / ' + CAST(@TotalDBs AS VARCHAR(10)) + ' Online</div>
            </div>
        </div>
        <div class="card-footer-row">
            <div class="card-sub">' + CAST(@OfflineCount AS VARCHAR(10)) + ' Offline / Suspect</div>
            <div class="card-badge badge-green"><i class="fas fa-check-circle" style="margin-right:4px;"></i>HEALTHY</div>
        </div>
    </div>
    <div class="card card-orange">
        <div class="card-header-row">
            <div class="card-icon icon-orange"><i class="fas fa-box"></i></div>
            <div class="card-header-text">
                <div class="card-header-title">Backups</div>
                <div class="card-header-value" style="color:#b45309;">' + CASE WHEN @BackupAlertCount = 0 THEN 'All Successful' ELSE CAST(@BackupAlertCount AS VARCHAR(10)) + ' Overdue' END + '</div>
            </div>
        </div>
        <div class="card-footer-row">
            <div class="card-sub">' + CAST(@BackupAlertCount AS VARCHAR(10)) + ' Overdue</div>
            <div class="card-badge ' + CASE WHEN @BackupAlertCount = 0 THEN 'badge-green' ELSE 'badge-orange' END + '"><i class="fas ' + CASE WHEN @BackupAlertCount = 0 THEN 'fa-check-circle' ELSE 'fa-exclamation-triangle' END + '" style="margin-right:4px;"></i>' + CASE WHEN @BackupAlertCount = 0 THEN 'HEALTHY' ELSE 'WARNING' END + '</div>
        </div>
    </div>
    <div class="card card-red">
        <div class="card-header-row">
            <div class="card-icon icon-red"><i class="fas fa-hard-drive"></i></div>
            <div class="card-header-text">
                <div class="card-header-title">Disk Space</div>
                <div class="card-header-value" style="color:#dc2626;">' + @LowestDiskDrive + ' ' + CAST(@LowestDiskPct AS VARCHAR(10)) + '% Free</div>
            </div>
        </div>
        <div class="card-footer-row">
            <div class="card-sub">Lowest Free Space</div>
            <div class="card-badge badge-orange"><i class="fas fa-exclamation-triangle" style="margin-right:4px;"></i>WARNING</div>
        </div>
    </div>
</div>';

-- 1. Server Details Table
DECLARE @ServerHTML NVARCHAR(MAX) = N'
<div class="section-title">1. SERVER DETAILS</div>
<table>
    <thead><tr>
        <th>GetDate</th><th>Hostname</th><th>SQL Instance</th><th>Edition</th><th>Build Number</th>
        <th>Is Cluster</th><th>Current Node</th><th>SQL Restart</th><th class="text-right">Uptime (Hrs)</th><th class="text-center">Status</th>
    </tr></thead>
    <tbody><tr>
        <td>' + CONVERT(VARCHAR(19), @GetDate, 120) + '</td>
        <td>' + @Hostname + '</td>
        <td style="font-weight:600;">' + @InstanceName + '</td>
        <td>' + @Edition + '</td>
        <td>' + @BuildNumber + '</td>
        <td>' + CASE WHEN @IsCluster = 1 THEN 'Yes' ELSE 'No' END + '</td>
        <td>' + @CurrentNode + '</td>
        <td>' + CONVERT(VARCHAR(16), @SQLRestart, 120) + '</td>
        <td class="text-right">' + CAST(@UptimeHours AS VARCHAR(10)) + '</td>
        <td class="text-center"><span class="status-badge status-ok">Relax</span></td>
    </tr></tbody>
</table>';

-- 2. Database Details Table
DECLARE @DbHTML NVARCHAR(MAX) = N'
<div class="section-title">2. DATABASE DETAILS</div>
<table>
    <thead><tr>
        <th>Database Name</th><th>Recovery Model</th><th>Compatibility Level</th><th class="text-right">Database Size (GB)</th><th>State</th><th class="text-center">Status</th>
    </tr></thead>
    <tbody>';

SELECT @DbHTML = @DbHTML + 
    '<tr>' +
    '<td style="font-weight:600;">' + name + '</td>' +
    '<td>' + recovery_model_desc + '</td>' +
    '<td>' + CAST(compatibility_level AS VARCHAR) + '</td>' +
    '<td class="text-right">' + CAST(SizeGB AS VARCHAR) + '</td>' +
    '<td>' + CASE WHEN state_desc = 'ONLINE' THEN state_desc ELSE '<span style="color:#dc2626;font-weight:700;">' + state_desc + '</span>' END + '</td>' +
    '<td class="text-center"><span class="status-badge ' + CASE WHEN state_desc = 'ONLINE' THEN 'status-ok' ELSE 'status-err' END + '">' + CASE WHEN state_desc = 'ONLINE' THEN 'Relax' ELSE 'CheckNow' END + '</span></td>' +
    '</tr>'
FROM #DatabaseInfo
ORDER BY name;

SET @DbHTML = @DbHTML + '</tbody></table>';

-- 3. Database Backup Details
DECLARE @BackupHTML NVARCHAR(MAX) = N'
<div class="section-title">3. DATABASE BACKUP DETAILS</div>
<table>
    <thead><tr>
        <th>Database Name</th><th>Recovery Model</th><th>Last Full Backup</th><th class="text-right">Full Age</th>
        <th>Last Diff Backup</th><th class="text-right">Diff Age</th>
        <th>Last Log Backup</th><th class="text-right">Log Age</th><th class="text-center">Status</th>
    </tr></thead>
    <tbody>';

SELECT @BackupHTML = @BackupHTML + 
    '<tr>' +
    '<td style="font-weight:600;">' + DBName + '</td>' +
    '<td>' + recovery_model_desc + '</td>' +
    '<td>' + ISNULL(CONVERT(VARCHAR(16), LastFull, 120), '<span style="color:#94a3b8;">Never</span>') + '</td>' +
    '<td class="text-right">' + ISNULL(CAST(FullAgeDays AS VARCHAR(10)) + ' Days', 'N/A') + '</td>' +
    '<td>' + ISNULL(CONVERT(VARCHAR(16), LastDiff, 120), '<span style="color:#94a3b8;">Never</span>') + '</td>' +
    '<td class="text-right">' + ISNULL(CAST(DiffAgeHours AS VARCHAR(10)) + ' Hrs', 'N/A') + '</td>' +
    '<td>' + ISNULL(CONVERT(VARCHAR(16), LastLog, 120), '<span style="color:#94a3b8;">Never</span>') + '</td>' +
    '<td class="text-right">' + 
        CASE WHEN recovery_model_desc = 'SIMPLE' THEN 'N/A'
             WHEN LogAgeMinutes IS NULL THEN 'N/A'
             WHEN LogAgeMinutes < 60 THEN CAST(LogAgeMinutes AS VARCHAR(10)) + ' Min'
             ELSE CAST(LogAgeMinutes / 60 AS VARCHAR(10)) + ' Hrs'
        END + '</td>' +
    '<td class="text-center"><span class="status-badge ' + 
        CASE WHEN FullAgeDays > 7 OR FullAgeDays IS NULL OR DiffAgeHours > 24 OR (recovery_model_desc <> 'SIMPLE' AND (LogAgeMinutes > 60 OR LogAgeMinutes IS NULL)) THEN 'status-err' ELSE 'status-ok' END + '">' +
        CASE WHEN FullAgeDays > 7 OR FullAgeDays IS NULL OR DiffAgeHours > 24 OR (recovery_model_desc <> 'SIMPLE' AND (LogAgeMinutes > 60 OR LogAgeMinutes IS NULL)) THEN 'CheckNow' ELSE 'Relax' END + '</span></td>' +
    '</tr>'
FROM #BackupInfo
ORDER BY DBName;

SET @BackupHTML = @BackupHTML + '</tbody></table>';

-- 4. Disk Details Table (UPDATED: Removed Progress Bar)
DECLARE @DiskHTML NVARCHAR(MAX) = N'
<div class="section-title">4. DISK DETAILS</div>
<table>
    <thead><tr>
        <th>Drive</th><th class="text-right">Total (GB)</th><th class="text-right">Used (GB)</th><th class="text-right">Free (GB)</th>
        <th class="text-center">Free %</th><th class="text-center">Status</th>
    </tr></thead>
    <tbody>';

SELECT @DiskHTML = @DiskHTML + 
    '<tr>' +
    '<td style="font-weight:600;">' + VolumeName + '</td>' +
    '<td class="text-right">' + CAST(TotalGB AS VARCHAR(20)) + '</td>' +
    '<td class="text-right">' + CAST(UsedGB AS VARCHAR(20)) + '</td>' +
    '<td class="text-right" style="font-weight:' + CASE WHEN FreePct < 25 THEN '700' ELSE '400' END + ';">' + CAST(FreeGB AS VARCHAR(20)) + '</td>' +
    '<td class="text-center" style="font-weight:700;color:' + CASE WHEN FreePct < 15 THEN '#dc2626' WHEN FreePct < 25 THEN '#b45309' ELSE '#1e293b' END + ';">' + CAST(FreePct AS VARCHAR(10)) + '%</td>' +
    '<td class="text-center"><span class="status-badge ' + 
        CASE WHEN FreePct < 15 THEN 'status-err' WHEN FreePct < 25 THEN 'status-warn' ELSE 'status-ok' END + '">' +
        CASE WHEN FreePct < 25 THEN 'CheckNow' ELSE 'Relax' END + '</span></td>' +
    '</tr>'
FROM #DiskInfo
ORDER BY VolumeName;

SET @DiskHTML = @DiskHTML + '</tbody></table>';

-- Footer with Signature
DECLARE @FooterHTML NVARCHAR(MAX) = N'
<div class="footer">
    <div class="disclaimer">
        <i class="fas fa-info-circle" style="font-size:16px;"></i>
        This is an automated email. Please do not reply.
    </div>
    <div class="signature">
        Thanks &amp; regards,<br>
        Siva Padala, MS SQL DBA
    </div>
</div>';

--------------------------------------------------------------------------------
-- 5. ASSEMBLE COMPLETE HTML DOCUMENT
--------------------------------------------------------------------------------
DECLARE @FullHTMLBody NVARCHAR(MAX) = N'
<!DOCTYPE html>
<html>
<head>' + @CSS + '</head>
<body>
    <div class="container">
        <div class="header">
            <h1>SQL Server Health Check Report</h1>
            <div class="sub">
                Server: <b>' + @InstanceName + '</b> &nbsp;|&nbsp; 
                Generated: ' + CONVERT(VARCHAR(19), @GetDate, 120) + ' &nbsp;|&nbsp; 
                SQL Version: ' + @Edition + ' (' + @BuildNumber + ')
            </div>
        </div>
        ' + @DashboardHTML + '
        ' + @ServerHTML + '
        ' + @DbHTML + '
        ' + @BackupHTML + '
        ' + @DiskHTML + '
        ' + @FooterHTML + '
    </div>
</body>
</html>';

--------------------------------------------------------------------------------
-- 6. SEND EMAIL
--------------------------------------------------------------------------------
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = @MailProfile,
    @recipients   = @Recipients,
    @subject      = @Subject,
    @body         = @FullHTMLBody,
    @body_format  = 'HTML';

-- Cleanup
IF OBJECT_ID('tempdb..#DatabaseInfo') IS NOT NULL DROP TABLE #DatabaseInfo;
IF OBJECT_ID('tempdb..#BackupInfo') IS NOT NULL DROP TABLE #BackupInfo;
IF OBJECT_ID('tempdb..#DiskInfo') IS NOT NULL DROP TABLE #DiskInfo;

END;
GO

/*



Read more »

July 24, 2026

Here is the script. 

/* 

USE master;

GO


IF OBJECT_ID('dbo.sp_Send_SQLHealthCheck_Report', 'P') IS NOT NULL

    DROP PROCEDURE dbo.sp_Send_SQLHealthCheck_Report;

GO


CREATE PROCEDURE dbo.sp_Send_SQLHealthCheck_Report

    @MailProfile NVARCHAR(128) = 'your_mail_profile', -- Replace with your DB Mail Profile

    @Recipients  NVARCHAR(MAX) = 'dba-team@company.com' -- Replace with target email address

AS

BEGIN

    SET NOCOUNT ON;

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;


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

    -- 1. REUSABLE INLINE CSS STYLES

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

    DECLARE @TableStyle VARCHAR(250) = 'width: 100%; border-collapse: collapse; margin-bottom: 25px; font-family: Segoe UI, Arial, sans-serif; font-size: 12px; text-align: left;';

    DECLARE @ThStyle VARCHAR(200)    = 'background-color: #0f172a; color: #ffffff; padding: 9px 10px; border: 1px solid #0f172a; font-weight: 600;';

    DECLARE @TdStyle VARCHAR(200)    = 'padding: 8px 10px; border-bottom: 1px solid #e2e8f0; color: #333333;';

    DECLARE @TdRightStyle VARCHAR(200)= 'padding: 8px 10px; border-bottom: 1px solid #e2e8f0; color: #333333; text-align: right;';

    

    DECLARE @BadgeGreen VARCHAR(200) = 'background-color: #d1fae5; color: #065f46; font-weight: bold; border-radius: 12px; padding: 3px 10px; text-align: center; display: inline-block;';

    DECLARE @BadgeYellow VARCHAR(200)= 'background-color: #fef3c7; color: #92400e; font-weight: bold; border-radius: 12px; padding: 3px 10px; text-align: center; display: inline-block;';

    DECLARE @BadgeRed VARCHAR(200)   = 'background-color: #fee2e2; color: #991b1b; font-weight: bold; border-radius: 12px; padding: 3px 10px; text-align: center; display: inline-block;';


    DECLARE @GetDate DATETIME = GETDATE();

    DECLARE @Subject NVARCHAR(256) = 'SQL Server Health Check Report - ' + @@SERVERNAME;


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

    -- 2. DATA COLLECTION: SERVER DETAILS

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

    DECLARE @Hostname NVARCHAR(128) = CAST(SERVERPROPERTY('MachineName') AS NVARCHAR(128));

    DECLARE @InstanceName NVARCHAR(128) = @@SERVERNAME;

    DECLARE @Edition NVARCHAR(128) = CAST(SERVERPROPERTY('Edition') AS NVARCHAR(128));

    DECLARE @BuildNumber NVARCHAR(128) = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128));

    DECLARE @IsCluster INT = CAST(SERVERPROPERTY('IsClustered') AS INT);

    DECLARE @CurrentNode NVARCHAR(128) = ISNULL(CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS NVARCHAR(128)), 'N/A');

    DECLARE @SQLRestart DATETIME;

    DECLARE @UptimeHours INT;


    SELECT @SQLRestart = sqlserver_start_time FROM sys.dm_os_sys_info;

    SET @UptimeHours = DATEDIFF(HOUR, @SQLRestart, @GetDate);


    DECLARE @ServerBadgeStyle VARCHAR(200) = CASE WHEN @UptimeHours < 24 THEN @BadgeRed ELSE @BadgeGreen END;

    DECLARE @ServerStatusText VARCHAR(50)  = CASE WHEN @UptimeHours < 24 THEN 'CheckNow' ELSE 'Relax' END;


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

    -- 3. DATA COLLECTION: DATABASES & BACKUPS

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

    IF OBJECT_ID('tempdb..#BackupInfo') IS NOT NULL DROP TABLE #BackupInfo;


    SELECT 

        d.name AS DBName,

        d.recovery_model_desc,

        d.state_desc,

        MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS LastFull,

        DATEDIFF(DAY, MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END), GETDATE()) AS FullAgeDays,

        MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END) AS LastDiff,

        DATEDIFF(HOUR, MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END), GETDATE()) AS DiffAgeHours,

        MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS LastLog,

        DATEDIFF(MINUTE, MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END), GETDATE()) AS LogAgeMinutes

    INTO #BackupInfo

    FROM sys.databases d

    LEFT JOIN msdb.dbo.backupset b ON d.name = b.database_name

    WHERE d.name NOT IN ('tempdb')

    GROUP BY d.name, d.recovery_model_desc, d.state_desc;


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

    -- 4. DATA COLLECTION: DISKS

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

    IF OBJECT_ID('tempdb..#DiskInfo') IS NOT NULL DROP TABLE #DiskInfo;


    CREATE TABLE #DiskInfo (

        VolumeName VARCHAR(128),

        TotalGB DECIMAL(10,2),

        UsedGB DECIMAL(10,2),

        FreeGB DECIMAL(10,2),

        FreePct DECIMAL(5,2)

    );


    INSERT INTO #DiskInfo

    SELECT DISTINCT 

        volume_mount_point,

        CAST(total_bytes / 1073741824.0 AS DECIMAL(10,2)),

        CAST((total_bytes - available_bytes) / 1073741824.0 AS DECIMAL(10,2)),

        CAST(available_bytes / 1073741824.0 AS DECIMAL(10,2)),

        CAST((available_bytes * 100.0 / total_bytes) AS DECIMAL(5,2))

    FROM sys.master_files AS f

    CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id);


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

    -- 5. CALCULATE EXECUTIVE KPI COUNTS

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

    DECLARE @OfflineOrRestoringCount INT = 0;

    DECLARE @BackupAlertCount INT = 0;

    DECLARE @DiskAlertCount INT = 0;

    DECLARE @LowestDiskDrive VARCHAR(128) = '';

    DECLARE @LowestDiskPct DECIMAL(5,2) = 100.0;


    SELECT @OfflineOrRestoringCount = COUNT(*) FROM sys.databases WHERE state_desc <> 'ONLINE' AND name NOT IN ('tempdb');


    SELECT @BackupAlertCount = COUNT(*) 

    FROM #BackupInfo 

    WHERE FullAgeDays > 7 OR FullAgeDays IS NULL 

       OR DiffAgeHours > 24 

       OR (recovery_model_desc <> 'SIMPLE' AND (LogAgeMinutes > 60 OR LogAgeMinutes IS NULL));


    SELECT @DiskAlertCount = COUNT(*) FROM #DiskInfo WHERE FreePct < 25;


    SELECT TOP 1 @LowestDiskDrive = VolumeName, @LowestDiskPct = FreePct 

    FROM #DiskInfo 

    ORDER BY FreePct ASC;


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

    -- 6. BUILD HTML: EXECUTIVE SUMMARY CARDS (TOP SECTION)

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

    DECLARE @SummaryHTML NVARCHAR(MAX) = N'

    <div style="display: flex; gap: 12px; margin-bottom: 25px;">

        <div style="flex: 1; border: 1px solid ' + CASE WHEN @UptimeHours < 24 THEN '#fecaca' ELSE '#bbf7d0' END + '; background-color: ' + CASE WHEN @UptimeHours < 24 THEN '#fef2f2' ELSE '#f0fdf4' END + '; border-radius: 8px; padding: 12px 15px; display: flex; align-items: center; gap: 12px;">

            <div style="font-size: 22px;">' + CASE WHEN @UptimeHours < 24 THEN '⚠️' ELSE '✅' END + '</div>

            <div>

                <div style="font-size: 11px; text-transform: uppercase; font-weight: 700; color: #475569;">SQL Service</div>

                <div style="font-size: 13px; font-weight: 700; color: ' + CASE WHEN @UptimeHours < 24 THEN '#dc2626' ELSE '#15803d' END + ';">' + CASE WHEN @UptimeHours < 24 THEN 'Rebooted < 24h' ELSE 'Running Normal' END + '</div>

            </div>

        </div>

        <div style="flex: 1; border: 1px solid ' + CASE WHEN @OfflineOrRestoringCount > 0 THEN '#fde68a' ELSE '#bbf7d0' END + '; background-color: ' + CASE WHEN @OfflineOrRestoringCount > 0 THEN '#fffbeb' ELSE '#f0fdf4' END + '; border-radius: 8px; padding: 12px 15px; display: flex; align-items: center; gap: 12px;">

            <div style="font-size: 22px;">' + CASE WHEN @OfflineOrRestoringCount > 0 THEN '⚠️' ELSE '✅' END + '</div>

            <div>

                <div style="font-size: 11px; text-transform: uppercase; font-weight: 700; color: #475569;">Database State</div>

                <div style="font-size: 13px; font-weight: 700; color: ' + CASE WHEN @OfflineOrRestoringCount > 0 THEN '#b45309' ELSE '#15803d' END + ';">' + CASE WHEN @OfflineOrRestoringCount > 0 THEN CAST(@OfflineOrRestoringCount AS VARCHAR(5)) + ' DB Not Online' ELSE 'All Online' END + '</div>

            </div>

        </div>

        <div style="flex: 1; border: 1px solid ' + CASE WHEN @BackupAlertCount > 0 THEN '#fecaca' ELSE '#bbf7d0' END + '; background-color: ' + CASE WHEN @BackupAlertCount > 0 THEN '#fef2f2' ELSE '#f0fdf4' END + '; border-radius: 8px; padding: 12px 15px; display: flex; align-items: center; gap: 12px;">

            <div style="font-size: 22px;">' + CASE WHEN @BackupAlertCount > 0 THEN '🚨' ELSE '✅' END + '</div>

            <div>

                <div style="font-size: 11px; text-transform: uppercase; font-weight: 700; color: #475569;">Backup Status</div>

                <div style="font-size: 13px; font-weight: 700; color: ' + CASE WHEN @BackupAlertCount > 0 THEN '#dc2626' ELSE '#15803d' END + ';">' + CASE WHEN @BackupAlertCount > 0 THEN CAST(@BackupAlertCount AS VARCHAR(5)) + ' Overdue' ELSE 'All Backups OK' END + '</div>

            </div>

        </div>

        <div style="flex: 1; border: 1px solid ' + CASE WHEN @LowestDiskPct < 15 THEN '#fecaca' WHEN @LowestDiskPct < 25 THEN '#fde68a' ELSE '#bbf7d0' END + '; background-color: ' + CASE WHEN @LowestDiskPct < 15 THEN '#fef2f2' WHEN @LowestDiskPct < 25 THEN '#fffbeb' ELSE '#f0fdf4' END + '; border-radius: 8px; padding: 12px 15px; display: flex; align-items: center; gap: 12px;">

            <div style="font-size: 22px;">💾</div>

            <div>

                <div style="font-size: 11px; text-transform: uppercase; font-weight: 700; color: #475569;">Disk Space</div>

                <div style="font-size: 13px; font-weight: 700; color: ' + CASE WHEN @LowestDiskPct < 15 THEN '#dc2626' WHEN @LowestDiskPct < 25 THEN '#b45309' ELSE '#15803d' END + ';">' + @LowestDiskDrive + ' (' + CAST(@LowestDiskPct AS VARCHAR(10)) + '% Free)</div>

            </div>

        </div>

    </div>';


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

    -- 7. BUILD HTML SECTIONS (1. SERVER, 2. DB, 3. BACKUPS, 4. DISK)

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

    -- 1. Server Details

    DECLARE @ServerHTML NVARCHAR(MAX) = N'

    <div style="font-size: 15px; font-weight: 700; color: #0f172a; border-left: 4px solid #0284c7; padding-left: 8px; margin-bottom: 10px;">1. Server Details</div>

    <table style="' + @TableStyle + '">

        <thead>

            <tr>

                <th style="' + @ThStyle + '">GetDate</th>

                <th style="' + @ThStyle + '">Hostname</th>

                <th style="' + @ThStyle + '">SQL Instance</th>

                <th style="' + @ThStyle + '">Edition</th>

                <th style="' + @ThStyle + '">Build Number</th>

                <th style="' + @ThStyle + '">Is Cluster</th>

                <th style="' + @ThStyle + '">Current Node</th>

                <th style="' + @ThStyle + '">SQL Restart</th>

                <th style="' + @ThStyle + ' text-align: right;">Uptime (Hrs)</th>

                <th style="' + @ThStyle + ' text-align: center;">Status</th>

            </tr>

        </thead>

        <tbody>

            <tr>

                <td style="' + @TdStyle + '">' + CONVERT(VARCHAR(16), @GetDate, 120) + '</td>

                <td style="' + @TdStyle + '">' + @Hostname + '</td>

                <td style="' + @TdStyle + ' font-weight: 600;">' + @InstanceName + '</td>

                <td style="' + @TdStyle + '">' + @Edition + '</td>

                <td style="' + @TdStyle + '">' + @BuildNumber + '</td>

                <td style="' + @TdStyle + '">' + CASE WHEN @IsCluster = 1 THEN 'Yes' ELSE 'No' END + '</td>

                <td style="' + @TdStyle + '">' + @CurrentNode + '</td>

                <td style="' + @TdStyle + '">' + CONVERT(VARCHAR(16), @SQLRestart, 120) + '</td>

                <td style="' + @TdRightStyle + '">' + CAST(@UptimeHours AS VARCHAR(10)) + '</td>

                <td style="' + @TdStyle + ' text-align: center;"><span style="' + @ServerBadgeStyle + '">' + @ServerStatusText + '</span></td>

            </tr>

        </tbody>

    </table>';


    -- 2. Database Details

    DECLARE @DbHTML NVARCHAR(MAX) = N'

    <div style="font-size: 15px; font-weight: 700; color: #0f172a; border-left: 4px solid #0284c7; padding-left: 8px; margin-bottom: 10px;">2. Database Details</div>

    <table style="' + @TableStyle + '">

        <thead>

            <tr>

                <th style="' + @ThStyle + '">Database</th>

                <th style="' + @ThStyle + '">Recovery Model</th>

                <th style="' + @ThStyle + '">State</th>

                <th style="' + @ThStyle + ' text-align: center;">Status</th>

            </tr>

        </thead>

        <tbody>';


    SELECT @DbHTML = @DbHTML + 

        '<tr style="' + CASE WHEN state_desc <> 'ONLINE' THEN 'background-color: #fef2f2; border-bottom: 1px solid #fca5a5;' ELSE '' END + '">

            <td style="' + @TdStyle + ' font-weight: 600;">' + name + '</td>

            <td style="' + @TdStyle + '">' + recovery_model_desc + '</td>

            <td style="' + @TdStyle + ' font-weight: ' + CASE WHEN state_desc <> 'ONLINE' THEN '700; color: #dc2626;' ELSE '400;' END + '">' + state_desc + '</td>

            <td style="' + @TdStyle + ' text-align: center;"><span style="' + 

                CASE WHEN state_desc = 'ONLINE' THEN @BadgeGreen ELSE @BadgeRed END + '">' +

                CASE WHEN state_desc = 'ONLINE' THEN 'Relax' ELSE 'CheckNow' END + '</span></td>

        </tr>'

    FROM sys.databases

    WHERE name NOT IN ('tempdb')

    ORDER BY name;


    SET @DbHTML = @DbHTML + '</tbody></table>';


    -- 3. Database Backup Details

    DECLARE @BackupHTML NVARCHAR(MAX) = N'

    <div style="font-size: 15px; font-weight: 700; color: #0f172a; border-left: 4px solid #0284c7; padding-left: 8px; margin-bottom: 10px;">3. Database Backup Details</div>

    <table style="' + @TableStyle + '">

        <thead>

            <tr>

                <th style="' + @ThStyle + '">Database</th>

                <th style="' + @ThStyle + '">Last Full</th>

                <th style="' + @ThStyle + ' text-align: right;">Full Age</th>

                <th style="' + @ThStyle + '">Last Diff</th>

                <th style="' + @ThStyle + ' text-align: right;">Diff Age</th>

                <th style="' + @ThStyle + '">Last Log</th>

                <th style="' + @ThStyle + ' text-align: right;">Log Age</th>

                <th style="' + @ThStyle + ' text-align: center;">Status</th>

            </tr>

        </thead>

        <tbody>';


    SELECT @BackupHTML = @BackupHTML + 

        '<tr style="' + CASE WHEN FullAgeDays > 7 OR FullAgeDays IS NULL OR DiffAgeHours > 24 OR (recovery_model_desc <> 'SIMPLE' AND (LogAgeMinutes > 60 OR LogAgeMinutes IS NULL)) THEN 'background-color: #fef2f2; border-bottom: 1px solid #fca5a5;' ELSE '' END + '">

            <td style="' + @TdStyle + ' font-weight: 600;">' + DBName + '</td>

            <td style="' + @TdStyle + '">' + ISNULL(CONVERT(VARCHAR(16), LastFull, 120), '<span style="color: #94a3b8;">Never</span>') + '</td>

            <td style="' + @TdRightStyle + '">' + ISNULL(CAST(FullAgeDays AS VARCHAR(10)) + ' Days', 'N/A') + '</td>

            <td style="' + @TdStyle + '">' + ISNULL(CONVERT(VARCHAR(16), LastDiff, 120), '<span style="color: #94a3b8;">Never</span>') + '</td>

            <td style="' + @TdRightStyle + '">' + ISNULL(CAST(DiffAgeHours AS VARCHAR(10)) + ' Hrs', 'N/A') + '</td>

            <td style="' + @TdStyle + '">' + ISNULL(CONVERT(VARCHAR(16), LastLog, 120), '<span style="color: #94a3b8;">Never</span>') + '</td>

            <td style="' + @TdRightStyle + '">' + 

                CASE 

                    WHEN recovery_model_desc = 'SIMPLE' THEN 'N/A'

                    WHEN LogAgeMinutes IS NULL THEN 'N/A'

                    WHEN LogAgeMinutes < 60 THEN CAST(LogAgeMinutes AS VARCHAR(10)) + ' Min'

                    ELSE CAST(LogAgeMinutes / 60 AS VARCHAR(10)) + ' Hrs'

                END + '</td>

            <td style="' + @TdStyle + ' text-align: center;"><span style="' + 

                CASE 

                    WHEN FullAgeDays > 7 OR FullAgeDays IS NULL THEN @BadgeRed

                    WHEN DiffAgeHours > 24 THEN @BadgeRed

                    WHEN recovery_model_desc <> 'SIMPLE' AND (LogAgeMinutes > 60 OR LogAgeMinutes IS NULL) THEN @BadgeRed

                    ELSE @BadgeGreen

                END + '">' +

                CASE 

                    WHEN FullAgeDays > 7 OR FullAgeDays IS NULL THEN 'CheckNow'

                    WHEN DiffAgeHours > 24 THEN 'CheckNow'

                    WHEN recovery_model_desc <> 'SIMPLE' AND (LogAgeMinutes > 60 OR LogAgeMinutes IS NULL) THEN 'CheckNow'

                    ELSE 'Relax'

                END + '</span></td>

        </tr>'

    FROM #BackupInfo

    ORDER BY DBName;


    SET @BackupHTML = @BackupHTML + '</tbody></table>';


    -- 4. Disk Details (With Dynamic Visual Bar Chart)

    DECLARE @DiskHTML NVARCHAR(MAX) = N'

    <div style="font-size: 15px; font-weight: 700; color: #0f172a; border-left: 4px solid #0284c7; padding-left: 8px; margin-bottom: 10px;">4. Disk Details</div>

    <table style="' + @TableStyle + '">

        <thead>

            <tr>

                <th style="' + @ThStyle + '">Drive</th>

                <th style="' + @ThStyle + ' text-align: right;">Total (GB)</th>

                <th style="' + @ThStyle + ' text-align: right;">Used (GB)</th>

                <th style="' + @ThStyle + ' text-align: right;">Free (GB)</th>

                <th style="' + @ThStyle + ' width: 180px;">Free % Visual Bar</th>

                <th style="' + @ThStyle + ' text-align: center;">Status</th>

            </tr>

        </thead>

        <tbody>';


    SELECT @DiskHTML = @DiskHTML + 

        '<tr style="' + CASE WHEN FreePct < 15 THEN 'background-color: #fef2f2; border-bottom: 1px solid #fca5a5;' WHEN FreePct < 25 THEN 'background-color: #fffbeb; border-bottom: 1px solid #fde68a;' ELSE '' END + '">

            <td style="' + @TdStyle + ' font-weight: 600;">' + VolumeName + '</td>

            <td style="' + @TdRightStyle + '">' + CAST(TotalGB AS VARCHAR(20)) + '</td>

            <td style="' + @TdRightStyle + '">' + CAST(UsedGB AS VARCHAR(20)) + '</td>

            <td style="' + @TdRightStyle + ' font-weight: ' + CASE WHEN FreePct < 25 THEN '700;' ELSE '400;' END + '">' + CAST(FreeGB AS VARCHAR(20)) + '</td>

            <td style="' + @TdStyle + '">

                <div style="display: flex; align-items: center; gap: 8px;">

                    <div style="flex: 1; background-color: #e2e8f0; border-radius: 4px; height: 10px; overflow: hidden;">

                        <div style="width: ' + CAST(FreePct AS VARCHAR(10)) + '%; background-color: ' + 

                            CASE WHEN FreePct < 15 THEN '#ef4444' WHEN FreePct < 25 THEN '#f59e0b' ELSE '#10b981' END + 

                            '; height: 100%;"></div>

                    </div>

                    <span style="font-weight: 700; min-width: 35px; color: ' + 

                        CASE WHEN FreePct < 15 THEN '#dc2626' WHEN FreePct < 25 THEN '#b45309' ELSE '#059669' END + 

                        ';">' + CAST(FreePct AS VARCHAR(10)) + '%</span>

                </div>

            </td>

            <td style="' + @TdStyle + ' text-align: center;"><span style="' + 

                CASE WHEN FreePct < 15 THEN @BadgeRed WHEN FreePct < 25 THEN @BadgeYellow ELSE @BadgeGreen END + '">' +

                CASE WHEN FreePct < 25 THEN 'CheckNow' ELSE 'Relax' END + '</span></td>

        </tr>'

    FROM #DiskInfo

    ORDER BY VolumeName;


    SET @DiskHTML = @DiskHTML + '</tbody></table>';


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

    -- 8. ASSEMBLE HTML DOCUMENT AND SEND EMAIL

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

    DECLARE @FullHTMLBody NVARCHAR(MAX);


    SET @FullHTMLBody = N'<!DOCTYPE html><html><body style="font-family: Segoe UI, Helvetica, Arial, sans-serif; background-color: #f8fafc; margin: 0; padding: 20px;">' +

                        N'<div style="max-width: 1000px; margin: auto; background-color: #ffffff; border-radius: 12px; border: 1px solid #e2e8f0; padding: 25px; box-shadow: 0 4px 6px -1px rgba(0, 0, 0, 0.05);">' +

                        N'<div style="text-align: center; border-bottom: 2px solid #f1f5f9; padding-bottom: 15px; margin-bottom: 20px;">' +

                        N'<div style="font-size: 24px; color: #0f172a; font-weight: 800; letter-spacing: -0.5px;">SQL Server Health Check Report</div>' +

                        N'<div style="font-size: 13px; color: #64748b; margin-top: 4px;">Generated on: <b>' + CONVERT(VARCHAR(19), @GetDate, 120) + N'</b> &nbsp;|&nbsp; SQL Instance: <b style="color: #0f172a;">' + @InstanceName + N'</b></div>' +

                        N'</div>' +

                        @SummaryHTML +

                        @ServerHTML +

                        @DbHTML +

                        @BackupHTML +

                        @DiskHTML +

                        N'<div style="text-align: center; color: #94a3b8; font-size: 11px; margin-top: 20px; border-top: 1px solid #f1f5f9; padding-top: 15px;">This is an automated system health notification. Please do not reply directly to this email.</div>' +

                        N'</div></body></html>';


    EXEC msdb.dbo.sp_send_dbmail

        @profile_name = @MailProfile,

        @recipients   = @Recipients,

        @subject      = @Subject,

        @body         = @FullHTMLBody,

        @body_format  = 'HTML';


    -- Clean Up Temp Tables

    IF OBJECT_ID('tempdb..#BackupInfo') IS NOT NULL DROP TABLE #BackupInfo;

    IF OBJECT_ID('tempdb..#DiskInfo') IS NOT NULL DROP TABLE #DiskInfo;

END;

GO


*/


Read more »
Subscribe to: Posts (Atom)

Author Name

Contact Form

Name

Email *

Message *

Powered by Blogger.