Hello Friends,
This PowerShell script I have written checks SQL Server's Dedicated Admin Connection (DAC) usage and diagnose Change Tracking (CT) health on a given SQL instance. It's designed to log the results in an HTML report format.:
PowerShell function SQLDACChangeTracking in more detail so you have a full understanding of what it does, section by section.
🔍 Section-by-Section Breakdown
🔹 1. Function and Header Setup
Function SQLDACChangeTracking {
$Server = $strSQLDACChangeTracking
$Server = $Server.toupper()
Initializes the function.
Takes the server name from a variable $strSQLDACChangeTracking and converts it to uppercase.
🔹 2. Report Header HTML Setup
$rptheader = @"
<table width='100%'><tbody>
<tr bgcolor=#01A982> <td align='center'> $Server SQL DAC ChangeTracking Report</td>
</table>
...
"@
Add-Content "$Report" $rptheader
Sets up the top portion of an HTML report.
Adds a title and column headers like Name and Status.
$Report is assumed to be a path to an output HTML file.
🔹 3. First SQL Query – Check DAC Session
$sqlquery1 = @"
USE databasename;
SELECT ...
FROM sys.endpoints ep
JOIN sys.dm_exec_sessions es ON ep.endpoint_id = es.endpoint_id
WHERE ep.name = 'Dedicated Admin Connection'
"@
This SQL checks whether any session is using the Dedicated Admin Connection (DAC).
DAC is used for emergency administrative access to SQL Server.
$sqlresults = Invoke-Sqlcmd ...
Executes the above query.
If any DAC session is active, it will return details like:
host_name (machine using DAC)
session_id
login_time
status
🔹 4. If DAC Query Returns Results
If $sqlresults is not null, it extracts fields and formats them into a green-colored HTML table indicating an active DAC session. However, there’s a mismatch here:
<td width='90%' align='left' >DAC session is not available</td>
This line implies DAC isn't in use, but it's inside the block that runs when DAC is in use. That could be misleading and might need correcting.
🔹 5. If DAC Query Fails (Fallback to DAC Mode)
If the first query fails (returns $null), it falls back to running a stored procedure:
USE databasename; EXEC spDiagChangeTracking
Runs under DAC connection using:
-DedicatedAdministratorConnection
Captures exceptions using:
-ErrorAction SilentlyContinue -ErrorVariable abc
If the execution succeeds, it reads values from $sqlresults.Tables[1], such as:
CT_Days_Old: how old the last tracked change is.
Auto_Cleanup_Time_(Local) and Cleanup_Time_(Local): cleanup timestamps.
Retention_Period and Retention_Unit: CT retention configuration.
🔹 6. Data Evaluation and HTML Reporting
Based on thresholds (like CT_Days_Old > 5), the script highlights rows using color coding:
Red: bad or needs attention
Green: healthy
Orange: warning or needs monitoring
Each piece of data is wrapped in an HTML table and written to the report using Add-Content.
🔹 7. Error Logging
If any exception occurs during DAC mode query:
$abc[0].Exception
Add-Content $logfile -Value "$abc : Checking SQL Server Services Details"
Logs the error details.
Displays an error in the HTML report using Error Exception.
✅ Use Cases for This Script
Health monitoring of SQL Server's Change Tracking (CT) feature.
Auditing unexpected use of the DAC connection.
Preventive maintenance by alerting on data retention age, cleanup times.
Daily automated reporting on CT status via HTML output.
