INF: Determining the Oldest Open Transaction

  - Microsoft SQL Server version 4.2 for OS/2
  - Microsoft SQL Server, versions 4.2 and 6.0

SUMMARY

There are many reasons why you would need to determine what the oldest open transaction is and who is holding it open. It could be that the transaction log is not being truncated as expected, leading you to believe that there is an open transaction preventing truncation. Or, you could be running into significant blocking (or livelocks) that is preventing updates to the database. Viewing the information in the oldest open transaction can provide information on which user is holding the open transaction, as well as what time the transaction was begun.

For more information on the transaction log filling up, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q110139
   TITLE: INF: Causes of SQL Transaction Log Filling Up

   use <database name>
   go
   select first from sysindexes where id = 8
   dump tran <database name> with truncate_only
   select first from sysindexes where id = 8
   go

MORE INFORMATION

The following steps will allow you to examine the oldest open transaction in SQL Server. The following steps must be run by the SA.

1. First, determine the database ID of the database you are working on, send trace output to the client, and force a checkpoint record to be written out. This will ensure we are dealing with the most recent (and up-to-date) checkpoint record:

         use <database name>
         go
         select db_id()
         checkpoint
         dbcc traceon(3604)
         go
   

2. Run the following command, replacing the <dbid> with the database ID found in step 1:

         dbcc log(<dbid>,0,0,0,-1,17,0)
         go
   

   This will print out the last checkpoint written to syslogs. Find the section that looks like "active xacts:(449, 14)". This is the page and row number of the oldest open transaction. Although it is theoretically possible to have about a hundred log rows on a page, if the second number is over 40, it is likely there are no open transactions.

3. Now retrieve the begin transaction information:

         dbcc log(<dbid>,1,<page>,<row>,0,0,0)
         go
   

   From the example in #2, the command would be (for the pubs database):

         dbcc log(4,1,449,14,0,0,0)
         go
   

   If this command prints out no information, or you receive an error such as:

         Invalid transaction id: 449,14
   

   then, it is possible that there are no active transactions. The field of interest here is the "spid=<number>". This is the spid (as seen in sp_who) of the process that created this transaction.

4. You should now confirm that there is an open transaction. The following command will try to retrieve the end of the transaction:

         dbcc log(<dbid>,1,<page>,<row>,0,30,0)
         go
   

   If this command does not return any information, then you have found the oldest open transaction. You can identify the user by running sp_who and matching the "spid" values from #3 and sp_who. If it does return information about the end of the transaction, follow this procedure one more time to ensure that the values were input correctly.

5. The last bit of information is to determine if the log has been truncated up to the oldest transaction. Run the following command:

         select first from sysindexes where id = 8
         go
   

   If the value for first is the same as the page number from step #2, then the log is truncated as small as it can go until the transaction is committed or aborted.