| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_monitor_deadlock_driver | | 31 Aug 14 | Defects Dependencies |
1 2 /* 3 ** sp_monitor_deadlock_driver 4 ** 5 ** Main driver procedure to perform deadlock analysis. 6 ** 7 ** Parameters: 8 ** @opt_verbose - Verbose output. 9 ** @opt_pagediag - Generate page diagnostics also. 10 ** (Only works if @opt_verbose is also 1.) 11 ** @num_deadlocks - (Output) # of deadlock events processed. 12 ** 13 ** Returns: 14 ** Nothing. 15 { 16 */ 17 create procedure sp_monitor_deadlock_driver( 18 @opt_verbose int = 0 19 , @opt_pagediag int = 0 20 , @num_deadlocks int output 21 ) as 22 begin 23 declare @dlk_id int 24 , @retval int 25 , @resolve_time datetime 26 , @dbname varchar(30) 27 28 -- Variables to retrieve info about each line-item for 29 -- a given deadlock ID. 30 31 , @dlk_id_inner int 32 , @held_spid int 33 , @wait_spid int 34 , @held_cmd varchar(30) 35 , @held_xactname varchar(255) 36 37 -- , @wait_cmd varchar(30) 38 -- , @wait_xactname varchar(255) 39 , @servername varchar(30) 40 , @objname varchar(255) 41 , @instancename varchar(30) 42 , @objdbid int 43 , @pagenumber int 44 , @pagediag int --whether to call page diagnostics 45 , @heldlocktype varchar(20) 46 , @waitlocktype varchar(20) 47 , @heldusername varchar(30) 48 , @heldapplname varchar(30) 49 , @waitusername varchar(30) 50 51 -- Location in code where deadlock occurred. 52 , @heldsrcID varchar(30) 53 , @waitsrcID varchar(30) 54 55 -- Get identity of proc (if any) of holding spid. 56 -- 57 , @heldprocdbid int 58 , @heldprocedureid int 59 , @heldlinenumber int 60 , @heldprocdbname varchar(30) 61 , @heldprocedurename varchar(255) 62 63 -- Common phrases used in print outputs. Centralize for 64 -- easy 'bgrep' processing. 65 , @deadlockid_str varchar(12) 66 67 select @num_deadlocks = 0 68 , @deadlockid_str = "Deadlock ID" 69 70 -- Process one row for given deadlock ID or all rows in the table, 71 -- and produce a report for each deadlock occurence, to show the 72 -- wait-for graph in English-language 1204'esque output. 73 -- 74 declare dlitem_cur cursor for 75 select DISTINCT ServerName, DeadlockID, ObjectDBName, 76 ResolveTime, InstanceName 77 from #mon__deadlock_info 78 79 open dlitem_cur 80 81 declare dlitem_cur_inner cursor for 82 select DeadlockID 83 , HeldSPID 84 , WaitSPID 85 , HeldCommand 86 , HeldTranName 87 , ObjectDBID 88 , ObjectName 89 , PageNumber 90 , HeldLockType 91 , WaitLockType 92 93 -- Following Fields reported under 'verbose' output model 94 , HeldUserName 95 , HeldApplName 96 , WaitUserName 97 98 , HeldProcDBID 99 , HeldProcedureID 100 , HeldProcDBName 101 , HeldProcedureName 102 , HeldLineNumber 103 , HeldSourceCodeID 104 , WaitSourceCodeID 105 106 from #mon__deadlock_info 107 where DeadlockID = @dlk_id 108 and ResolveTime = @resolve_time 109 110 while (1 = 1) 111 begin -- { 112 fetch dlitem_cur into @servername, @dlk_id 113 , @dbname 114 , @resolve_time 115 , @instancename 116 if (@@sqlstatus != 0) 117 break 118 119 select @num_deadlocks = @num_deadlocks + 1 120 121 print ' ' 122 if (@@clustermode = 'shared disk cluster') 123 begin 124 print "****** Server: '%1!' %2! %3! Dbname: '%4!' Resolve Time: %5! Detected by Instance: '%6!' ****** " 125 , @servername 126 , @deadlockid_str 127 , @dlk_id, @dbname 128 , @resolve_time 129 , @instancename 130 end 131 else 132 begin 133 print "****** Server: '%1!' %2! %3! Dbname: '%4!' Resolve Time: %5! ****** " 134 , @servername 135 , @deadlockid_str 136 , @dlk_id, @dbname 137 , @resolve_time 138 end 139 -- For this DeadlockID, go back and process all rows. 140 open dlitem_cur_inner 141 142 while (1 = 1) 143 begin -- { 144 145 fetch dlitem_cur_inner into @dlk_id_inner 146 , @held_spid 147 , @wait_spid 148 , @held_cmd 149 , @held_xactname 150 , @objdbid 151 , @objname 152 , @pagenumber 153 , @heldlocktype 154 , @waitlocktype 155 , @heldusername 156 , @heldapplname 157 , @waitusername 158 , @heldprocdbid 159 , @heldprocedureid 160 , @heldprocdbname 161 , @heldprocedurename 162 , @heldlinenumber 163 , @heldsrcID 164 , @waitsrcID 165 166 if (@@sqlstatus != 0) 167 break 168 169 print " %1! %2! Spid %3! was waiting for '%4!' lock on dbid=%5! object '%6!' page=%7!" 170 , @deadlockid_str 171 , @dlk_id_inner 172 , @wait_spid 173 , @waitlocktype 174 , @objdbid 175 , @objname 176 , @pagenumber 177 178 print " %1! %2! Spid %3! was holding '%4!' lock on object '%5!' page=%6!. Command: '%7!', Holding TranName: '%8!'" 179 , @deadlockid_str 180 , @dlk_id_inner 181 , @held_spid 182 , @heldlocktype 183 , @objname 184 , @pagenumber 185 , @held_cmd 186 , @held_xactname 187 188 if (@opt_verbose = 1) 189 begin -- { 190 191 exec @retval = sp_monitor_deadlock_verbose 192 @deadlockid_str 193 , @dlk_id_inner 194 , @held_spid 195 , @heldusername 196 , @heldapplname 197 , @waitusername 198 199 , @heldprocdbid 200 , @heldprocedureid 201 , @heldprocdbname 202 , @heldprocedurename 203 , @heldlinenumber 204 205 , @objdbid 206 , @pagenumber 207 , @objname 208 , @opt_pagediag 209 210 , @heldsrcID 211 , @waitsrcID 212 213 end -- } 214 215 end -- } 216 217 close dlitem_cur_inner 218 219 end -- } 220 close dlitem_cur 221 222 deallocate cursor dlitem_cur_inner 223 deallocate cursor dlitem_cur 224 225 end -- } 226
exec sp_procxmode 'sp_monitor_deadlock_driver', 'AnyMode' go Grant Execute on sp_monitor_deadlock_driver to public go
| DEPENDENCIES |
| PROCS AND TABLES USED calls proc sybsystemprocs..sp_monitor_deadlock_verbose reads table tempdb..#mon__deadlock_info (1) CALLERS called by proc sybsystemprocs..sp_monitor_deadlock  called by proc sybsystemprocs..sp_monitor |
