Paul S.
Randal
Recovering a Database
with a Missing
Transaction Log
We had a SAN problem over the weekend. One of our main databases
was shut down with open transactions, and we lost the log file. The
last working backup is two weeks old. Is there any way to recover
the database without having to resort to using the old backup?
Kimberly
L. Tripp
Paul S. Randal and Kimberly
L. Tripp are a husband-and-wife team who own and run
SQLskills.com, a world-renowned SQL Server
consulting and training
company. They’re both SQL
Server MVPs and Microsoft
regional directors, with more
than 30 years of combined
SQL Server experience.
Paul
Kimberly
Website
Yes, but not without consequences. Usually when a database has open
transactions and the server crashes, crash recovery will run on the
affected database and roll back the open transactions. This prevents
the effects of partially completed transactions from being present in the
database. If the transaction log isn’t available when SQL Server starts,
the database will be in the SUSPECT state.
In this case, the only way to bring the database online (note that I’m
not saying “the only way to make the database usable”) is to use the
emergency mode repair functionality that was added in SQL Server
2005. This basically builds a new transaction log, then runs the DBCC
CHECKDB command with the REPAIR_ALLOW_DATA_LOSS argu-
ment. You can read more about this in my blog post “EMERGENCY-
mode repair: the very, very last resort.”
The problem you’ll have if you decide to go this route is that the
emergency mode repair can’t roll back any of the active transactions,
because it has no knowledge of what they were—because the trans-
action log was destroyed. This means that, at best, the resulting data-
base will be transactionally inconsistent (i.e., the state of the data in
the database is unknown).
For example, there might have been a transaction that was updating some sales records in a table and only half of them were updated
