motioneye
asked on
Sybase Customized sp_thresholdaction
We have a customized script below for. I wonder if the script below also doing a log dump for several db we have in the same instance ? for example it define dbccdb and sybsecurity to perform dum transaction with truncate only. But how do I add few more db to do same dum_transaction with truncate_only in this procedure
create procedure sp_thresholdaction
@dbname varchar(30),
@segmentname varchar(30),
@space_left int,
@status int
as
declare @devname varchar(100),
@before_size int,
@after_size int,
@dumpfilename varchar(100) ,
@logdumpname varchar(100) ,
@after_time datetime,
@before_time datetime,
@atime char(10),
@btime
char(10),
@error int,
@nextslash int,
@dev varchar(100),
@dumpdir varchar(100),
@cuttime datetime,
@audit_table_number int
/*************************************************************
*************
**************************************************************************/
/*
*/
select @before_time = getdate()
select @dumpfilename = "XXX_1_SDS" + @dbname + ".tran." +
convert(char(6), @before_time, 12)
+
substring(convert(char(6), @before_time, 8),1,2) +
substring(convert(char(8), @before_time, 8),4,2) +
substring(convert(char(8), @before_time, 8),7,2) +
"--THRESHOLD"
select @logdumpname = @dbname + ".tran." +
convert(char(6), @before_time, 12) +
substring(convert(char(6), @before_time, 8),1,2) +
substring(convert(char(8), @before_time, 8),4,2) +
substring(convert(char(8), @before_time, 8),7,2) +
"--THRESHOLD"
/*
** If the segment is 2 in syssegments it is the logsegment, dump the log
** otherwise it is a data segment, print a message
*/
if exists (select * from syssegments where segment != 2 and name = @segmentname)
begin
print "%1!: Threshold Warning: Segment '%2!' has reached '%3!' pages free"
, @logdumpname, @segmentname, @space_left
if @dbname = "dbccdb"
begin
print 'Truncating dbccdb due to threshold'
dump tran dbccdb with truncate_only
select @cuttime = dateadd(dd,-9,getdate())
execute sp_dbcc_deletehistory @cuttime
dump tran dbccdb with truncate_only
select @cuttime = dateadd(dd,-6,getdate())
execute sp_
dbcc_deletehistory @cuttime
dump tran dbccdb with truncate_only
select @cuttime = dateadd(dd,-3,getdate())
execute sp_dbcc_deletehistory @cuttime
dump tran dbccdb with truncate_only
select @cuttime = dateadd(dd,-1,getdate()
)
execute sp_dbcc_deletehistory @cuttime
end
/*
if @dbname = "sybsecurity"
begin
select @audit_table_number = value
from master.dbo.sysconfigures
where name = "current audit table"
if @audit_table_number = 1
tru
ncate table sysaudits_01
if @audit_table_number = 2
truncate table sysaudits_02
if @audit_table_number = 3
truncate table sysaudits_03
end
*/
end
else
begin
if (@space_left > 0)
print "%1!: Logspace left in database '%2!'", @log
dumpname, @space_left
/*
** Get the log size just before the dump starts
*/
select @before_size = reserved_pgs(id,doampg)
from sysindexes
where name = "syslogs"
/* Try AUTO = DBNAMEdump */
select @devname = phyname
from master..sysdevices
where name = @dbname + "dump"
select @error= @@rowcount - 1
if (@error = 0)
begin
/* All these rows to get the dumpdirectory */
select @dev = @devname
select @nextslash = 0
select @nextslash = @nexts
lash + charindex("/", @dev)
while ((select charindex("/", @dev)) != 0)
begin
select @dumpdir = substring (@devname, 1, @nextslash - 1)
select @dev = substring (@devname, @nextslash + 1, char_length(@dev))
select @ne
xtslash = @nextslash + charindex("/", @dev)
end
/* Get dumpdevice, dump to dumpdevice, print if went wrong */
select @devname = @dumpdir + "/" + @logdumpname
dump transaction @dbname to @devname
select @error=@@error
i
f (@error != 0)
print "%1!: Failed to dump to '%2!'", @logdumpname, @devname
end
/* Try THRESHOLDDUMP */
if (@error != 0)
begin
select @devname = phyname
from master..sysdevices
where name = "thresholddump"
select @error= @@rowcount - 1
if @error = 0
begin
/* All these rows to get the dumpdirectory */
select @dev = @devname
select @nextslash = 0
select @nextslash = @nextslash + charindex("/", @dev)
while ((select charindex("/", @dev)) != 0)
begin
select @dumpdir = substring (@devname, 1, @nextslash - 1)
select @dev = substring (@devname, @nextslash + 1, char_length(@dev))
select @nextslash = @nextslash
+ charindex("/", @dev)
end
/* Get dumpdevice, dump to dumpdevice, print if went wrong */
select @devname = @dumpdir + "/" + @logdumpname
dump transaction @dbname to @devname
select @error=@@error
if (@error !=
0)
print "%1!: Failed to dump to '%2!'", @logdumpname, @devname
end
end
/* Try DEFAULTDUMP */
if (@error != 0)
begin
select @devname = phyname
from master..sysdevices
where name = "defaultdump"
s
elect @error= @@rowcount - 1
if @error = 0
begin
/* All these rows to get the dumpdirectory */
select @dev = @devname
select @nextslash = 0
select @nextslash = @nextslash + charindex("/", @dev)
while ((select charindex
("/", @dev)) != 0)
begin
select @dumpdir = substring (@devname, 1, @nextslash - 1)
select @dev = substring (@devname, @nextslash + 1, char_length(@dev))
select @nextslash = @nextslash + charindex("/", @dev)
end
/* Get dumpdevice, dump to dumpdevice, print if went wrong */
select @devname = @dumpdir + "/" + @logdumpname
dump transaction @dbname to @devname
select @error=@@error
if (@error != 0)
print "%1!: Failed to dump to '%2!'", @
logdumpname, @devname
end
end
/* Try DPDUMP */
if (@error != 0)
begin
select @devname = phyname
from master..sysdevices
where name = "dp_dump"
select @error= @@rowcount - 1
if @error = 0
begin
dump transaction @dbname to @devname
select @error=@@error
if (@error != 0)
print "%1!: Failed to dump to '%2!'", @logdumpname, @devname
end
end
/* Try BACKUPDUMP */
if (@error != 0)
begin
sele
ct @devname = phyname
from master..sysdevices
where name = "backupdump"
select @error= @@rowcount - 1
if @error = 0
begin
/* All these rows to get the dumpdirectory */
select @dev = @devname
select @nextsla
sh = 0
select @nextslash = @nextslash + charindex("/", @dev)
while ((select charindex("/", @dev)) != 0)
begin
select @dumpdir = substring (@devname, 1, @nextslash - 1)
select @dev = substring (@devname, @nextslash + 1, char_length(@dev
))
select @nextslash = @nextslash + charindex("/", @dev)
end
/* Get dumpdevice, dump to dumpdevice, print if went wrong */
select @devname = @dumpdir + "/" + @logdumpname
dump transaction @dbname to @devname
sel
ect @error=@@error
if (@error != 0)
print "%1!: Failed to dump to '%2!'", @logdumpname, @devname
end
end
/* Truncate log and write what failed */
if (@error != 0)
begin
select @devname = "waste basket"
dump transaction @dbname with truncate_only
select @error=@@error
if (@error != 0)
print "%1!: Log Dump Error : Could not truncate with truncate only", @logdumpname
else
print "%1!: Log Dump Error : Truncated log with t
runcate only", @logdumpname
end
if (@error != 0)
begin
print "%1!: Log Dump Error : Truncating log with no_log since syslogs full", @logdumpname
dump transaction @dbname with no_log
select @error=@@error
if (@error != 0)
begin
print "%1!: Log Dump Error : Failed truncating log with no_log", @logdumpname
end
end
if (@error != 0)
begin
print "%1!: Log Dump Error : Could not dump transaction log", @logdumpname
end
/* Error check
ing */
select @error = @@error
if @error != 0
begin
print "%1!: Log Dump Error '%2!'", @logdumpname, @error
end
/* Get size of log and time after dump */
select @after_time = getdate(),
@after_size = reserved_pgs (id,
doampg)
from sysindexes
where name = "syslogs"
/* Print messages to error log */
print "%1!: Log dumped to device '%2!'", @logdumpname, @devname
print "%1!: Log dump sizes: Before: '%2!', After '%3!'",
@logdumpname, @before_siz
e, @after_size
select @btime = convert(char(10), @before_time, 8)
select @atime = convert(char(10), @after_time, 8)
print "%1!: Log dump times: %2!, %3!", @logdumpname, @btime, @atime
end /* End of 'if segment = 2' section */
(39 rows affected)
(return status = 0)
1>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER