Rebuilding a transaction log file

The database’s transaction log file contains, like the name implies, a log of all the transactions happening in the database. If you’ve set your database to the “simple” recovery model, the log is truncated (emptied) at every checkpoint (more or less all the time). In “bulk logged” or “full” recovery model, you’ll have to truncate the log files yourself, by performing a transaction log backup.

So because of this, the log file has to stay in sync with the rest of the database at all times. But what do you do if your log file is lost or corrupted? Luckily, there’s a simple way to recover it.

I’ve discovered to ways to remedy this problem, for two different scenarios.

The simple option: Attaching a clean database

If your database was shut down cleanly and you have all the database files (except the log file, of course), you can just attach the database using the CREATE DATABASE statement with FOR ATTACH_REBUILD_LOG.

It’ll look something like this:

CREATE DATABASE DatabaseNameHere
ON (FILENAME='D:\File1.mdf'),
   (FILENAME='D:\File2.ndf'),
   (FILENAME='D:\File3.ndf'),
   (FILENAME='D:\File4.ndf')
FOR ATTACH_REBUILD_LOG;

This basically attaches the database from the MDF/NDF files, and creates a blank, 1 MB large log file from scratch in the default log directory of your server. This is by far the easiest and least risky way to recreate a new log file.

This will of course break the backup chain, but then again, if you’ve lost your log file there’s really not much else you could do anyway.

The tricky option: database is suspect

In some situations, a corrupt log file will render the database “suspect”. First off, change the database mode to “emergency” and to single user mode using the following script. I’ve added “read-write” as well, in case the database is set as read-only:

ALTER DATABASE databaseName SET EMERGENCY;
ALTER DATABASE databaseName SET SINGLE_USER;
ALTER DATABASE databaseName SET READ_WRITE;

If the database is now in single-user, emergency mode, you can run a DBCC command on it. The following DBCC operation does not only create a new log file, but also checks the database for consistency errors, so it may take a while to run, depending on the size of your database.

DBCC CHECKDB (databaseName, REPAIR_ALLOW_DATA_LOSS);

With a little bit of luck, the operation will succeed, and your heart rate and blood pressure will go down a notch. Now, all you have to do is set the database back online and for multi-user use.

ALTER DATABASE databaseName SET MULTI_USER;
ALTER DATABASE databaseName SET ONLINE;

I don’t really have loads of experience of crashing log files, though a… uhm… friend of mine managed to lose a log file once. I’ve tried the solution above, and it worked for me once. Your mileage may vary, though.

Best of luck, anyway!