Database | Proc | Application | Created | Links |
sybsystemprocs | sp_dropsegment ![]() | ![]() | 31 Aug 14 | Defects Dependencies |
1 2 /* Sccsid = "@(#) generic/sproc/src/%M% %I% %G%" */ 3 4 /* 4.8 1.1 06/14/90 sproc/src/defaultlanguage */ 5 6 /* 7 ** Messages for "sp_dropsegment" 17520 8 ** 9 ** 17260, "Can't run %1! from within a transaction." 10 ** 17281, "The specified device is not used by the database." 11 ** 17520, "There is no such segment as '%1!'." 12 ** 17521, "Can't drop the '%1!'segment completely." 13 ** 17522, "The segment '%1!' is being used." 14 ** 17523, "Segment '%1!' does not reference device '%2!'." 15 ** 17524, "There is only one device mapping for the segment '%1!' -- use sp_dropsegment with no device argument." 16 ** 17525, "Segment dropped." 17 ** 17526, "Segment reference to device dropped." 18 ** 17527, "WARNING: There are no longer any segments referencing device '%1!'. This device will no longer be used for space allocation." 19 ** 17528, "WARNING: There are no longer any segments referencing devices '%1!'. These devices will no longer be used for space allocation." 20 ** 17590, "The specified database does not exist." 21 ** 17715, "The last-chance threshold for database %1! is now %2! pages." 22 ** 17716, "Could not update the last-chance threshold for database %1!" 23 ** 17288, "You must execute this procedure from the database 24 ** in which you wish to %1! a segment. Please execute 25 ** 'use %2!' and try again." 26 ** 17289, "Set your curwrite to the hurdle of current database." 27 */ 28 29 create procedure sp_dropsegment 30 @segname varchar(255), /* segment name */ 31 @dbname varchar(255), /* database name */ 32 @device varchar(255) = null /* device name */ 33 as 34 35 declare @dbuid int /* id of the owner of the database */ 36 declare @dbid smallint /* id of the database */ 37 declare @segbit int /* this is the bit to turn on in sysusages */ 38 declare @segnum int /* this is the segment number*/ 39 declare @lct int /* log's last-chance threshold setting */ 40 declare @msg varchar(1024) 41 declare @procval int 42 declare @bitdesc varchar(30) /* bit description for the db */ 43 declare @tempdb_mask int /* All database status bit for a tempdb */ 44 declare @isatempdb int /* Is this a temp. db ? */ 45 declare @isamaster int 46 47 select @procval = 0 48 49 50 /* 51 ** If we're in a transaction, disallow this since it might make recovery 52 ** impossible. 53 */ 54 if @@trancount > 0 55 begin 56 /* 57 ** 17260, "Can't run %1! from within a transaction." 58 */ 59 raiserror 17260, "sp_dropsegment" 60 return (1) 61 end 62 else 63 begin 64 set chained off 65 end 66 67 set transaction isolation level 1 68 69 /* 70 ** Only the Database Owner (DBO) or 71 ** Accounts with SA role can execute it. 72 ** Call proc_role() with the required SA role. 73 */ 74 75 if (user_id() = 1) 76 begin 77 /* If user has sa role audit this as a successful sa 78 ** command execution. 79 */ 80 if charindex("sa_role", show_role()) > 0 81 select @procval = proc_role("sa_role") 82 end 83 else 84 begin 85 /* user_id() is not DBO hence user does not have SA role 86 ** audit this as a failed sa command execution. 87 */ 88 select @procval = proc_role("sa_role") 89 return (1) 90 end 91 92 /* 93 ** There are two drop cases: 94 ** 1) dropping the segment 95 ** 2) dropping a segment reference to a device 96 ** The sproc does things differently depending on which we're doing. 97 */ 98 99 /* 100 ** Make ure the database exists 101 */ 102 if not exists (select * from master.dbo.sysdatabases 103 where name = @dbname) 104 begin 105 /* 17590, "The specified database does not exist." */ 106 raiserror 17590 107 return (1) 108 end 109 110 /* 111 ** Make sure that we are in the database specified 112 ** by @dbname. 113 */ 114 if @dbname != db_name() 115 begin 116 /* 117 ** 13232, "drop" 118 ** 17288, "You must execute this procedure from the database 119 ** in which you wish to %1! a segment. Please execute 120 ** 'use %2!' and try again." 121 */ 122 declare @action varchar(30) 123 select @action = description 124 from master.dbo.sysmessages 125 where error = 13232 and langid = @@langid 126 if @action is null 127 select @action = description 128 from master.dbo.sysmessages 129 where error = 13232 and langid is null 130 if @action is null select @action = "drop" 131 raiserror 17288, @action, @dbname 132 return (1) 133 end 134 135 select @dbid = db_id(@dbname) 136 137 /* 138 ** Determine if we are dealing with a temporary database. 139 */ 140 select @tempdb_mask = number 141 from master.dbo.spt_values 142 where type = "D3" and name = "TEMPDB STATUS MASK" 143 144 if (@dbid = 2) or exists (select * from master.dbo.sysdatabases 145 where dbid = @dbid 146 and (status3 & @tempdb_mask) != 0) 147 begin 148 select @isatempdb = 1 149 end 150 else 151 begin 152 select @isatempdb = 0 153 end 154 155 select @isamaster = 0 156 if ((@dbid = db_id("master")) 157 or (@dbid = db_id("master_companion"))) 158 begin 159 select @isamaster = 1 160 end 161 162 /* 163 ** Check to see if the segment exists. 164 */ 165 if not exists (select * 166 from syssegments 167 where name = @segname) 168 begin 169 /* 170 ** 17520, "There is no such segment as '%1!'." 171 */ 172 raiserror 17520, @segname 173 return (1) 174 end 175 176 /* 177 ** Get the segment number of @segname. 178 */ 179 select @segbit = segment 180 from syssegments 181 where name = @segname 182 183 select @segnum = @segbit 184 185 /* 186 ** Now convert the segment number to the right bit for segmap. 187 */ 188 if (@segbit < 31) 189 select @segbit = power(2, @segbit) 190 else 191 /* 192 ** Since this is segment 31, power(2, 31) will overflow 193 ** since segmap is an int. We'll grab the machine-dependent 194 ** bit mask from spt_values to clear the right bit. 195 */ 196 select @segbit = low 197 from master.dbo.spt_values 198 where type = "E" 199 and number = 2 200 201 /* 202 ** Don't allow the segments 'default', 'system', or 'logsegment' to 203 ** be dropped completely. 204 */ 205 if @segname in ("system", "default", "logsegment") 206 begin 207 if @device is null 208 begin 209 /* 210 ** 17521, "Can't drop the '%1!'segment completely." 211 */ 212 raiserror 17521, @segname 213 return (1) 214 end 215 216 /* 217 ** For the case where we are dropping the logsegment from a 218 ** device, the logic below works OK. If this is the last 219 ** device for the the segment, it won't let the segment be 220 ** dropped. 221 */ 222 end 223 224 /* If we are removing log space and this is not a temporary database ... */ 225 if @segbit = 4 and @isatempdb = 0 226 begin 227 /* 228 ** ... check single user bit (4096). 229 ** The database must be in single user mode so that no log allocation 230 ** or deallocation take place while looking for possibly stranded 231 ** objects, and while recalculating the free space counter. 232 */ 233 select @bitdesc = null 234 select @bitdesc = v.name 235 from master.dbo.spt_values v, master.dbo.sysdatabases d 236 where d.dbid = @dbid 237 and v.type = "D" 238 and d.status & v.number = 4096 239 if @bitdesc is null 240 begin 241 /* 242 ** 17793, "System Administrator (SA) must set database '%1!' to single-user mode with sp_dboption before using '%2!'." 243 */ 244 raiserror 17793, @dbname, "sp_dropsegment" 245 return (1) 246 end 247 end 248 249 /* 250 ** If we are dropping the segment, check to see that the segment isn't 251 ** being used by anyone. 252 */ 253 if @device is null 254 begin 255 256 /* 257 ** Check to see if the segment is being used. 258 */ 259 if exists (select * 260 from sysindexes i, syssegments s, syspartitions p 261 where s.name = @segname 262 and (i.segment = s.segment or 263 p.segment = s.segment)) 264 begin 265 /* 266 ** 17522, "The segment '%1!' is being used." 267 */ 268 raiserror 17522, @segname 269 return (1) 270 end 271 end 272 273 /* 274 ** If we are dropping the segment reference, check to see that arguments are 275 ** reasonable. 276 */ 277 if @device is not null 278 begin 279 /* 280 ** Now see if the database even uses the @device 281 */ 282 if not exists (select * 283 from master.dbo.sysusages u, master.dbo.sysdevices d 284 where d.name = @device 285 and u.vdevno = d.vdevno 286 and u.dbid = @dbid) 287 begin 288 /* 289 ** 17281, "The specified device is not used by the database." 290 */ 291 raiserror 17281 292 293 294 295 return (1) 296 end 297 298 /* 299 ** Now check that the segment references the device. 300 */ 301 if not exists (select * 302 from master.dbo.sysusages u, master.dbo.sysdevices d 303 where u.segmap & @segbit = @segbit 304 and d.name = @device 305 and u.vdevno = d.vdevno 306 and u.dbid = @dbid 307 and ((d.status & 2 = 2) or (d.status2 & 8 = 8))) 308 begin 309 /* 310 ** 17523, "Segment '%1!' does not reference device '%2!'." 311 */ 312 raiserror 17523, @segname, @device 313 return (1) 314 end 315 316 /* 317 ** Now check to see if this is the last unique device reference for the 318 ** segment. If it is then we don't want to drop the reference. 319 */ 320 select distinct d.name into #temptable 321 from master.dbo.sysusages u, master.dbo.sysdevices d 322 where u.segmap & @segbit = @segbit 323 and u.vdevno = d.vdevno 324 and u.dbid = @dbid 325 and ((d.status & 2 = 2) or (d.status2 & 8 = 8)) 326 327 if (select count(*) from #temptable) <= 1 328 begin 329 /* 330 ** 17524, "There is only one device mapping for the segment '%1!' -- use sp_dropsegment with no device argument." 331 */ 332 raiserror 17524, @segname 333 return (1) 334 end 335 336 drop table #temptable 337 end 338 339 /* 340 ** If we're dropping the segment, go ahead drop the segment and 341 ** clear its bit in sysusages. 342 */ 343 if @device is null 344 begin 345 346 347 /* 348 ** Remove any threshold references 349 */ 350 if exists (select * from sysobjects where name = "systhresholds") 351 delete systhresholds 352 from systhresholds t, syssegments s 353 where s.name = @segname 354 and s.segment = t.segment 355 356 /* 357 ** NOTE: Don't update master.dbo.sysusages and syssegments as a xact 358 ** since it could cause problems for recovery. 359 */ 360 361 362 363 delete syssegments 364 where name = @segname 365 366 367 368 /* Encapsulate sysusages/anchors update in a transaction */ 369 if (@isamaster = 1) 370 begin transaction sysusg_upd 371 372 /* 373 ** Now clear the segment from device in sysusages. 374 */ 375 update master.dbo.sysusages 376 set segmap = segmap & (~ @segbit) 377 from master.dbo.sysusages u, master.dbo.sysdevices d 378 where u.vdevno = d.vdevno 379 and u.dbid = @dbid 380 and ((d.status & 2 = 2) 381 or (d.status2 & 8 = 8)) 382 end 383 384 /* 385 ** We want to just clear the segment reference from the device. 386 */ 387 else 388 begin 389 390 391 /* Encapsulate sysusages/anchors update in a transaction */ 392 if (@isamaster = 1) 393 begin transaction sysusg_upd 394 395 update master.dbo.sysusages 396 set segmap = segmap & (~ @segbit) 397 from master.dbo.sysusages u, master.dbo.sysdevices d 398 where u.vdevno = d.vdevno 399 and u.dbid = @dbid 400 and ((d.status & 2 = 2) 401 or (d.status2 & 8 = 8)) 402 and d.name = @device 403 404 /* 405 ** Remove any threshold references that exceed the remaining 406 ** size of the segment 407 */ 408 if exists (select * from sysobjects where name = "systhresholds") 409 delete systhresholds 410 from systhresholds t, syssegments s 411 where s.name = @segname 412 and s.segment = t.segment 413 and t.free_space >= (select sum(size) 414 from master.dbo.sysusages 415 where dbid = @dbid 416 and segmap & @segbit = @segbit) 417 end 418 419 if (@isamaster = 1) 420 begin 421 if (@@error != 0) 422 begin 423 rollback tran sysusg_upd 424 return (1) 425 end 426 427 dbcc dbrepair(@dbname, "upd_usg") 428 if (@@error != 0) 429 begin 430 rollback tran sysusg_upd 431 return (1) 432 end 433 434 commit transaction sysusg_upd 435 end 436 437 /* 438 ** Now we need to activate the new segment map. 439 */ 440 dbcc dbrepair(@dbname, remap, NULL, @segnum, @segname) 441 442 /* 443 ** By removing a piece of the log segment, we may have cleared a stranded 444 ** object condition, so we need to look for stranded objects. We can also 445 ** take this opportunity to recalculate the log's last-chance threshold. 446 */ 447 if @segbit = 4 448 begin 449 dbcc dbrepair(@dbname, findstranded, NULL, @isatempdb) 450 select @lct = lct_admin("lastchance", @dbid) 451 if @lct > 0 452 exec sp_getmessage 17715, @msg out 453 else 454 exec sp_getmessage 17716, @msg out 455 456 print @msg, @dbname, @lct 457 end 458 459 if @device is null 460 begin 461 /* 462 ** 17525, "Segment dropped." 463 */ 464 exec sp_getmessage 17525, @msg output 465 print @msg 466 end 467 else 468 begin 469 /* 470 ** 17526, "Segment reference to device dropped." 471 */ 472 exec sp_getmessage 17526, @msg output 473 print @msg 474 end 475 476 /* 477 ** If there are no more segment references to the device, 478 ** print a warning. 479 */ 480 if exists (select * 481 from master.dbo.sysusages 482 where segmap = 0 483 and dbid = @dbid) 484 begin 485 declare @nosegs varchar(255), /* list of unreferenced devices */ 486 @curdevice varchar(30), 487 @i int 488 489 select @curdevice = min(d.name) 490 from master.dbo.sysusages u, master.dbo.sysdevices d 491 where u.vdevno = d.vdevno 492 and u.dbid = @dbid 493 and ((d.status & 2 = 2) or (d.status2 & 8 = 8)) 494 and segmap = 0 495 select @nosegs = @curdevice, @i = 1 496 while (@curdevice is not null) 497 begin 498 select @curdevice = min(d.name) 499 from master.dbo.sysusages u, master.dbo.sysdevices d 500 where u.vdevno = d.vdevno 501 and u.dbid = @dbid 502 and ((d.status & 2 = 2) 503 or (d.status2 & 8 = 8)) 504 and segmap = 0 505 and d.name > @curdevice 506 if @curdevice is not null 507 begin 508 select @nosegs = @nosegs + ", " + @curdevice 509 select @i = @i + 1 510 end 511 end 512 513 /* 514 ** Get the device names. 515 */ 516 if @i > 1 517 /* 518 ** 17528, "WARNING: There are no longer any segments referencing devices '%1!'. These devices will no longer be used for space allocation." 519 */ 520 exec sp_getmessage 17528, @msg output 521 else 522 /* 523 ** 17527, "WARNING: There are no longer any segments referencing device '%1!'. This device will no longer be used for space allocation." 524 */ 525 exec sp_getmessage 17527, @msg output 526 print @msg, @nosegs 527 end 528 return (0) 529
exec sp_procxmode 'sp_dropsegment', 'AnyMode' go Grant Execute on sp_dropsegment to public go
DEPENDENCIES |
PROCS AND TABLES USED read_writes table sybsystemprocs..syssegments ![]() reads table sybsystemprocs..syspartitions ![]() reads table sybsystemprocs..sysobjects ![]() read_writes table master..sysusages (1) ![]() reads table sybsystemprocs..sysindexes ![]() reads table master..sysmessages (1) ![]() read_writes table tempdb..#temptable (1) calls proc sybsystemprocs..sp_getmessage ![]() reads table master..syslanguages (1) ![]() reads table master..sysmessages (1) ![]() calls proc sybsystemprocs..sp_validlang ![]() reads table master..syslanguages (1) ![]() reads table sybsystemprocs..sysusermessages ![]() reads table master..sysdevices (1) ![]() reads table master..spt_values (1) ![]() reads table master..sysdatabases (1) ![]() writes table sybsystemprocs..systhresholds ![]() |