Monday 11 April 2011

ASE : Setting the thresholds

Threshold settings allow customized procedures to be run when database segments
approach a defined capacity.

The "last chance threshold" is set by default, to execute sp_thresholdaction within the
current database, when a segment reaches 95% of capacity. The procedure sp_thresholdaction
needs to be created by the DBA. Here is a sample:

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

declare @msg varchar(80),
@date1 datetime,
@fname varchar(80),
@fdate varchar(20),
@fpath varchar(40)

select @fpath = '/usr/dumps/logs/'

select @date1 = getdate()

select @fdate =
convert(varchar(2),datepart(MM,@date1)) +
convert(varchar(2),datepart(DD,@date1)) +
convert(varchar(2),datepart(HH,@date1)) +
convert(varchar(2),datepart(MI,@date1))

select @fname = @fpath + 'log_' + @dbname + @fdate + '.dmp'

select @msg = '***!! Last Chance Threshold reached, for ' + @dbname + '(' + @segmentname + ')'

print @msg

if @segmentname = 'logsegment'
dump tran @dbname to @fname

return



Other threshold levels can be created, for specific segments. They can be set up
to print informational messages to the error log, as a forewarning to the DBA. Here's a
sample which reflects the command syntax:

1> sp_addthreshold dbname,logsegment,400,'proc_log_threshold'
2> go
Adding threshold for segment 'logsegment' at '400' pages.
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
(return status = 0)

No comments:

Post a Comment