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 Ring Buffer — always active since SQL Server 2008, no configuration required. 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: The Ring Buffer holds 4 MB by default. At high deadlock frequency export early; older events are silently discarded once the buffer wraps.

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.
-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.

Execution Flow

START dbatools installed? NO throw: dbatools not found YES -SqlInstance provided? NO → Default: $env:COMPUTERNAME Invoke-DbaQuery — Ring Buffer (System Health XE) sys.dm_xe_session_targets WHERE name='system_health' AND target='ring_buffer' xml_deadlock_report events · filtered by StartTime / EndTime · ORDER BY EventTime DESC 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 · ordered newest first (from Ring Buffer ORDER BY) 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
⬆ Index
All Commands