| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_monitor_disable | | 31 Aug 14 | Defects Dependencies |
1 2 /* 3 ** sp_monitor_disable 4 ** 5 ** This stored procedure is a subordinate stored procedure that is invoked from 6 ** the parent stored procedure sp_monitor when "disable" is passed as the 7 ** argument. This stored procedure provides a way to disable the configuration 8 ** parameters relevant to monitoring 9 ** 10 ** sp_monitor "disable" should be run once the monitoring activities using 11 ** sp_monitor stored procedures that use monitoring tables are complete. 12 ** This will ensure that all the configuration parameters doing monitoring 13 ** are turned off. 14 ** 15 ** Monitoring can be disabled using 'all' mode where all the config options 16 ** related to all monitoring items are turned OFF. Optionally, individual 17 ** config options for individual monitoring types can be selectively 18 ** disabled. If 'all' monitoring types are disabled, the control table is then 19 ** dropped. 20 ** 21 ** Parameters: 22 ** @montype - Type of monitoring to be disabled. 23 ** 24 ** Returns: 25 ** 0 - upon sucessful completion 26 ** 1 - If an error resulted. 27 { 28 */ 29 30 create procedure sp_monitor_disable(@montype varchar(30) = NULL 31 ) as 32 begin 33 declare @rtnstatus int 34 declare @val int 35 , @char_index int 36 , @cfgnum int 37 , @cfgname varchar(80) 38 , @printmsg_cfg varchar(80) 39 , @printmsg_type varchar(80) 40 , @whoami varchar(30) 41 42 select @whoami = object_name(@@procid, db_id('sybsystemprocs')) 43 exec @rtnstatus = sp_monitor_check_permission @whoami 44 if (@rtnstatus != 0) 45 return @rtnstatus 46 47 exec @rtnstatus = sp_monitor_verify_setup 48 if (@rtnstatus != 0) 49 return @rtnstatus 50 51 -- User might have entered: sp_monitor disable, 'procstack monitoring' 52 -- If so, strip out the term 'monitoring', and get the base monitoring type 53 -- which would be one of terms like 'all', 'connection', 'procstack' etc. 54 -- 55 if (@montype LIKE "% monitoring") 56 begin 57 -- Locate start of ' monitoring' phrase. 58 select @char_index = charindex(' ', @montype) 59 if (@char_index > 0) 60 begin 61 select @montype = ltrim(rtrim(substring(@montype, 1, 62 (@char_index - 1)))) 63 end 64 end 65 66 -- Validate argument for monitoring type and error out if it's invalid. 67 -- 68 if (@montype IS NOT NULL) and (@montype NOT IN ('all' 69 , 'connection' 70 , 'statement' 71 , 'event' 72 , 'procedure' 73 , 'deadlock' 74 , 'procstack' 75 )) 76 begin 77 raiserror 19260, "sp_monitor 'help', 'disable'" 78 return 1 79 end 80 81 -- Silently return if there is no work to do; i.e. the control table is 82 -- not to be found. 83 -- 84 if (object_id('tempdb.dbo.mon_config') IS NULL) 85 begin 86 return 0 87 end 88 89 exec sp_getmessage 19479, @printmsg_type output 90 exec sp_getmessage 17778, @printmsg_cfg output 91 92 if (@montype IS NULL) or (@montype = 'all') 93 begin 94 if (object_id('tempdb.dbo.mon_config') IS NOT NULL) 95 begin 96 print @printmsg_type, "all" 97 98 -- Turn OFF monitoring for all options that you find set ON in the 99 -- control table. Eliminate duplicate rows, as certain config options 100 -- are common across monitoring types and will appear more than once 101 -- in the control table. 102 -- 103 select distinct confignum, configname 104 into #mon_configs_on 105 from tempdb.dbo.mon_config 106 where configval = 0 107 and confignum > 0 -- eliminate non-config entry rows. 108 109 -- Report which config options are being turned OFF. 110 declare mon_configs_on_cur cursor for 111 select confignum, configname 112 from #mon_configs_on 113 for read only 114 115 open mon_configs_on_cur 116 while (1 = 1) 117 begin 118 fetch mon_configs_on_cur into @cfgnum, @cfgname 119 if (@@sqlstatus != 0) 120 break 121 122 print @printmsg_cfg, @cfgname 123 end 124 125 close mon_configs_on_cur 126 deallocate cursor mon_configs_on_cur 127 128 select @val = config_admin(23, confignum, 0, 0, NULL, NULL) 129 from #mon_configs_on 130 131 drop table #mon_configs_on 132 drop table tempdb.dbo.mon_config 133 134 select @val = mdaconfig('disable_lwp', 'end') 135 end 136 end 137 138 else 139 begin 140 print @printmsg_type, @montype 141 142 -- Turn OFF config options for individual monitoring items. 143 -- Some of these config options are common between different monitoring 144 -- types. So, if are we turning OFF, say, monitoring for 'statement', 145 -- and we still want to retain monitoring for 'connection', we don't 146 -- want to turn OFF the config options that are common between them. 147 -- The way we handle this situation is: 148 149 -- . Find the config options for this monitoring type that are 0. 150 -- (If the option was recorded as 1, it means that when monitoring 151 -- was enabled for the type being disabled, that config option 152 -- was already ON, turned ON by the user using sp_configure.) 153 154 -- . For each such option, find out if the option is ON for some 155 -- other monitoring type. If so, leave it alone. 156 157 -- . Otherwise, turn OFF this config option. This will ensure that 158 -- we only affect those config options that are needed by this 159 -- monitoring type and are currently unused by any other type. 160 -- 161 declare cfg_option_cur cursor for 162 select m1.confignum, m1.configname 163 from tempdb.dbo.mon_config m1 164 where m1.monitor = @montype 165 and not exists (select 1 166 from tempdb.dbo.mon_config m2 167 where m2.monitor != @montype 168 and m2.confignum = m1.confignum) 169 170 and (m1.configval = 0) -- cfg was not ON before monitoring started 171 and m1.confignum > 0 -- eliminate non-config entry rows. 172 for read only 173 174 open cfg_option_cur 175 176 while (1 = 1) 177 begin 178 fetch cfg_option_cur into @cfgnum, @cfgname 179 if (@@sqlstatus != 0) 180 break 181 182 print @printmsg_cfg, @cfgname 183 select @val = config_admin(23, @cfgnum, 0, 0, NULL, NULL) 184 end 185 186 close cfg_option_cur 187 deallocate cursor cfg_option_cur 188 189 -- Delete all rows to indicate that monitoring is disabled now. 190 delete tempdb.dbo.mon_config where monitor = @montype 191 end 192 193 return (@rtnstatus) 194 end -- } 195
exec sp_procxmode 'sp_monitor_disable', 'AnyMode' go Grant Execute on sp_monitor_disable to public go
| DEPENDENCIES |
| PROCS AND TABLES USED read_writes table tempdb..#mon_configs_on (1) calls proc sybsystemprocs..sp_monitor_check_permission calls proc sybsystemprocs..sp_monitor_verify_setup  reads table master..sysobjects (1)  calls proc sybsystemprocs..sp_monitor_getcfgnum reads table master..sysconfigures (1) calls proc sybsystemprocs..sp_monitor_getcfgval reads table master..syscurconfigs (1) reads table master..sysconfigures (1) calls proc sybsystemprocs..sp_getmessage reads table master..sysmessages (1) calls proc sybsystemprocs..sp_validlang reads table master..syslanguages (1) reads table sybsystemprocs..sysusermessages reads table master..syslanguages (1) |
