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