My two cents

Some days ago, my boss got on my desk asking if I could take a look at one database that was not opening. That DB was in a virtual machine used for our sales presentations.

For some reason, several files got corrupted in that VM, and between those was our beloved transaction log file.

It was a perfect opportunity to sharpen my database recovery skills, there was quite some time that I didn’t played with those tools and to be honest I had forgot almost everything.

So, as I will probably forget about it soon (thankfully, SQL Server does not crash that often), and as there is quite some time that I don’t post anything here, I think it will be good to blog about it.

The error recorded in the log was:

Error: 9004
An error occurred while processing the log for database AnyDB.

I love clear messages like that. This helps a lot to identify what caused the problem, right? No other meaningful error on SQL Server Errorlog nor in EventViewer.

A quick look at google confirmed my suspicion:

Time to restore from our backups.

To be honest, this is what I would do in any critical database, but where is the fun it that? :-)

So I went for the not recommended neither supported procedure in SQL Server 2000, which is:

1 - Enable updates to system tables

EXEC sp_configure 'allow updates',1

RECONFIGURE WITH OVERRIDE

2 - Save the current status for your database

SELECT dbid, name, status

FROM sysdatabases

WHERE dbname = 'test'

3 - Set it to “Emergency” mode (Status = 32768)

UPDATE sysdatabases

SET status = 32768

WHERE dbname = 'test'

4 - Recreate an empty log

DBCC REBUILD_LOG (test,'<PATH>\test.ldf')

5 - Check if your database is physically ok

ALTER DATABASE test SET SINGLE_USER

DBCC CHECKDB (test)

6 - Change your DB status to something “neutral”

UPDATE sysdatabases

SET status = 28

WHERE dbname = 'test'

7 - Disable updates to system tables

EXEC sp_configure 'allow updates',0

RECONFIGURE WITH OVERRIDE

8 - Restart SQL Server

In SQL Server 2005, the procedure is much easier, and tough obviously still not recommended, it appears to use only supported commands:

ALTER DATABASE test SET SINGLE_USER, EMERGENCY

DBCC CHECKDB (test, REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE test SET MULTI_USER, ONLINE

Nice, huh? Obviously, both procedures will leave your database in a not-so-good shape because losing your transaction log, means losing the ability to undo uncommitted work and redo committed work that was not yet copied to your data file. So you should not trust anymore the integrity of you data.

Even so, I believe that there are times when this is worthwhile. If you don’t have a good backup and/or you are dealing with development databases this might be a useful last resort.

For more details, see Paul Randal's blog (always worth reading).