Monitoring Use Case

sp_xact_loginfo can be used for periodically monitoring the longest running transaction per database.

For example, a stored procedure can be formed around sp_xact_loginfo in which it populates the tables with the oldest active transaction information and populates a user defined table. The execution of this stored procedure can be periodic, at desired frequency 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( 
**           dbid                     int, 
**           oldestactivexact_span    int,
**           startxactpagenum         int,
**           spid                     int,
**           xactstarttime            varchar(27),
**           startlogpagenum          int,
**           stppage                  bigint,
**           sec_truncpoint_block     int,
**           can_free_wo_kill         int,
**           dump_in_progress         int,
**           nolog                    int,        
**           username                 varchar(30) null)
*/
create procedure sp_record_oldestactivexact
@dbname	varchar(30)
as
declare     @dbid int,
            @oats_span_pct int,  
            @oat_startpage bigint,
            @firstlog_page bigint,
            @canfree_withoutabort int,
            @stp_blocking int,
            @stp_page bigint,
            @dump_in_progress int,
            @oat_spid int,
            @oat_starttime varchar(27),
            @activexact int,
            @free_with_dumptran_pct int,
            @errorcode int,			
            @username varchar(30)

select @dbid = db_id(@dbname)

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 (@activexact = 1)
    begin
        print "activexact is true"
        select @username = suser_name(sysproc.uid) 
                from master..systransactions systran, 
                master..sysprocesses sysproc 
                      where systran.spid = @oat_spid 
                and systran.spid = sysproc.spid
        insert into oldest_active_xact 
            values(
                @dbid,
                @oats_span_pct,
                @oat_startpage,
                @oat_spid,
                @oat_starttime,
                @firstlog_page,
                @stp_page,
                @stp_blocking,
                @free_with_dumptran_pct,
                @dump_in_progress,
                @activexact,@username)
    end
else
    begin
        print "activexact is false"	
        insert into oldest_active_xact values(
                @dbid,
                @oats_span_pct,
                @oat_startpage,
                @oat_spid,getdate(),
                @firstlog_page,
                @stp_page,
                @stp_blocking,
                @free_with_dumptran_pct,
                @dump_in_progress,
                @activexact,NULL)
end