Getting the process ID for the oldest open transaction.

Getting the process ID for the oldest open transaction

Use the following query to find the spid of the oldest open transaction in a transaction log that has reached its last-chance threshold:

use master

go

select dbid, spid from syslogshold

where dbid = db_id("name_of_database")

For example, to find the oldest running transaction on the pubs2 database:

select dbid, spid from syslogshold

where dbid = db_id ("pubs2")

dbid   spid

------ ------

    7      1