Monitors and analyzes deadlock events from the monitoring table monDeadLock, and presents this data in human-readable format.
sp_monitor deadlock [ [, filters ] [, output_modes ] ]
To display deadlock events from either a monitoring table or an archive, enter:
sp_monitor deadlock
[ [,NULL | '<deadlockID>' | '<forDate>']
[, { 'verbose' | 'pagediag' }
| { 'count by date'
| 'count by application'
| 'count by date, object'
} ] ]
filters – filters out deadlock events based on attributes including:
<deadlockID> – the integer deadlock ID, entered as a string.
<forDate> – the character representation of the date literal for which the deadlock events must be analyzed.
output_modes – allows the user to customize the output for each deadlock event. This parameter can be a combination of verbose and pagediag, separated by commas, or a summary output mode, which generates the frequency of deadlocks by object name, or application name, and so forth, rather than generating line item descriptions of each deadlock.
verbose – produces details of the deadlock event: user, application names, and so forth.
pagediag – produces page diagnostics that identify the type of page (data or index) in the deadlock event. This print option is only valid with the verbose output mode.
The summary output modes give summary information on the frequency of deadlocks. They are 'count by date', 'count by application', 'count by date, object'.
Example 1 A typical line item report:
> sp_monitor deadlock **********Server: 'adit_125x_2k_bigbang' Deadlock ID 95 Dbname: 'tempdb' Resolve Time: Oct. 1 2006 1:12PM******** Deadlock ID 95 Spid 470 was waiting for 'exclusive intent' lock on dbid=2 object 'sysdepends' page=0 Deadlock ID 95 Spid 456 was holding 'exclusive table' lock on object 'sysdepends' page=0 TranName: '$ALTER TABLE #t2_alter_table ADD/DROP/MODIFY COLUMNS ID=1055600629' Deadlock ID 95 Spid 456 was waiting for 'exclusive table' lock on dbid=2 object 'syscomments' page=0 Deadlock ID 95 Spid 470 was holding 'exclusive table' lock on object 'syscomments' page=0. Command: 'CREATE TABLE', Holding TranName: '$CREATE TABLE #t1_create_ta02004700014258171 allpages executiontime'
Example 2 Generates the report for a particular deadlock event.
> sp_monitor deadlock, ID 97 **********Server: 'adit_125x_2k_bigbang' Deadlock ID 97 Dbname: 'tempdb' Resolve Time: Oct. 1 2006 1:12PM******** Deadlock ID 97 Spid 470 was waiting for 'exclusive intent' lock on dbid=2 object 'sysdepends' page=0 Deadlock ID 95 Spid 456 was holding 'exclusive table' lock on object 'sysdepends' page=0 TranName: '$ALTER TABLE #t2_alter_table ADD/DROP/MODIFY COLUMNS ID=1087600743' Deadlock ID 97 Spid 456 was waiting for 'exclusive table' lock on dbid=2 object 'syscomments' page=0 Deadlock ID 97 Spid 470 was holding 'exclusive table' lock on object 'syscomments' page=0. Command: 'CREATE TABLE', Holding TranName: '$CREATE TABLE #t1_create_ta02004700014258171 allpages executiontime'
Example 3 Tracking the code causing a deadlock:
sp_monitor deadlock, '120', "verbose, pagediag"
verbose produces the SQL text, with line numbers, of procedures involved in the deadlock. pagediag analyzes the pages, if any, involved in the deadlock.
Use sp_configure to manually set monitoring table infrastructure:
sp_configure 'deadlock pipe max messages', 200
