Debugging Transactions

Two DBCC commands come in very handy when debugging transaction-related problems. The first is DBCC OPENTRAN. It allows you to retrieve the oldest active transaction in a database. Since only the inactive portion of a log is backed up and truncated, a malevolent or zombie transaction can cause the log to fill prematurely. You can use DBCC OPENTRAN to identify the offending process so that it may be terminated if necessary. Listing 13.12 shows an example.

Listing 13.12. Using DBCC OPENTRAN
DBCC OPENTRAN(pubs)
Transaction information for database 'pubs'.

Oldest active transaction:
    SPID (server process ID) : 15
    UID (user ID) : 1
    Name          : user_transaction
    LSN           : (57:376:596)
    Start time    : Aug  5 1999  5:54:46:713AM

Another handy ...

Get Guru's Guide to SQL Server Architecture and Internals, The now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial