Provides information about deadlocks. Use deadlock pipe max messages to tune the maximum number of messages returned.
monDeadLock is an historical monitoring table. See Performance and Tuning: 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 max messages, and deadlock pipe active configuration parameters for this monitoring table to collect data.
The columns for monDeadLock are:
Name |
Datatype |
Attributes |
Description |
|---|---|---|---|
DeadLockID |
int |
Unique identifier for the deadlock |
|
VictimKPID |
int |
Kernel process ID (kpid) of the victim process for the deadlock |
|
InstanceID |
int |
ID of an instance in a shared-disk cluster. |
|
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 |
HeldSourceCodeID |
varchar(30) |
For internal use only. |
|
WaitSourceCodeID |
varchar(30) |
For internal use only. |
|
HeldClientApplName |
varchar(30) |
Null |
Value for the clientapplname property set by the application holding the lock |
HeldClientName |
varchar(30) |
Null |
Value of the clientname property set by the application holding the lock |
HeldClientHostName |
varchar(30) |
Null |
Value for the clienthostname property set by the application holding the lock |
HeldHostName |
varchar(30) |
Null |
Name of the host machine on which the application that executed the query holding the lock is running |
HeldNumLocks |
int |
Number of locks currently held by holding spid |
|
HeldProcDBName |
varchar(30) |
Null |
Name of the database in which the stored procedure was executing the blocking process at the time the deadlock occurred, if applicable |
HeldProcedureName |
varchar(30) |
Null |
Name of the stored procedure the blocking process was executing at the time the deadlock occurred, if applicable |
HeldStmtNumber |
int |
Statement number in the SQL batch of the SQL statement holding the lock |
|
ObjectDBName |
varchar(30) |
Null |
Name of the database |
ObjectID |
int |
Null |
Unique identifier for the object |
WaitApplName |
varchar(30) |
Null |
Name of the application waiting for the lock |
WaitBatchID |
int |
Identifier of the SQL batch executed by the process waiting for the lock when the lock timeout occurred |
|
WaitClientApplName |
varchar(30) |
Null |
Value of the clientapplname property set by the application waiting for the lock |
WaitClientHostName |
varchar(30) |
Null |
Value of the clienthostname property set by the application waiting for the lock |
WaitClientName |
varchar(30) |
Null |
Value of the clientname property set by the application waiting for the lock |
WaitCommand |
varchar(30) |
Null |
Category of process or command that the process was executing when it was blocked and then timed out |
WaitContextID |
int |
Unique context identifier for the process waiting for the lock when it was blocked by another process |
|
WaitHostName |
varchar(30) |
Null |
Name of the host running the process waiting for the lock. |
WaitLineNumber |
int |
Line number of the SQL statement in the SQL batch or stored procedure waiting for the lock |
|
WaitProcDBID |
int |
Unique identifier for the database in which the stored procedure waiting for the lock resides, if applicable |
|
WaitProcDBName |
varchar(30) |
Null |
Name for the database where the stored procedure that is waiting for the lock resides, if applicable |
WaitProcDBName |
varchar(30) |
Null |
Name for the database where the stored procedure that is waiting for the lock resides, if applicable |
WaitProcedureID |
int |
ID of the stored procedure waiting for the lock, if applicable |
|
WaitProcedureName |
varchar(30) |
Null |
Name for the stored procedure waiting for the lock, if applicable |
WaitStmtNumber |
int |
Line number in SQL batch waiting for the lock |
|
WaitTranName |
varchar(255) |
Null |
Name of the transaction in which the lock was requested |
