Using lct_admin abort to Abort Suspended Transactions

All open transactions are suspended when the transaction log reaches the last-chance threshold.

Typically, space is created by dumping the transaction log, since this removes committed transactions from the beginning of the log. However, if one or more transactions at the beginning of the log is still open, it prevents a dump of the transaction log.

Use lct_admin abort to terminate suspended transactions that are preventing a transaction log dump. Since terminating a transaction closes it, this allows the dump to proceed. This figure illustrates a possible scenario for using lct_admin abort. A transaction log has reached its LCT, and open transactions T1 and T6 are suspended. Because T1 is at the beginning of the log, it prevents a dump from removing closed transactions T2 through T5 and creating space for continued logging. Terminating T1 with lct_admin abort allows you to close T1 so that a dump can clear transactions T1 through T5 from the log

Before you can abort a transaction, you must first determine its ID.

1. 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

2. To terminate the oldest transaction, enter the process ID (spid) of the process that initiated the transaction, which also terminates any other suspended transactions in the log that belong to the specified process.
   For example, if process 83 holds the oldest open transaction in a suspended log, and you want to terminate the transaction, enter:

   select lct_admin("abort", 83)

   This also terminates any other open transactions belonging to process 83 in the same transaction log.

   To terminate all open transactions in the log, enter:

   select lct_admin("abort", 0, 12)

   See the Reference Manual: Commands.