First, What This Article Is Not About
"Log" is overloaded in SQL Server, and it's worth clearing up front: the transaction log (the .ldf file) is not a diagnostic log in the sense this article covers. It is the write-ahead durability mechanism every database depends on for crash recovery and, in an AlwaysOn AG, for replication itself. You don't "read" it for troubleshooting the way you'd read the error log, its contents are only meaningfully exposed through the undocumented fn_dblog / fn_dump_dblog functions, used almost exclusively for forensic recovery, not routine operations.
What does matter operationally about the transaction log is its physical health, specifically VLF (virtual log file) count: a log that has grown and shrunk repeatedly accumulates hundreds or thousands of VLFs, which measurably slows startup, recovery, and backups. That's a health-check concern, not a log-reading one, and it's exactly what Get-sqmDatabaseHealth reports on alongside the actual diagnostic logs below.
Quick Reference
| Log | Captures | Lives in |
|---|---|---|
| Error log | Startup/shutdown, logins, backups, internal errors | Text files on disk |
| Agent log | The Agent service itself | Text files on disk |
| Agent job history | Per-job, per-step run outcomes | msdb |
| system_health XEvents | Deadlocks, waits, non-yielding schedulers | Ring buffer (+ file, version-dependent) |
| Blocked-process XEvents | Blocking chains over the threshold | Ring buffer/file, needs a dedicated session |
| Default trace | Autogrowth, some DDL/security events | Rolling .trc files on disk |
| Windows Application log | Service events, AG role changes | Windows Event Log |
| Windows Cluster log | WSFC quorum/lease/heartbeat detail | Generated on demand |
| Setup log | Install/patch outcome and failures | Text files on disk |
| Backup/restore history | Every backup and restore ever run | msdb |
The SQL Server Error Log
The engine's own log: service startup and shutdown, successful and failed logins (if audit level is configured to record them), backup/restore completions, and internal errors in the Error: n, Severity: n, State: n. format. It rotates on every service restart or on sp_cycle_errorlog, keeping a configurable number of archives (6 by default) alongside the current file.
Native query via xp_readerrorlog or, better, sp_readerrorlog, or dbatools' Get-DbaErrorLog.
Get-sqmErrorLog wraps this with ready-made filters for the events people actually search for, -FailedLogins, -SuccessfulLogins, -Backups, -Restores, -Errors, and more, with language-neutral login detection so it works correctly on non-English instances too. Get-Help Get-sqmErrorLog -Full for the complete parameter list.
The SQL Server Agent Log
Easy to confuse with job history, but a different thing entirely: SQLAGENT.OUT covers the Agent service, its own startup, scheduler activity, mail/alert notification failures, not what any individual job did. It rotates on Agent restart or sp_cycle_agent_errorlog, keeping 9 archives by default.
Native query via xp_readerrorlog 0, 2 (the second parameter selects the Agent log instead of the error log), or dbatools' Get-DbaAgentLog.
Get-sqmAgentJobHistory below covers what people actually mean when they say "check the Agent log" (a job's own run history). Use dbatools' Get-DbaAgentLog directly for genuine Agent-service-level issues.
SQL Server Agent Job History
Not a text log at all: every job step's outcome, duration, and message lands in msdb.dbo.sysjobhistory. Retention is capped, not time-based, by default 1000 rows total and 100 rows per job (sp_set_sqlagent_properties), so a chatty job can silently push an infrequent job's older history out.
Get-sqmAgentJobHistory returns the execution history of all or selected jobs, filterable by job name, status, and time range, last 7 days by default.
Extended Events: system_health
Always running since SQL Server 2008, no setup required, and not something you can meaningfully turn off. Captures deadlock graphs, wait statistics beyond a threshold, non-yielding scheduler events, and memory-pressure ring buffer entries, into an in-memory ring buffer (plus a small on-disk file target on newer versions), oldest events overwritten first.
Native query via sys.fn_xe_file_target_read_file against the ring buffer target, or dbatools' Get-DbaXESession / Read-DbaXEFile.
Get-sqmDeadlockReport reads system_health specifically for deadlock graphs, parses them, and returns victim, all involved processes, statements, and locked resources per deadlock. Invoke-sqmExtendedEvents -Diagnose goes further, aggregating events across sessions for top waits and blocking-chain patterns.
Extended Events: Blocked-Process Reports
Blocking is only ever visible historically if something captured it at the time; querying sys.dm_exec_requests only shows blocking happening right now. blocked_process_report events fire once 'blocked process threshold (s)' is set above 0, but system_health does not reliably include that event class on every SQL Server version, verified directly: on a default installation the event class can simply be absent from system_health's own definition. A dedicated session is the only guaranteed capture path.
Register-sqmBlockedProcessMonitor creates and starts a dedicated Extended Events session specifically to guarantee this capture exists, independent of what system_health happens to contain. Get-sqmBlockingHistory reads past incidents from it (and from system_health, if it happens to have them too); Get-sqmBlockingReport is the real-time counterpart for "what's blocking right now."
The Default Trace
The predecessor to Extended Events, still enabled by default (sp_configure 'default trace enabled') through SQL Server 2022, writing to a small set of rolling .trc files. It captures a fixed, non-configurable set of event classes: autogrowth events, some DDL changes, some security/permission changes, and a handful of others, but the set is shallow and was never meant as a compliance audit trail.
Native query via sys.fn_trace_gettable(@path, DEFAULT), filtering on event class (92/93 = data/log file autogrow).
Get-sqmDatabaseHealth reads the default trace's autogrowth events (event classes 92/93) directly, over a configurable lookback window, as one input into its overall per-database health check.
The Windows Application Event Log
SQL Server writes selected events here in addition to its own error log, most usefully anything a monitoring tool watching Windows Event Log (rather than polling SQL Server directly) needs to see, and events tied to state a specific EventID makes reliably parseable. The clearest example: every AlwaysOn AG role change is logged as event ID 1480, "The %ls role of availability group '%s' has been successfully changed to '%ls'", structured and language-independent since SQL Server 2012.
Native query via PowerShell's own Get-WinEvent (not a dbatools or SQL Server feature, this is plain Windows).
Get-sqmAlwaysOnFailoverHistory reads event ID 1480 from the Application log specifically to build a timeline of every AG role transition, exactly the "why did it fail over, and when" question the error log alone can't answer cleanly.
The Windows Failover Cluster Log
A different subsystem entirely: WSFC, not SQL Server, and it's the only place the cluster-level side of a failover shows up, quorum evaluation, lease timeouts, heartbeat loss between nodes, resource state transitions. This matters because a primary can go dark with the Application log showing nothing useful yet, the lease expiring is the SQL Server instance protecting itself before WSFC has even acted.
Not a standing file, generated on demand via Get-ClusterLog, which compiles a detailed diagnostic text log from the live cluster service.
Get-sqmClusterInfo covers cluster topology (nodes, roles, IP resources), not log content; for the log itself, run Get-ClusterLog directly on a cluster node. See How AlwaysOn Really Works for how the lease timeout and quorum mechanics behind this actually work.
The SQL Server Setup Log
Every install, upgrade, and CU/patch application writes a detailed log under %ProgramFiles%\Microsoft SQL Server\<version>\Setup Bootstrap\Log\<timestamp>\, with Summary.txt as the entry point and per-component detail logs alongside it. This is the first and often only place that explains why a patch actually failed, permission errors, pending reboots, a locked file, when the patch's own on-screen error message just says "failed."
Invoke-sqmPatchAnalysis answers an adjacent but different question, whether the currently installed build is up to date against known CU/SP releases, not why a specific installation attempt failed.
Backup and Restore History
Not a flat-file log, but arguably the most audit-relevant one on this list: every backup ever taken is recorded in msdb.dbo.backupset / backupmediafamily, and every restore in msdb.dbo.restorehistory, both effectively permanent unless someone explicitly purges history. This is the authoritative source for "when was this database last backed up" and "when was this database last restored," questions that matter for both operational confidence and audit evidence.
Get-sqmDatabaseRestoreHistory lists every database's last restore explicitly, including databases that have never been restored (reported as such, not silently omitted). Test-sqmBackupIntegrity goes further and validates the backups themselves, not just the fact that history says one happened.
Which Log Answers Which Question
| Symptom | Check |
|---|---|
| A login just failed | Error log — Get-sqmErrorLog -FailedLogins |
| A job didn't run, or failed | Agent job history — Get-sqmAgentJobHistory |
| A query is stuck right now | Real-time blocking — Get-sqmBlockingReport |
| A query was stuck earlier, nobody looked in time | Blocked-process XEvents — Get-sqmBlockingHistory |
| Two transactions deadlocked | system_health — Get-sqmDeadlockReport |
| A data/log file grew unexpectedly | Default trace — Get-sqmDatabaseHealth |
| An AG failed over | Windows Application log — Get-sqmAlwaysOnFailoverHistory, then the cluster log for the WSFC-side detail |
| Setup or a CU install failed | Setup Bootstrap log (manual) |
| Is my backup chain intact / when was this restored | msdb history — Get-sqmDatabaseRestoreHistory, Test-sqmBackupIntegrity |