sp_thresholdaction can be used to identify the oldest active transaction and decide on action based on the information returned.
You can truncate the log to free up the space or abort the oldest active transaction or both based on the defined criteria. This use case assumes that the oldest active transaction span needs to be watched or limited only when free space in log segment falls beyond threshold.
create procedure sp_logthresholdaction
@dbname varchar(30),
@segmentname varchar(30),
@space_left int,
@status int
as
declare
@oats_span_pct int,
@dbid int,
@at_startpage bigint,
@firstlog_page bigint,
@canfree_withoutabort int,
@stp_blocking int,
@stp_pagebig int,
@dump_in_progress int,
@oat_spid int,
@oat_starttime datetime,
@activexact int,
@free_with_dumptran_pct int,
@errorcode int,
@xactspan_limit int,
@space_left_pct int,
@dumptrancmd varchar(256),
@dumpdevice varchar(128),
@killcmd varchar(128)
select @dbid = db_id(@dbname)
select @dumpdevice = “/ferrum_dev1/db1.tran.dmp1”
select @free_with_dumptran_pct = 5
/*
** attempt dump tran only if it can free at
** least 5 percent of log space
*/
select @xactspan_limit = 20
/*
** optionally also kill oldest active transaction even after
** dump tran if its exceeds 20 percent in the log
*/
select @space_left_pct = logspace_pagestopct(@dbid, @space_left, 0)
print “Space left in log segment “ + @space_left_pct + “ percent.”
select @dump_in_progress = 1
while (@dump_in_progress> 0)
begin -- {
exec sp_xact_loginfo@dbid,
“oldestactive”,
NULL,
0,
0,
@span_pct = @oats_span_pct output,
@startpage = @oat_startpage output,
@xact_spid = @oat_spid output,
@starttime = @oat_starttime output,
@firstlog_page = @firstlog_page output,
@stp_page = @stp_page output,
@stp_blocking = @stp_blocking output,
@canfree_without_abort_pct = @free_with_dumptran_pct output,
@dump_in_progress = @dump_in_progress output,
@activexact = @activexact output,
@errorcode = @errorcode output
if (@dump_in_progress> 0)
begin
sleep 30
continue
end
select @killcmd = “kill “ + @xact_spid + “ with status_only”
if (@canfree_withoutabort>@free_with_dumptran)
begin
select @dumptrancmd = “dump tran ” + @dbname + “ on “ + @dumpdevice
exec(@dumptrancmd)
/*
** Optionally, also abort oldest active transaction.
*/
if ((@stp_blocking = 0) and
(@oats_span_pct> @xactspan_limit))
then
/*
** Some diagnostic information can be printed or warning actions
** can be executed here before aborting the transaction.
*/
exec(@killcmd)
end
else
/*
** Some diagnostic information can be printed or warning actions
** can be executed here before aborting the transaction.
*/
exec(@killcmd)
end
end -- }