Rescue Scenario Use Case

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