Chapter 15: Managing Free Space with Thresholds

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