1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558
|
-- Author : Fabien Celaia
-- Date : 27.6.1999
-- Desc. : managing easily the dump/load processes
if exists(select * from sysobjects where name = "trackDump" and type = "U")
begin
print "Dropping old trackDump table"
drop table trackDump
end
go
-- Author : Fabien Celaia
-- Date : 27.6.1999
-- Desc. : table trackDump, to manage the dumps made by sp__dump
print "Creating new trackDump table"
create table trackDump
(
dbid int not null,
file varchar(255) not null,
type char(1) not null,
error int,
start datetime not null,
stop datetime not null
)
go
print "Creating primary key to trackDump"
create unique clustered index trackDump_unc on trackDump( start, dbid)
go
print "Granting table trackDump"
grant delete on trackDump to oper_role
go
if exists(select * from sysobjects where name = "sp__dump" and type ="P")
begin
print "Dropping old sp__dump stored procedure"
drop proc sp__dump
end
go
print "Creating new sp__dump stored procedure"
go
create proc sp__dump (@dbname varchar(30), @method char(1)=NULL, @pathtodump varchar(200)=NULL)
as
-- Author : Fabien Celaia
-- Date : 27.6.1999
-- Syntax : sp__dump @dbname, {@method}, {@pathtodump}
-- Inputs : @dbname (mandatory) : name of the database to save
-- @method (optional) : NULL => dump database
-- "T" => dump transaction log
-- @pathtodump (optional) : Null => c:\temp (Windows) or /tmp (Unix)
-- : else saved in the given directory
-- Outputs: 0 => succeed
-- -1 => DB doesn't exist
-- -2 => DB needs to be saved before it's log
-- -3 => BS is down
-- -4 => Corrupted database : try to save the last log
begin
declare @fichier varchar(255), @debut datetime, @fin datetime,
declare @err int, @delcmd char(3), @numstripe int, @cpt int
set nocount on
if exists (select @@version where @@version like "%NT%")
/* Unix */
begin
if @pathtodump is null
select @pathtodump = "/tmp"
select @delcmd = "rm"
end
else
/* Dos */
begin
if @pathtodump is null
select @pathtodump = "C:\temp\"
select @delcmd = "del"
end
if not exists(select name from master..sysdatabases where name = @dbname)
begin
print "The database %1! does not exist", @dbname
return -1
end
else
begin
-- Creation du fichier
select @debut = getdate()
select @fichier= @pathtodump+@dbname + "_" + substring(convert(char, @debut, 112), 1,8) +
"_" + substring(convert(char,@debut,8),1,2) + substring(convert(char,@debut,8),4,2)+ substring(convert(char,@debut,8),7,2)
-- Spécifie des stripe devices par bloc de 2Gb
select @numStripe = sum(size/512)/2048, @cpt=1
from master..sysusages
where dbid=db_id(@db_name)
while @cpt < @numStripe
begin
select @fichier = @fichier+ " stripe on " +@fichier+ convert(char(3),@cpt)
select @cpt=@cpt+1
end
-- test si base corrompue : si tel est le cas, sauvegarde du dernier log
if exists (select *
from master..sysdatabases
where name = @dbname
and status2 = status2|256)
begin
print "The database %1!is corrupt : trying to save the last transaction log.", @dbname
dump tran @dbname to @fichier with no_truncate
return (-4)
end
if @method is not null
-- si sauvegarde du journal de transaction
begin
-- test si base deja sauvegardee
if not exists(select * from DBA..trackDump where dbid = db_id(@dbname) and type ="D")
begin
print "Save the full database before the transaction log"
return -2
end
else
begin
select @fichier = @fichier +".tran"
dump tran @dbname to @fichier
select @err = @@error
if @err = 7205
print "Dump failed : BS probably is down."
if @err = 0
print "Backup succeed"
insert into DBA..trackDump (dbid, start, stop, file, type, error)
values (db_id(@dbname), @debut, getdate(), @fichier, "T", @err)
end
end
else
-- sinon sauvegarde de la base de donnees
begin
select @fichier = @fichier+".dmp"
-- Si la base est configuree pour utiliser dbcc checkstorage, l'executer !
dbcc checkstorage(@dbname)
select @err=@@error
if @err <> 0
begin
print "DBCC checkstorage failed for DB %1"`, @dbname
insert into DBA..trackDump (dbid, start, stop, file, type, error)
values (db_id(@dbname), @debut, getdate(), NULL, "C", @err)
end
dump database @dbname to @fichier
select @err = @@error
if @err = 7205
begin
print "Dump failed : BS probably is down."
return -3
end
if @err = 0
print "Backup succeed"
insert into DBA..trackDump (dbid, start, stop, file, type, error)
values (db_id(@dbname), @debut, getdate(), @fichier, "D", @@error)
end
end
return @err
end
go
if exists(select * from sysobjects where name = "sp__dump_fault" and type ="P")
begin
print "Dropping old sp__dump_fault stored procedure"
drop proc sp__dump_fault
end
go
print "Creating new sp__dump_fault stored procedure"
go
create proc sp__dump_fault @dbname varchar(30) = Null
as
-- Author : Fabien Celaia
-- Date : 27.6.1999
-- Syntax : sp__dump_fault @dbname
-- Inputs : @dbname (optional) : name of the database to check
-- Outputs: 0 => succeed
begin
set nocount on
if @dbname is Null
select db_name(t.dbid) DBName, t.start,t.error , m.description
from DBA..trackDump t, master..sysmessages m
where t.error <> 0
and t.error = m.error
group by dbid
order by start
else
select db_name(t.dbid) DBName, t.start,t.error, m.description
from DBA..trackDump t, master..sysmessages m
where t.error <> 0
and t.error = m.error
and t.dbid = db_id(@dbname)
order by start
end
go
if exists(select * from sysobjects where name = "sp__dump_clean" and type ="P")
begin
print "Dropping old sp__dump_clean stored procedure"
drop proc sp__dump_clean
end
go
print "Creating new sp__dump_clean stored procedure"
go
create proc sp__dump_clean (@dbname varchar(30))
as
-- Date : 30.6.1999
-- Syntax : sp__dump_clean @dbname, @time
-- Inputs : @dbname (mandatory) : name of the database to clean
-- Outputs: 0 => succeed
-- -1 => DB doesn't exist
begin
declare @limite datetime, @fich varchar(80), @xpval int, @delcmd varchar(31)
set nocount on
if not exists(select name from master..sysdatabases where name = @dbname)
begin
print "The database %1! does not exist", @dbname
return -1
end
-- test si la configuration permet d'envoyer une commande en ligne
select @xpval = cur.value
from master..sysconfigures cfg, master..syscurconfigs cur
where cfg.config= cur.config
and cfg.name like "%xp_cmdshell%"
exec sp_configure "xp_cmdshell", 0
select @limite=max(start)
from DBA..trackDump
where dbid = db_id(@dbname)
and type="D"
and error=0
declare track_cur cursor
for select file
from DBA..trackDump
where dbid=db_id(@dbname)
and start < @limite
for update
open track_cur
fetch track_cur into @fich
while @@sqlstatus != 2
begin
select @fich = @delcmd +" "+ @fich
exec xp_cmdshell @fich
fetch track_cur into @fich
end
close track_cur
deallocate cursor track_cur
select file "Deleted Files" from DBA..trackDump
where dbid=db_id(@dbname)
and start < @limite
delete DBA..trackDump
where dbid=db_id(@dbname)
and start < @limite
exec sp_configure "xp_cmdshell", @xpval
end
go
if exists(select * from sysobjects where name = "sp__dump_help" and type ="P")
begin
print "Dropping old sp__dump_help stored procedure"
drop proc sp__dump_help
end
go
print "Creating new sp__dump_help stored procedure"
go
create proc sp__dump_help (@dbname varchar(30)=null)
as
-- Date : 8.8.2000
-- Syntax : sp__dump_help : display the list of the interessting dumps and the parameters
-- Inputs : -
-- Outputs:
begin
declare @def varchar(255), @del varchar(30)
if exists(select @@version where @@version like "%NT%")
/* NT */
select @del="del", @def="C:\temp"
else
/* Unix */
select @del="rm", @def="/tmp"
print ""
print "Default Dump device : %1! ", @def
print "OS Command deleting a file : %1! ", @del
print ""
print ""
print "Available dumps"
print "==============="
print ""
select db_name(dbid), start Date,case type when "T" then "Transaction" when "C" then "Checkstorage" else "Database" end "Type",file from trackDump
where error = 0
order by 1,2
end
go
grant execute on sp__dump_help to public
go
if exists(select * from sysobjects where name = "sp__dump_recover" and type ="P")
begin
print "Dropping old sp__dump_recover stored procedure"
drop proc sp__dump_recover
end
go
print "Creating new sp__dump_recover stored procedure"
go
create proc sp__dump_recover (@dbname varchar(30), @time datetime=NULL)
as
-- Author : Fabien Celaia
-- Date : 27.6.1999
-- Syntax : sp__dump_recover @dbname, @time
-- Inputs : @dbname (mandatory) : name of the database to reload
-- @time (optional) : recover till the given time (default = current datetime)
-- Outputs: 0 => succeed
-- -1 => Corrupted database
-- -2 => No valide database dumps exist
-- -3 => BS is down
-- -4 => Database in use
begin
declare @file varchar(255), @err int
set nocount on
if db_name() = @dbname
begin
print "You must exit of the database %1! before loading it. Please, use master."
return (-4)
end
if @time is null
select @time = getdate()
declare dump_cur cursor for
select file
from DBA..trackDump
where stop < @time
and error = 0
and dbid = db_id(@dbname)
and start > ( select max(start)
from DBA..trackDump
where error = 0
and dbid = db_id(@dbname)
and type ="D")
order by start
select @err= count(uid)
from master..sysprocesses
where dbid = db_id(@dbname)
and suid <>0
if @err > 0
begin
select @file= convert(varchar(8), @err)
print "%1! users are currently using %2!. Kill them before executing the load !",@file, @dbname
select u.uid UserID, l.name Login, u.name UserName
from master..sysprocesses p, sysusers u, master..syslogins l
where p.dbid = db_id(@dbname)
and p.suid <>0
and p.suid = l.suid
and u.suid = l.suid
return -4
end
/*cas de la base corrompue*/
if exists (select *
from master..sysdatabases
where name = @dbname
and status2 = status2|256)
begin
print "The database %1!is corrupt : check the errorlog to make it stable the problem before uploading !", @dbname
return (-1)
end
else
begin
select @file = file
from DBA..trackDump
where error = 0
and dbid = db_id(@dbname)
and type ="D"
and start = (select max(start)
from DBA..trackDump
where error = 0
and dbid = db_id(@dbname)
and type ="D")
if @file is null
begin
select @file = convert(char(8), @time, 4)+ " at " +convert(char(8), @time, 8)
print "The database %1! has no valid load before %2!", @dbname, @file
return (-2)
end
else
begin
load database @dbname from @file
select @err = @@error
if @err = 7205
begin
print "Load failed : BS probably is down."
return -3
end
if @err = 0
print "Load succeed"
open dump_cur
fetch dump_cur into @file
while @@sqlstatus <> 2
begin
load tran @dbname from @file
select @err = @@error
if @err = 7205
begin
print "Load failed : BS probably is down."
return -3
end
if @err = 0
print "Load succeed"
fetch dump_cur into @file
end
deallocate cursor dump_cur
end
online database @dbname
end
return @err
end
go
print "Granting sp__dump... stored procedures"
grant execute on sp__dump_recover to oper_role
grant execute on sp__dump to oper_role
grant execute on sp__dump_clean to oper_role
go
print "Creating new sp__kill stored procedure"
go
create proc sp__kill (@dbname varchar(30))
as
begin
set nocount on
declare @snum varchar(5)
declare ckill cursor for select convert(char(5),spid) from master..sysprocesses where dbid=db_id(@dbname)
open ckill
fetch ckill into @snum
while @@sqlstatus <> 2
begin
print "Killing process %1!",@snum
exec ("kill " + @snum)
fetch ckill into @snum
end
end
go
grant execute on sp__kill to oper_role , sa_role, sso_role
use sybsystemprocs
go
if exists(select name from sysobjects where name = "sp_thresholdaction" and type = "P")
begin
declare @old varchar(30), @debut datetime
select @debut = getdate()
select @old = "sp_threshold_"+substring(convert(char, @debut, 112), 1,8) +
"_" + substring(convert(char,@debut,8),1,2) + substring(convert(char,@debut,8),4,2)
print "Renaming old sp_thresholdaction to %1!", @old
exec sp_rename sp_thresholdaction, @old
end
go
print "Creating new sp_thresholdaction stored procedure"
go
create procedure sp_thresholdaction
@dbname varchar(30),
@segmentname varchar(30),
@space_left int,
@status int
as
begin
declare @taille_avant decimal(7,2),
@taille_apres decimal(7,2),
@err int,
@ficname varchar(128),
@repname varchar(128),
@dumpname varchar(255),
@datet datetime
/* Tronque les decimales superflus */
set arithabort numeric_truncation off
/* journal de transactions (debut) */
if @segmentname = ( select name from syssegments where segment = 2 )
begin
/* Recupere l'espace occupe dans le journal avant la sauvegarde */
select @taille_avant = (reserved_pgs(id, doampg) + reserved_pgs(id, ioampg))/1024.0
from sysindexes
where name = "syslogs"
/* Effectue la sauvegarde du journal + purge */
exec DBA..sp__dump @dbname, "T"
/* Controle d'erreur */
select @err = @@error
/* Si Erreur sur dump tran to file */
if @err != 0
begin
print "Impossible de sauvegarder le journal de la base
'%1!'. (Error Nr '%2!')", @dbname, @err
return
end
/* Recupere l'espace occupe dans le journal apres la sauvegarde */
select @taille_apres = (reserved_pgs(id, doampg) + reserved_pgs(id, ioampg))/1024.0
from sysindexes
where name = "syslogs"
end
else
begin
print "BE CAREFULL: THE DATABASE %1! IS FILLING UP !!!", @dbname
print "Available : %1! (2Kb)", @space_left
end
end
go
print "Granting sp_thresholdaction"
grant execute on sp_thresholdaction to public
go |
Partager