A more complex procedure

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 the log did not shrink significantly, a long-running transaction may be 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_pgs(id, doampg)
      from sysindexes
      where sysindexes.name = "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_pgs(id, doampg) 
           from sysindexes 
           where sysindexes.name = "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