sp_thresholdaction fires when the last chance threshold (LCT) is reached in each database (~95% log full). If fired, the transaction log can be saved as a conventional trans_dump file (as we do on dali) or the transaction log can be truncated (as we do on peter). Crimson and prod do not have a LCT in place either because it isn't supported in older versions of Sybase or because the databases are laid out w/ data and log on the same segment, thus precluding the ability of the LCT to fire correctly.

The code for the various LCTs we have is in max:/export/syb_ops/sql/sp_thresholdaction.*.sql

This is dali's LCT, which links up with a SQL backtrack openserver, which allows up to keep our transaction chain intact for that database. When it fires, a line is written to the errorlog containing key word "LCT," which is read by swatch and subsequently notifies the email recipeients of the event.

Here's the text of the stored procedure:

use sybsystemprocs
go

if exists (select * from sysobjects where name = "sp_thresholdaction"
           and type = 'P')
   drop proc sp_thresholdaction
go

create procedure sp_thresholdaction
  @dbname varchar(30),
  @segmentname varchar(30),
  @space_left int,
  @status int
as

declare @controlfile varchar(100)
select @controlfile = "/export/datatools/trans_dump/prod11/"+@dbname

exec backtrack_open_srv...dtsbackup @controlfile, "-log_only"

print "LCT fired through backtrack_open_srv on '%1!'", @dbname
go

Other servers replace the "exec" line with a dump tran [dbname] with truncate_only line. See the code for examples.

One note; creating a sp_thresholdaction procedure in the sybsystemprocs database of a SQL server makes that code active for ALL databases in the server. If however you have a need to have a different LCT for one particular database, you may create the procedure in that database, and Sybase will execute the local LCT instead. Similarly, this same logic allows you to not have a server-wide LCT but have one at a database level. Just be careful as you create sp_thresholdaction stored procedures all over the place that you remember where they are; else tracking down a failed LCT could be painful.