powershell-scripts/Database-HealthCheck.ps1 at main · AnonGoldup/powershell-scripts · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
<#
.SYNOPSIS
    Comprehensive SQL Server health check with HTML report output.

.DESCRIPTION
    Performs a full health assessment of a SQL Server instance including
    database status, disk space, performance counters, job status, and
    backup status. Generates an HTML report for review.

.PARAMETER ServerInstance
    SQL Server instance name.

.PARAMETER ReportPath
    Path to write the HTML health check report.

.EXAMPLE
    .\Database-HealthCheck.ps1 -ServerInstance "SQLSERVER01" -ReportPath "D:\Reports"
#>

[CmdletBinding()]
param(
    [Parameter(Mandatory)]
    [string]$ServerInstance,

    [string]$ReportPath = "D:\DBA\Reports"
)

$ErrorActionPreference = "Stop"
$timestamp = Get-Date -Format "yyyyMMdd_HHmmss"
$reportFile = Join-Path $ReportPath "HealthCheck_${ServerInstance}_$timestamp.html"

if (-not (Test-Path $ReportPath)) {
    New-Item -ItemType Directory -Path $ReportPath -Force | Out-Null
}

$reportData = @{}

Write-Host "Running health check on $ServerInstance..."

# 1. Server Information
$serverInfo = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query @"
    SELECT
        @@SERVERNAME AS ServerName,
        @@VERSION AS Version,
        SERVERPROPERTY('ProductVersion') AS ProductVersion,
        SERVERPROPERTY('Edition') AS Edition,
        (SELECT COUNT(*) FROM sys.databases WHERE database_id > 4) AS UserDatabases,
        (SELECT cntr_value FROM sys.dm_os_performance_counters
         WHERE counter_name = 'Page life expectancy'
         AND object_name LIKE '%Buffer Manager%') AS PageLifeExpectancy
"@
$reportData["ServerInfo"] = $serverInfo

# 2. Database Status
$dbStatus = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query @"
    SELECT
        name,
        state_desc AS Status,
        recovery_model_desc AS RecoveryModel,
        CAST(SUM(size) * 8.0 / 1024 AS DECIMAL(10,2)) AS SizeMB
    FROM sys.databases d
    INNER JOIN sys.master_files mf ON d.database_id = mf.database_id
    WHERE d.database_id > 4
    GROUP BY name, state_desc, recovery_model_desc
    ORDER BY name
"@
$reportData["Databases"] = $dbStatus

# 3. Disk Space
$diskSpace = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query @"
    SELECT DISTINCT
        vs.volume_mount_point AS Drive,
        CAST(vs.total_bytes / 1073741824.0 AS DECIMAL(10,2)) AS TotalGB,
        CAST(vs.available_bytes / 1073741824.0 AS DECIMAL(10,2)) AS FreeGB,
        CAST((vs.total_bytes - vs.available_bytes) * 100.0 / vs.total_bytes AS DECIMAL(5,1)) AS PctUsed
    FROM sys.master_files mf
    CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs
"@
$reportData["DiskSpace"] = $diskSpace

# 4. Failed Jobs (last 24 hours)
$failedJobs = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query @"
    SELECT
        j.name AS JobName,
        h.step_name AS StepName,
        msdb.dbo.agent_datetime(h.run_date, h.run_time) AS FailureTime,
        h.message AS ErrorMessage
    FROM msdb.dbo.sysjobhistory h
    INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
    WHERE h.run_status = 0
      AND msdb.dbo.agent_datetime(h.run_date, h.run_time) >= DATEADD(HOUR, -24, GETDATE())
    ORDER BY h.run_date DESC, h.run_time DESC
"@
$reportData["FailedJobs"] = $failedJobs

# 5. Top Wait Stats
$waitStats = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query @"
    SELECT TOP 5
        wait_type,
        waiting_tasks_count,
        wait_time_ms / 1000 AS wait_sec,
        CAST(100.0 * wait_time_ms / SUM(wait_time_ms) OVER() AS DECIMAL(5,1)) AS pct
    FROM sys.dm_os_wait_stats
    WHERE wait_type NOT IN (
        'SLEEP_TASK','BROKER_TASK_STOP','CLR_SEMAPHORE','LAZYWRITER_SLEEP',
        'CHECKPOINT_QUEUE','WAITFOR','XE_DISPATCHER_WAIT',
        'FT_IFTS_SCHEDULER_IDLE_WAIT','DISPATCHER_QUEUE_SEMAPHORE',
        'REQUEST_FOR_DEADLOCK_SEARCH','SQLTRACE_BUFFER_FLUSH','BROKER_EVENTHANDLER'
    )
    AND waiting_tasks_count > 0
    ORDER BY wait_time_ms DESC
"@
$reportData["WaitStats"] = $waitStats

# Generate HTML Report
$html = @"
<!DOCTYPE html>
<html>
<head>
    <title>Health Check - $ServerInstance - $(Get-Date -Format 'yyyy-MM-dd')</title>
    <style>
        body { font-family: Segoe UI, sans-serif; margin: 20px; background: #f5f5f5; }
        h1 { color: #333; border-bottom: 2px solid #0078d4; padding-bottom: 10px; }
        h2 { color: #0078d4; margin-top: 30px; }
        table { border-collapse: collapse; width: 100%; margin-bottom: 20px; background: white; }
        th { background: #0078d4; color: white; padding: 8px 12px; text-align: left; }
        td { padding: 8px 12px; border-bottom: 1px solid #ddd; }
        tr:hover { background: #f0f7ff; }
        .ok { color: green; font-weight: bold; }
        .warning { color: orange; font-weight: bold; }
        .critical { color: red; font-weight: bold; }
        .summary { background: white; padding: 15px; border-radius: 5px; margin-bottom: 20px; }
    </style>
</head>
<body>
    <h1>SQL Server Health Check</h1>
    <div class="summary">
        <strong>Server:</strong> $($serverInfo.ServerName)<br>
        <strong>Version:</strong> $($serverInfo.ProductVersion) ($($serverInfo.Edition))<br>
        <strong>Databases:</strong> $($serverInfo.UserDatabases)<br>
        <strong>Page Life Expectancy:</strong> $($serverInfo.PageLifeExpectancy)s
        $(if ([int]$serverInfo.PageLifeExpectancy -lt 300) {'<span class="warning">(Below 300s threshold)</span>'} else {'<span class="ok">(OK)</span>'})<br>
        <strong>Report Generated:</strong> $(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')
    </div>
"@

# Database Status Table
$html += "<h2>Database Status</h2><table><tr><th>Database</th><th>Status</th><th>Recovery Model</th><th>Size (MB)</th></tr>"
foreach ($db in $dbStatus) {
    $statusClass = if ($db.Status -eq "ONLINE") { "ok" } else { "critical" }
    $html += "<tr><td>$($db.name)</td><td class='$statusClass'>$($db.Status)</td><td>$($db.RecoveryModel)</td><td>$($db.SizeMB)</td></tr>"
}
$html += "</table>"

# Disk Space Table
$html += "<h2>Disk Space</h2><table><tr><th>Drive</th><th>Total (GB)</th><th>Free (GB)</th><th>Used %</th></tr>"
foreach ($disk in $diskSpace) {
    $diskClass = if ([double]$disk.PctUsed -gt 90) { "critical" } elseif ([double]$disk.PctUsed -gt 80) { "warning" } else { "ok" }
    $html += "<tr><td>$($disk.Drive)</td><td>$($disk.TotalGB)</td><td>$($disk.FreeGB)</td><td class='$diskClass'>$($disk.PctUsed)%</td></tr>"
}
$html += "</table>"

# Failed Jobs
$html += "<h2>Failed Jobs (Last 24 Hours)</h2>"
if ($failedJobs.Count -eq 0) {
    $html += "<p class='ok'>No failed jobs in the last 24 hours.</p>"
} else {
    $html += "<table><tr><th>Job</th><th>Step</th><th>Time</th><th>Error</th></tr>"
    foreach ($job in $failedJobs) {
        $html += "<tr><td>$($job.JobName)</td><td>$($job.StepName)</td><td>$($job.FailureTime)</td><td>$($job.ErrorMessage)</td></tr>"
    }
    $html += "</table>"
}

# Wait Stats
$html += "<h2>Top Wait Statistics</h2><table><tr><th>Wait Type</th><th>Count</th><th>Wait (sec)</th><th>% of Total</th></tr>"
foreach ($w in $waitStats) {
    $html += "<tr><td>$($w.wait_type)</td><td>$($w.waiting_tasks_count)</td><td>$($w.wait_sec)</td><td>$($w.pct)%</td></tr>"
}
$html += "</table>"

$html += "</body></html>"

$html | Out-File -FilePath $reportFile -Encoding UTF8
Write-Host "Health check report saved to: $reportFile"