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)
|
|
