ASE Database forum - Need a simple threshold example

Author

Need a simple threshold example

Sal Giannone

2005-03-31, 8:04 pm

Hi, does anyone have a simple threshold that executes when
the data part of the database is about 80% full. It could
either report the message in the log or send an email...

thanks

Ray DiMarcello

2005-04-01, 7:02 am

Not handy. But there's one in the documentation.

<Sal Giannone> wrote in message news:424c52de.6edd.1681692777@sybase.com...
> Hi, does anyone have a simple threshold that executes when
> the data part of the database is about 80% full. It could
> either report the message in the log or send an email...
>
> thanks

mpeppler@peppler.org

2005-04-01, 7:02 am

> Hi, does anyone have a simple threshold that executes when
> the data part of the database is about 80% full. It could
> either report the message in the log or send an email...

Here's one that I use. It reports the threshold execution in
the log, and does a dump tran as necessary.

Michael

create procedure sp_thresholdaction
@dbname varchar(30),
@segmentname varchar(30),
@free_space int,
@status int
as
declare @devname varchar(100),
@before_size int,
@after_size int,
@before_time datetime,
@after_time datetime,
@error int,
@env varchar(20),
@time varchar(10)

if exists (select 1 from master..sysprocesses where
db_name(dbid) = @dbname
and cmd like 'DUMP TRAN%')
begin
raiserror 99999 "LOG DUMP: A transaction dump for
database '%1!' is still running - aborting this dump",
@dbname
return
end

select @env = lower(substring(@@se
rvername, 1,
charindex('_',@@serv
ername)-1))

/*
** if this is a last-chance threshold, print a LOG FULL msg
** @status is 1 for last-chance thresholds,0 for all others
*/
if (@status&1) = 1
begin
raiserror 99999 "LOG FULL: database '%1!'", @dbname
end

/*
** if the segment is the logsegment, dump the log
** log segment is always "2" in syssegments
*/
if @segmentname = (select name from syssegments
where segment = 2)
begin
/* get the time and log size
** just before the dump starts
*/
select @before_time = getdate(),
@before_size = reserved_pgs(id, doampg)
from sysindexes
where sysindexes.name = "syslogs"

print "LOG DUMP: database '%1!', threshold '%2!'",
@dbname, @free_space

select @time = substring(convert(va
rchar(8),
getdate(), 8), 1, 2) +
substring(convert(va
rchar(8),
getdate(), 8), 4, 2) +
substring(convert(va
rchar(8),
getdate(), 8), 7, 2)

select @devname = "compress::/sybase/backup/" +
@dbname +
'-log-' +
convert(char(8), getdate(), 112) + "_" + @time +
'.cmp'

dump transaction @dbname to @devname
/* error checking */
select @error = @@error
if @error != 0
begin
print "LOG DUMP ERROR: %1!", @error
print "LOG DUMP: dumping %1! with truncate_only",
@dbname
dump tran @dbname with truncate_only
select @error = @@error
if @error != 0
begin
print "LOG DUMP ERROR: %1!", @error
end
end
else
begin
print "LOG DUMPED TO: device '%1!", @devname
end

/* get size of log and time after dump */
select @after_time = getdate(),
@after_size = reserved_pgs(id, doampg)
from sysindexes
where sysindexes.name = "syslogs"

/* print messages to error log */
print "LOG DUMP PAGES: Before: '%1!', After '%2!'",
@before_size, @after_size
print "LOG DUMP TIME: %1!, %2!", @before_time,
@after_time
end /* end of 'if segment = 2' section */
else /* this is a data segment, print a message */
begin
raiserror 99999 "THRESHOLD WARNING: database '%1!',
segment '%2!' at '%3!' pages", @dbname, @segmentname,
@free_space
end

go

Sponsored Links