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 |
21/04/2008, 13h49
[ASE]Documentation sur la gestion des journaux ?
00
Fabien Celaia
Mes articles
Attention : pas de réponse technique par MP : pensez aux autres, passez par les forums !
Je cherche surtout des "strategies" deja mises en pratique et fiables pour les decliner ici.
