| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_modifythreshold | | 31 Aug 14 | Defects Dependencies |
1 2 /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */ 3 4 /* 5 ** Messages for "sp_modifythreshold" 17870 6 ** 7 ** 17870, "Table '%1!' does not exist in database '%2!' -- cannot add 8 ** thresholds." 9 ** 17871, "There is no segment named '%1!'." 10 ** 17872, "This threshold is too close to one or more existing thresholds. 11 ** Thresholds must be no closer than 128 pages to each other." 12 ** 17874, "A threshold at %1! pages is logically impossible for 13 ** segment '%2!'. Choose a value between %3! and %4! pages." 14 ** 17875, "Only the DBO of database '%1!' or a user with System Administrator 15 ** (SA) authorization may add, delete, or modify thresholds in that 16 ** database." 17 ** 17904, "Segment '%1!' does not have a threshold at '%2!' pages." 18 ** 18031, "This procedure can only affect thresholds in the current database. 19 ** Say 'USE %1!', then run this procedure again." 20 ** 18032, "You may not alter the free space or segment name of the 21 ** log's last-chance threshold." 22 ** 17289, "Set your curwrite to the hurdle of current database." 23 ** 18084, "A threshold at %1! pages for segment '%2!' is too close to the 24 ** maximum size of the database. Because of this, the threshold will 25 ** not fire more than once per SQL server re-boot. Modify the value 26 ** using sp_modifythreshold to be between %3! and %4! pages." 27 ** 17879, "Only Users that have System Administrator (SA) authorization 28 ** granted directly may add, or modify thresholds in that 29 ** database." 30 */ 31 32 create procedure sp_modifythreshold 33 @dbname varchar(255), /* this database */ 34 @segname varchar(255), /* segment name */ 35 @free_space int, /* threshold level */ 36 @new_proc_name varchar(255) = NULL, /* threshold procedure */ 37 @new_free_space int = NULL, /* new threshold level */ 38 @new_segname varchar(255) = NULL /* new segment name */ 39 as 40 41 declare @segno int, 42 @segmap int, 43 @pagect int, 44 @status int, 45 @proxim int, 46 @newsegno int, 47 @maxthpgct int, 48 @suid int, 49 @roles varbinary(255), 50 @proc varchar(255), 51 @msg varchar(1024), 52 @notdbo int, 53 @sarole int 54 55 /* 56 ** If we are under a user defined xact, disallow this since we may 57 ** leave the dbtable's threshold cache out-of-sync if the end user 58 ** rollbacks its xact. 59 */ 60 if @@trancount > 0 61 begin 62 /* 63 ** 17260, "Can't run %1! from within a transaction." 64 */ 65 raiserror 17260, "sp_modifythreshold" 66 return (1) 67 68 end 69 70 set transaction isolation level 1 71 set chained off 72 73 /* 74 ** Make sure we are in the right database 75 */ 76 if (@dbname != db_name()) 77 begin 78 /* 79 ** 18031, "You are in the wrong database. Say 'USE %1!', then run 80 ** this procedure again." 81 */ 82 raiserror 18031, @dbname 83 return (1) 84 85 end 86 87 /* 88 ** Make sure our database is recent enough to contain Systhresholds 89 */ 90 if (select name from sysobjects 91 where name = 'systhresholds' 92 and type = 'S') 93 is NULL 94 begin 95 /* 96 ** 17870, "Table '%1!' does not exist in database '%2!' -- cannot add 97 ** thresholds." 98 */ 99 raiserror 17870, "systhresholds", @dbname 100 return (1) 101 end 102 103 /* 104 ** Check to see if we are actually the DBO of this database without 105 ** sa_role 106 */ 107 if exists (select * from sysusers where uid = 1 and suid = suser_id()) 108 begin 109 select @notdbo = 0 110 end 111 else 112 begin 113 /* 114 ** Check to see if we are the DBO alias of this database 115 ** without sa_role 116 */ 117 if exists (select * from sysalternates 118 where suid = suser_id() and 119 altsuid = (select suid from sysusers 120 where uid = 1)) 121 begin 122 select @notdbo = 0 123 end 124 else 125 begin 126 select @notdbo = 1 127 end 128 129 end 130 131 select @sarole = 0 132 133 /* 134 ** Check to see if we need to do auditing for sa_role. 135 */ 136 if (charindex("sa_role", show_role()) > 0) 137 begin 138 select @sarole = proc_role("sa_role") 139 end 140 141 /* 142 ** Make sure the user (is the DBO) or (has "sa_role" directly granted to them) 143 */ 144 if (@notdbo = 1 and @sarole != 1) 145 begin 146 if (@sarole = 2) 147 begin 148 /* User has sa_role indirectly therefore deny this attempt. 149 ** This behavior is due to bug 131764, the inability to handle 150 ** all system defined roles acquired through UDR roles. 151 */ 152 /* 153 ** Only Users that have System Administrator (SA) authorization 154 ** granted directly may add, or modify thresholds in that 155 ** database. 156 */ 157 raiserror 17879 158 end 159 else 160 begin 161 /* 162 ** 17875, "Only the DBO of database '%1!' or a user with System 163 ** Administrator (SA) authorization may add, delete, or modify 164 ** thresholds in that database." 165 */ 166 raiserror 17875, @dbname 167 end 168 169 return (1) 170 end 171 172 173 174 /* 175 ** Make sure the segment name is valid. 176 */ 177 select @segno = segment from syssegments where name = @segname 178 if @segno is NULL 179 begin 180 /* 181 ** 17871, "There is no segment named '%1!'." 182 */ 183 raiserror 17871, @segname 184 return (2) 185 end 186 187 /* 188 ** Make sure the threshold exists 189 */ 190 select @proc = proc_name, @status = status 191 from systhresholds 192 where segment = @segno 193 and free_space = @free_space 194 if (@proc is null) 195 begin 196 /* 197 ** 17904, "Segment '%1!' does not have a threshold at '%2!' pages." 198 */ 199 raiserror 17904, @segname, @free_space 200 select segment_name = @segname, free_space 201 from systhresholds 202 where segment = @segno 203 204 return (1) 205 end 206 207 /* 208 ** Establish values for new threshold items 209 */ 210 if (@new_free_space is NULL) 211 select @new_free_space = @free_space 212 if (@new_segname is NULL) 213 select @new_segname = @segname 214 if (@new_proc_name is NULL) 215 select @new_proc_name = @proc 216 select @suid = suser_id() 217 select @roles = current_roles() 218 219 /* 220 ** May not alter free space and segment name on the logsegment 221 ** last-chance threshold 222 */ 223 if ((@segno = 2) and 224 ((@status & 1) = 1) and 225 ((@new_free_space != @free_space) or 226 (@new_segname != @segname))) 227 begin 228 /* 229 ** 18032, "You may not alter the free space or segment name of the 230 ** log's last-chance threshold." 231 */ 232 raiserror 18032 233 return (1) 234 end 235 236 /* If the user provided a new segment name ... */ 237 if @new_segname != @segname 238 begin 239 /* ... then make sure that it is a valid one ... */ 240 select @newsegno = segment from syssegments where name = @new_segname 241 if @newsegno is NULL 242 begin 243 /* 244 ** 17871, "There is no segment named '%1!'." 245 */ 246 raiserror 17871, @new_segname 247 return (2) 248 end 249 end 250 else 251 begin 252 /* 253 ** ... otherwise, do the threshold freespace size 254 ** validation tests using the original segment. 255 */ 256 select @newsegno = @segno 257 end 258 259 /* 260 ** Make sure the threshold fits within the segment 261 */ 262 if (@newsegno < 31) 263 select @segmap = power(2, @newsegno) 264 else 265 select @segmap = low 266 from master.dbo.spt_values 267 where type = "E" 268 and number = 2 269 270 select @pagect = sum(size) from master..sysusages 271 where dbid = db_id() 272 and segmap & @segmap = @segmap 273 if (@new_free_space < 0) or (@new_free_space > @pagect) 274 begin 275 /* 276 ** 17874, "A threshold at %1! pages is logically impossible for 277 ** segment '%2!'. Choose a value between %3! and %4! pages." 278 */ 279 raiserror 17874, @new_free_space, @new_segname, 0, @pagect 280 return (1) 281 end 282 283 /* 284 ** Make sure there is no existing threshold too close to this new one 285 */ 286 select @proxim = (2 * @@thresh_hysteresis) - 1 287 if exists (select free_space from systhresholds 288 where segment = @newsegno 289 and free_space between (@new_free_space - @proxim) 290 and (@new_free_space + @proxim) 291 and free_space != @free_space) 292 begin 293 /* 294 ** 17872, "This threshold is too close to one or more existing 295 ** thresholds. Thresholds must be no closer than 128 pages 296 ** to each other." 297 */ 298 raiserror 17872 299 select segment_name = @new_segname, free_space 300 from systhresholds 301 where segment = @newsegno 302 and free_space between (@new_free_space - @proxim) 303 and (@new_free_space + @proxim) 304 and free_space != @free_space 305 306 return (1) 307 end 308 309 /* 310 ** Make sure that the threshold is not too close to the maximum size of 311 ** the database segment. If threshold value + @@thresh_hysteresis greater 312 ** than maximum size of the database segment then threshold will fire only 313 ** once. Signal this condition to the user. 314 */ 315 select @maxthpgct = @pagect - @@thresh_hysteresis - 1 316 if (@pagect <= (@new_free_space + @@thresh_hysteresis)) 317 begin 318 /* 319 ** 18084, "A threshold at %1! pages for segment '%2!' is too 320 ** close to the maximum size of the database. Because of this, 321 ** the threshold will not fire more than once per SQL server re-boot. 322 ** Modify the value using sp_modifythreshold to be 323 ** between %3! and %4! pages." 324 */ 325 exec sp_getmessage 18084, @msg output 326 print @msg, @new_free_space, @new_segname, 0, @maxthpgct 327 328 /* This is not an error, so raise a message and continue. */ 329 end 330 331 /* 332 ** We do not validate the procedure name, since it might be in a different 333 ** database or even in a remote server. Thus, we have done all the tests 334 ** we can. Modify the threshold. 335 */ 336 337 /* 338 ** 17906, "Dropping threshold for segment '%1!' at '%2!' pages." 339 */ 340 exec sp_getmessage 17906, @msg output 341 print @msg, @segname, @free_space 342 343 344 begin transaction modify_threshold 345 346 delete systhresholds 347 where segment = @segno 348 and free_space = @free_space 349 350 if (@@error != 0) 351 begin 352 rollback transaction 353 /* 354 ** 17907, "Delete of systhresholds row failed." 355 */ 356 raiserror 17907 357 return (1) 358 end 359 360 /* 361 ** 17873, "Adding threshold for segment '%1!' at '%2!' pages." 362 */ 363 exec sp_getmessage 17873, @msg output 364 print @msg, @new_segname, @new_free_space 365 366 367 368 369 insert systhresholds values (@newsegno, @new_free_space, @status, 370 @new_proc_name, @suid, @roles) 371 372 373 if (@@error != 0) 374 begin 375 rollback transaction 376 /* 377 ** 17877, "Insert of systhresholds row failed." 378 */ 379 raiserror 17877 380 return (1) 381 end 382 383 384 /* 385 ** Last, rebuild the database threshold table 386 */ 387 dbcc dbrepair(@dbname, "newthreshold", @segname, 0, @new_segname) 388 389 if (@@error != 0) 390 begin 391 rollback transaction 392 /* 393 ** 17878, "Rebuild of the database threshold table failed." 394 */ 395 raiserror 17878 396 return (1) 397 end 398 399 commit transaction 400 401 return (0) 402
exec sp_procxmode 'sp_modifythreshold', 'AnyMode' go Grant Execute on sp_modifythreshold to public go
| RESULT SETS | |
| sp_modifythreshold_rset_002 | |
| sp_modifythreshold_rset_001 | |
| DEPENDENCIES |
| PROCS AND TABLES USED reads table sybsystemprocs..syssegments reads table sybsystemprocs..sysusers reads table master..spt_values (1)  reads table sybsystemprocs..sysalternates calls proc sybsystemprocs..sp_getmessage  reads table master..sysmessages (1) reads table master..syslanguages (1) reads table sybsystemprocs..sysusermessages calls proc sybsystemprocs..sp_validlang reads table master..syslanguages (1) reads table sybsystemprocs..sysobjects read_writes table sybsystemprocs..systhresholds reads table master..sysusages (1) |
