Get-sqmDeadlockReport
Deadlock Analysis
sqmSQLTool v1.8.2+ · Get
🔓 VIEW SERVER STATE required
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
| Parameter | Type | Required | Default | Notes |
|---|---|---|---|---|
| -SqlInstance | string | Optional | $env:COMPUTERNAME | Target SQL Server instance. |
| -SqlCredential | PSCredential | Optional | , | SQL or Windows credential. |
| -StartTime | datetime | Optional | (Get-Date).AddHours(-24) | Return deadlocks from this point in time. |
| -EndTime | datetime | Optional | Get-Date | Return deadlocks up to this point in time. |
| -Source | string | Optional | Both | Both, RingBuffer or EventFile (SQL Server 2012+). With Both an unreadable .xel falls back to the ring buffer with a warning. |
| -MaxDeadlocks | int | Optional | 100 | Maximum number of deadlock records to return (newest first). |
| -OutputPath | string | Optional | , | When provided, writes one .xdl file per deadlock. File name: Deadlock_Instance_timestamp_N.xdl. Directory is created automatically. |
| -EnableException | switch | Switch | $false | Throw on error instead of Write-Error. |
| -NoOpen | switch | Switch | $false | Do not automatically open the HTML summary report after creation (only relevant with -OutputPath). |
Execution Flow
Return Object (per deadlock)
| Property | Type | Description |
|---|---|---|
| .Timestamp | datetime2 | Time the deadlock was recorded by the XE session. |
| .DeadlockIndex | int | Sequential index within this call (1-based, newest first). |
| .VictimSpid | string | SPID of the session chosen as victim. |
| .VictimLogin / .VictimHost / .VictimProgram | string | Identity of the victim session. |
| .VictimStatement | string | SQL statement the victim was executing (from inputbuf). |
| .VictimWaitTime | string | Wait time of the victim at deadlock time. |
| .ProcessCount | int | Total number of processes involved in the deadlock. |
| .Processes | List | All involved processes: IsVictim, SpId, LoginName, HostName, Statement, LockMode, WaitResource, LogUsed. |
| .Resources | List | Contested resources: ResourceType, ObjectName, IndexName, LockMode, AssociatedProcesses. |
| .DeadlockGraphXml | string | Raw 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