· Uwe Janke
Commands / Get-sqmDeadlockReport

Get-sqmDeadlockReport

Deadlock Analysis sqmSQLTool v1.8.2+ · Get 🔓 VIEW SERVER STATE required
Reads and parses deadlock events from the System Health Extended Event session, always active since SQL Server 2008, no configuration required. Since v1.9.169.0 it reads both targets of the session by default: the in-memory ring buffer and the system_health*.xel files, which usually hold weeks instead of hours. Deadlocks found in both are returned once. For each deadlock the function returns the victim session (SPID, login, host, program, statement), all involved processes with their lock modes and statements, the affected resources (tables, indexes), and the raw deadlock graph XML. XDL files can optionally be written per deadlock for direct SSMS import (double-click to open as deadlock graph).

Note: XEvents store UTC. -StartTime/-EndTime are local times and are converted (up to v1.9.168.0 they were compared unconverted, so the most recent one to two hours could be missing). Timestamp is local time, TimestampUtc the original value. In a test on SQL Server 2025 a fresh deadlock was only in the .xel file, not in the ring buffer.

Parameters

ParameterTypeRequiredDefaultNotes
-SqlInstancestringOptional$env:COMPUTERNAMETarget SQL Server instance.
-SqlCredentialPSCredentialOptional, SQL or Windows credential.
-StartTimedatetimeOptional(Get-Date).AddHours(-24)Return deadlocks from this point in time.
-EndTimedatetimeOptionalGet-DateReturn deadlocks up to this point in time.
-SourcestringOptionalBothBoth, RingBuffer or EventFile (SQL Server 2012+). With Both an unreadable .xel falls back to the ring buffer with a warning.
-MaxDeadlocksintOptional100Maximum number of deadlock records to return (newest first).
-OutputPathstringOptional, When provided, writes one .xdl file per deadlock. File name: Deadlock_Instance_timestamp_N.xdl. Directory is created automatically.
-EnableExceptionswitchSwitch$falseThrow on error instead of Write-Error.
-NoOpenswitchSwitch$falseDo not automatically open the HTML summary report after creation (only relevant with -OutputPath).

Execution Flow

START dbatools installed? NO throw: dbatools not found YES -SqlInstance provided? NO → Default: $env:COMPUTERNAME Ring buffer + system_health*.xel (-Source) .xel path taken from the event_file target · duplicates removed xml_deadlock_report · StartTime / EndTime converted to UTC · newest first For each deadlock event (up to MaxDeadlocks) · parse errors → Write-Warning + continue [xml] deadlock graph → victim-list · process-list · resource-list Victim = process with id matching victim-list.victimProcess.id Per process: LoginName · HostName · Statement (inputbuf) · LockMode · WaitResource -OutputPath provided? YES Write .xdl file per deadlock Deadlock_Instance_ts_N.xdl (SSMS-ready) NO Add PSCustomObject to results list Timestamp · VictimSpid / Login / Host / Program / Statement · Processes[] · Resources[] · DeadlockGraphXml Return List[PSCustomObject] One entry per deadlock · newest first · Timestamp local, TimestampUtc Empty list if no deadlocks found in the given time range Error handling -EnableException: throw · else: Write-Error + return $null DONE

Return Object (per deadlock)

PropertyTypeDescription
.Timestampdatetime2Time the deadlock was recorded by the XE session.
.DeadlockIndexintSequential index within this call (1-based, newest first).
.VictimSpidstringSPID of the session chosen as victim.
.VictimLogin / .VictimHost / .VictimProgramstringIdentity of the victim session.
.VictimStatementstringSQL statement the victim was executing (from inputbuf).
.VictimWaitTimestringWait time of the victim at deadlock time.
.ProcessCountintTotal number of processes involved in the deadlock.
.ProcessesListAll involved processes: IsVictim, SpId, LoginName, HostName, Statement, LockMode, WaitResource, LogUsed.
.ResourcesListContested resources: ResourceType, ObjectName, IndexName, LockMode, AssociatedProcesses.
.DeadlockGraphXmlstringRaw deadlock XML node, save as .xdl for SSMS visualization.

Examples

Deadlocks in the last 24 hours (default)
Get-sqmDeadlockReport
Deadlocks of the last 7 days on a named instance
Get-sqmDeadlockReport -SqlInstance "SQL01" -StartTime (Get-Date).AddDays(-7)
Export deadlock graphs as XDL files for SSMS
Get-sqmDeadlockReport -SqlInstance "SQL01" -OutputPath "C:\System\WinSrvLog\MSSQL\Deadlocks"
Show victim and statement for each deadlock in the last hour
Get-sqmDeadlockReport -StartTime (Get-Date).AddHours(-1) |
    Select-Object Timestamp, VictimLogin, VictimStatement, ProcessCount