Viewing the Oldest Active Transactions

The system administrator can view the processes that are using the most log space.

This example shows the top three oldest active transactions having longest spans in the transaction log:

select top 3 convert(numeric(3,0), 
      loginfo(db_id(), ‘xactspanbyspid’, t.spid)) as XACTSPAN, 
          convert(char(4), t.spid) as SPID,   
          convert(char(20), t.starttime) as STARTTIME,   
          convert(char(4), p.suid) as SUID,   
          convert(char(15), p.program_name) as PROGNAME,   
          convert(char(15), p.cmd) as COMMAND,   
          convert(char(16), p.hostname) as HOSTNAME,   
          convert(char(16), p.hostprocess) as HOSTPROCESS   
from master..systransactions t, master..sysprocesses p   
where t.spid = p.spid   
order by XACTSPAN desc

XACTSPAN SPID     STARTTIME     SUID PROGRAM COMMMAND  HOSTNAME  HOSTPROCESS
-------- ---- ----------------- ---- ------- -------- ---------- -----------
  38      19  Aug 5 2013 12:20AM  1    ISQL   WAITFOR linuxstore4  26141          
  20      20  Aug 5 2013 12:20AM  1    ISQL   WAITFOR linuxstore2  23467          
  10      21  Aug 5 2013 12:21AM  1    ISQL   WAITFOR linuxstore6  4971           
(return status =0)