Provides information about deadlocks. Use deadlock pipe max messages to tune the maximum number of messages returned.
monDeadLock is an historical monitoring table. See “Stateful historical monitoring tables”.
Use sp_monitor 'deadlock' to check current deadlock options. The deadlock parameter provides a number of reports based on monDeadLock, which are useful for analyzing the history of server deadlocks.
Enable the enable monitoring, deadlock
pipe active, and deadlock max pipe messages configuration
parameters for this monitoring table to collect data.
Name |
Datatype |
Attributes |
Description |
|---|---|---|---|
DeadLockID |
int |
Unique identifier for the deadlock |
|
VictimKPID |
int |
Kernel process ID (kpid) of the victim process for the deadlock |
|
ResolveTime |
datetime |
Time when the deadlock was resolved |
|
ObjectDBID |
int |
Unique database identifier for database where the object resides |
|
PageNumber |
int |
Page number requested for the lock, if applicable |
|
RowNumber |
int |
Row number requested for the lock, if applicable |
|
HeldFamilyId |
smallint |
spid of the parent process holding the lock |
|
HeldSPID |
smallint |
spid of process holding the lock |
|
HeldKPID |
int |
kpid of process holding the lock |
|
HeldProcDBID |
int |
Unique identifier for the database where the stored procedure that caused the lock to be held resides, if applicable |
|
HeldProcedureID |
int |
Unique object identifier for the stored procedure that caused the lock to be held, if applicable |
|
HeldBatchID |
int |
Identifier of the SQL batch executed by the process holding the lock when the deadlock occurred |
|
HeldContextID |
int |
Unique context identifier for the process holding the lock when it was blocked by another process (not when it acquired the lock) |
|
HeldLineNumber |
int |
Line number within the batch of the statement being executed by the process holding the lock when it was blocked by another process (not when it acquired the lock) |
|
WaitFamilyId |
smallint |
spid of the parent process waiting for the lock |
|
WaitSPID |
smallint |
spid of the process waiting for the lock |
|
WaitKPID |
int |
kpid of the process waiting for the lock |
|
WaitTime |
int |
Amount of time, in milliseconds, that the waiting process was blocked before the deadlock was resolved |
|
ObjectName |
varchar(30) |
Null |
Name of the object |
HeldUserName |
varchar(30) |
Null |
Name of the user for whom the lock is being held |
HeldApplName |
varchar(30) |
Null |
Name of the application holding the lock |
HeldTranName |
varchar(255) |
Null |
Name of the transaction in which the lock was acquired |
HeldLockType |
varchar(20) |
Null |
Type of lock being held |
HeldCommand |
varchar(30) |
Category of process or command that the process was executing when it was blocked |
|
WaitUserName |
varchar(30) |
Null |
Name of the user for whom the lock is being requested |
WaitLockType |
varchar(20) |
Null |
Type of lock requested |
