The following threshold procedure performs different actions, depending on the value of the parameters passed to it. Its conditional logic allows it to be used with both log and data segments.
The procedure:
Prints a “LOG FULL” message if the procedure was called as the result of reaching the log’s last-chance threshold. The status bit is 1 for the last-chance threshold, and 0 for all other thresholds. The test if (@status&1) = 1 returns a value of true only for the last-chance threshold.
Verifies that the segment name provided is the log segment. The segment ID for the log segment is always 2, even if the name has been changed.
Prints “before” and “after” size information on the transaction log. If a log does not shrink significantly, it may indicate that a long-running transaction is causing the log to fill.
Prints the time the transaction log dump started and stopped, helping gather data about dump durations.
Prints a message in the error log if the threshold is not on the log segment. The message gives the database name, the segment name, and the threshold size, letting you know that the data segment of a database is filling up.
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,
@before_time datetime,
@after_time datetime,
@error int
/*
** 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
print "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_pages(db_id(), object_id("syslogs"))
print "LOG DUMP: database ’%1!’, threshold ’%2!’",
@dbname, @space_left
select @devname = "/backup/" + @dbname + "_" +
convert(char(8), getdate(),4) + "_" +
convert(char(8), getdate(), 8)
dump transaction @dbname to @devname
/* error checking */
select @error = @@error
if @error != 0
begin
print "LOG DUMP ERROR: %1!", @error
end
/* get size of log and time after dump */
select @after_time = getdate(),
@after_size = reserved_pages(db_id(), object_id("syslogs"))
/* print messages to error log */
print "LOG DUMPED TO: device ’%1!", @devname
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
print "THRESHOLD WARNING: database ’%1!’, segment ’%2!’ at ’%3!’ pages", @dbname, @segmentname, @space_left
end
