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
  

Copyright © 2005. Sybase Inc. All rights reserved.

View this book as PDF