| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_monitor_deadlock_usage | | 31 Aug 14 | Defects Dependencies |
1 2 /* 3 ** sp_monitor_deadlock_usage 4 ** 5 ** Print the usage help information for monitoring deadlocks. 6 ** 7 ** Parameters: 8 ** @output_type - 'terse' or 'verbose' 9 ** @output_count_by_date 10 ** @output_count_by_appln 11 ** @output_count_by_object 12 ** @output_count_by_date_object 13 ** @opt_verbose 14 ** @opt_pagdiag 15 ** - Output mode phrases that are recognized as 16 ** valid and used to report in usage messages. 17 { 18 */ 19 create procedure sp_monitor_deadlock_usage( 20 @output_type varchar(8) 21 , @output_count_by_date varchar(30) 22 , @output_count_by_appln varchar(30) 23 , @output_count_by_object varchar(30) 24 , @output_count_by_date_object varchar(30) 25 , @opt_verbose varchar(7) 26 , @opt_pagediag varchar(8) 27 ) as 28 begin 29 declare @dlk_table varchar(30) 30 , @deadlock varchar(8) 31 , @deadlock_table varchar(30) 32 , @mdlk_table varchar(30) 33 , @arch_dbname varchar(10) 34 , @prefix varchar(10) 35 , @verb_pagediag varchar(30) 36 , @msg varchar(200) 37 , @sp_command varchar(80) 38 , @dlk_pipe_max_msgs_cfg varchar(30) 39 , @dlk_pipe_max_msgs_value int 40 , @newline char(1) 41 , @tabchar char(1) 42 , @indent varchar(5) 43 44 select @dlk_table = 'daily_monDeadLock' 45 , @deadlock = 'deadlock' 46 , @deadlock_table = 'monDeadLock' 47 , @mdlk_table = 'master.dbo.monDeadLock' 48 , @arch_dbname = 'mondb' 49 , @prefix = 'daily_' 50 , @verb_pagediag = @opt_verbose + "," + @opt_pagediag 51 , @dlk_pipe_max_msgs_cfg = "deadlock pipe max messages" 52 , @dlk_pipe_max_msgs_value = 200 53 , @newline = char(10) 54 , @tabchar = char(9) 55 , @indent = " -- " 56 57 print " " 58 exec sp_getmessage 18954, @msg output 59 select @sp_command = @msg 60 + " sp_monitor '%1!' [ [, '@filters' ] [, '@output_modes' ] ]" 61 print @sp_command, @deadlock 62 63 select @sp_command = @msg + " sp_monitor '%1!' " 64 print @sp_command, @deadlock 65 print " [ [, '<deadlockID>' | '<for Date>' ]" 66 print " [, { '%1!' | '%2!' }" 67 , @opt_verbose, @opt_pagediag 68 69 -- reuse sp_command to build print format string w/newlines embedded. 70 -- 71 select @sp_command = @tabchar + @tabchar + " |" 72 + "{ '%1!' " 73 + @newline + @tabchar + @tabchar + @tabchar 74 + "| '%2!' " 75 + @newline + @tabchar + @tabchar + @tabchar 76 + "| '%3!' " 77 + @newline + @tabchar + @tabchar + @tabchar 78 + "| '%4!' " 79 + @newline + @tabchar + @tabchar 80 + " } ]" 81 -- print " | { '%1!' | '%2!' | '%3!' | '%4!' } ]" 82 print @sp_command 83 , @output_count_by_date 84 , @output_count_by_appln 85 , @output_count_by_object 86 , @output_count_by_date_object 87 print " ]" 88 89 if (@output_type = 'terse') 90 begin 91 return 0 92 end 93 94 print " " 95 96 -- Reuse @verb_pagediag to store term for msg. 97 exec sp_getmessage 19494, @verb_pagediag output 98 select @sp_command = "sp_monitor 'enable', " + "'" + @deadlock + "'" 99 print @verb_pagediag, @sp_command 100 101 exec sp_getmessage 19497, @msg output 102 print @msg 103 , @dlk_pipe_max_msgs_cfg, @dlk_pipe_max_msgs_value 104 105 select @sp_command = "sp_configure '" + @dlk_pipe_max_msgs_cfg + "'" 106 + ", " + convert(varchar, @dlk_pipe_max_msgs_value) 107 108 print @verb_pagediag, @sp_command 109 110 print " " 111 exec sp_getmessage 19498, @msg output 112 print @msg, @mdlk_table 113 114 print " " 115 exec sp_getmessage 19499, @msg output 116 print @msg, @indent 117 print " sp_monitor 'deadlock'" 118 119 print " " 120 exec sp_getmessage 19500, @msg output 121 print @msg, @indent 122 print " sp_monitor 'deadlock', '10'" 123 124 print " " 125 exec sp_getmessage 19501, @msg output 126 print @msg, @indent 127 print " sp_monitor 'deadlock', '10', '%1!'" 128 , @opt_verbose 129 130 print " " 131 exec sp_getmessage 19502, @msg output 132 print @msg, @indent 133 print " sp_monitor 'deadlock', '10', '%1!'" 134 , @verb_pagediag 135 136 print " " 137 exec sp_getmessage 19503, @msg output 138 print @msg, @indent 139 print " sp_monitor 'deadlock', NULL, '%1!'" 140 , @output_count_by_appln 141 142 print " " 143 exec sp_getmessage 19504, @msg output 144 print @msg, @indent 145 print " sp_monitor 'deadlock', NULL, '%1!'" 146 , @output_count_by_object 147 148 print " " 149 exec sp_getmessage 19505, @msg output 150 print @msg, @deadlock_table 151 152 print " " 153 exec sp_getmessage 19506, @msg output 154 print @msg, @indent, @deadlock_table, @arch_dbname 155 print " sp_monitor 'archive', 'deadlock'" 156 157 print " " 158 exec sp_getmessage 19507, @msg output 159 print @msg, @indent, @arch_dbname, @deadlock_table 160 print " %1!..sp_monitor 'report', 'deadlock'" 161 , @arch_dbname 162 , @deadlock_table 163 164 print " " 165 exec sp_getmessage 19508, @msg output 166 print @msg, @indent, @deadlock_table 167 print " sp_monitor 'report', 'deadlock', '10'" 168 169 print " " 170 exec sp_getmessage 19509, @msg output 171 print @msg, @indent, @deadlock_table, @arch_dbname 172 print " sp_monitor 'report', 'deadlock', '10', '%1!'" 173 , @opt_verbose 174 175 print " " 176 exec sp_getmessage 19510, @msg output 177 print @msg, @indent, @deadlock_table 178 print " sp_monitor 'report', 'deadlock', 'Mar 16 2006'" 179 180 print " " 181 exec sp_getmessage 19511, @msg output 182 print @msg, @indent 183 print " sp_monitor 'report', 'deadlock', NULL, '%1!'" 184 , @output_count_by_date 185 186 print " " 187 exec sp_getmessage 19512, @msg output 188 print @msg, @indent 189 print " sp_monitor 'report', 'deadlock', NULL, '%1!'" 190 , @output_count_by_appln 191 192 print " " 193 exec sp_getmessage 19513, @msg output 194 print @msg, @indent 195 print " sp_monitor 'report', 'deadlock', NULL, '%1!'" 196 , @output_count_by_object 197 198 print " " 199 exec sp_getmessage 19514, @msg output 200 print @msg 201 202 print " " 203 exec sp_getmessage 19515, @msg output 204 print @msg, @indent 205 , @prefix, @deadlock_table, @arch_dbname 206 print " sp_monitor 'archive, prefix=%1!', 'deadlock'" 207 , @prefix 208 209 print " " 210 exec sp_getmessage 19516, @msg output 211 print @msg, @indent 212 , @prefix, @deadlock_table, @arch_dbname 213 print " sp_monitor 'report, prefix=%1!', 'deadlock'" 214 , @prefix 215 216 end -- } 217
exec sp_procxmode 'sp_monitor_deadlock_usage', 'AnyMode' go Grant Execute on sp_monitor_deadlock_usage to public go
| DEFECTS | |
 MGTP 3 Grant to public sybsystemprocs..sp_monitor_deadlock_usage | |
| MNER 3 No Error Check should check return value of exec | 58 |
| MNER 3 No Error Check should check return value of exec | 97 |
| MNER 3 No Error Check should check return value of exec | 101 |
| MNER 3 No Error Check should check return value of exec | 111 |
| MNER 3 No Error Check should check return value of exec | 115 |
| MNER 3 No Error Check should check return value of exec | 120 |
| MNER 3 No Error Check should check return value of exec | 125 |
| MNER 3 No Error Check should check return value of exec | 131 |
| MNER 3 No Error Check should check return value of exec | 137 |
| MNER 3 No Error Check should check return value of exec | 143 |
| MNER 3 No Error Check should check return value of exec | 149 |
| MNER 3 No Error Check should check return value of exec | 153 |
| MNER 3 No Error Check should check return value of exec | 158 |
| MNER 3 No Error Check should check return value of exec | 165 |
| MNER 3 No Error Check should check return value of exec | 170 |
| MNER 3 No Error Check should check return value of exec | 176 |
| MNER 3 No Error Check should check return value of exec | 181 |
| MNER 3 No Error Check should check return value of exec | 187 |
| MNER 3 No Error Check should check return value of exec | 193 |
| MNER 3 No Error Check should check return value of exec | 199 |
| MNER 3 No Error Check should check return value of exec | 203 |
| MNER 3 No Error Check should check return value of exec | 210 |
| MUCO 3 Useless Code Useless Brackets in create proc | 19 |
| MUCO 3 Useless Code Useless Begin-End Pair | 28 |
| MUCO 3 Useless Code Useless Brackets | 89 |
| VNRD 3 Variable is not read @dlk_table | 44 |
 MTR1 2 Metrics: Comments Ratio Comments: 9% | 19 |
| MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 1 = 1dec - 2exi + 2 | 19 |
| MTR3 2 Metrics: Query Complexity Complexity: 98 | 19 |
| DEPENDENCIES |
| PROCS AND TABLES USED calls proc sybsystemprocs..sp_getmessage  reads table sybsystemprocs..sysusermessages reads table master..sysmessages (1)  reads table master..syslanguages (1) calls proc sybsystemprocs..sp_validlang reads table master..syslanguages (1) CALLERS called by proc sybsystemprocs..sp_monitor_deadlock  called by proc sybsystemprocs..sp_monitor |