Finding Transactions After A Crash

Paul Randal has a procedure which will find rolled-back transactions after a crash:

Then we can search in the transaction log, using the fn_dblog function, for LOP_BEGIN_XACT log records from before the crash point that have a matching LOP_ABORT_XACT log record after the crash point, and with the same transaction ID. This is easy because for LOP_BEGIN_XACT log records, there’s a Begin Time column, and for LOP_ABORT_XACT log records (and, incidentally, for LOP_COMMIT_XACT log records), there’s an End Time column in the TVF output.

And there’s a trick you need to use: to get the fn_dblog function to read log records from before the log clears (by the checkpoints that crash recovery does, in the simple recovery model, or by log backups, in other recovery models), you need to enable trace flag 2537. Now, if do all this too long after crash recovery runs, the log may have overwritten itself and so you won’t be able to get the info you need, but if you’re taking log backups, you could restore a copy of the database to the point just after crash recovery has finished, and then do the investigation.

Read on for the code, as well as a test.

Related Posts

New(ish) VLF Status: 4

Paul Randal points out a new VLF status which can appear if you’re using an Availability Group: At least since I started working on the SQL Server team (just after 7.0 shipped) and since then there have only been two VLF status codes: 0 = the VLF is not active (i.e. it can be (re)activated […]

Read More

The Problems With NOLOCK

Rob Farley demonstrates the downside of the READ UNCOMMITTED isolation level: I’m going to create a table and insert exactly 1 million rows. This particular table will be a clustered index, and will contain 1 million GUIDs. 1 2 CREATE TABLE dbo.demoNOLOCK (someguid uniqueidentifier NOT NULL PRIMARY KEY); INSERT dbo.demoNOLOCK (someguid) SELECT TOP (1000000) NEWID() […]

Read More

Categories

January 2017
MTWTFSS
« Dec Feb »
 1
2345678
9101112131415
16171819202122
23242526272829
3031