O'Reilly logo

Guru's Guide to SQL Server Architecture and Internals, The by Ken Henderson

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required