Monitoring and Control Use Case

In addition to monitoring, the action of aborting the oldest active transaction based on conditional criteria can also be implemented in sp_xact_loginfo which is run periodically through job scheduler.

/*
** Stored procedure assumes presence of a pre-created table for monitoring 
** oldest active transactions with following table definition:
**
**	create table oldest_active_xact(datetimetime_of_recording,
**           dbid                     int, 
**           oldestactivexact_span    int,
**           spid                     int,
**           username                 varchar(30),
**           startxactpagenum         int,
**           startlogpagenum          int,
**           xactstarttime            datetime,
**           can_free_wo_kill         int,
**           sec_truncpoint_block     int,
**           nolog                    int,
**           action_taken             varchar(30))        
**           lock datarows                  
*/
create procedure sp_control_oldestactivexact
		   @dbname	varchar(30)
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,			
           @username                varchar(30),
           @action_taken            varchar(30),
           @xact_maxspan_pct        int,
           @killcmd                 varchar(128)

	select @dbid = db_id(@dbname)
	select @xact_maxspan_pct = 20
	select @action_taken = “none”

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

	select @killcmd = “kill “ + @oldesactive_spid + “ with status_only”

	if (@nolog == 0)
	then
select @username = suser_name(systran.suid) 
from master..systransactionssystran where systran.spid =@oldestactive_spid
if (@oats_span_pct> @xact_maxspan_pct)
begin
	exec(@killcmd)
	select @action_taken = “transaction abort”
end
		insert into oldest_active_xact values(getdate(), @dbid, 
@oats_span_pct, @oat_spid, @username, @oat_page, @firstlog_page, 
@free_with_dumptran_pct, @stp_blocking, @activexact, @action_taken)
	else
		/*
		** Just to cover possibility of no active transactions which have
		** generated any log.
		*/
		insert into oldest_active_xact values(getdate(), @dbid,
					0, 0, NULL, 0, 0, 0, 0, 1, @action_taken)
	end