SQL Server Transaction Log – Rebuilding the Transaction Log in SQL Server 2005

dbcc-checkdbrecoverysql serversql-server-2005

We have a very large database (~6TB), whose transaction log file was deleted (while SQL Server was shut down. We have tried:

  1. Detaching and reattaching the database; and
  2. Undeleting the transaction log file

…but nothing has worked so far.

We are currently running:

ALTER DATABASE <dbname> REBUILD 
LOG ON (NAME=<dbname>,FILENAME='<logfilepath>')

… but given the size of the database, this will probably take a few days to complete.

Questions

  • Is there a difference between the command above and the following one?

      DBCC CHECKDB ('<dbname>', REPAIR_ALLOW_DATA_LOSS)

  • Should we be executing REPAIR_ALLOW_DATA_LOSS instead?

It's worth noting that the data is derived from other sources so the database can be rebuilt, however we suspect it will be much quicker to repair the database than to reinsert all the data again.

Update

For those keeping score: the ALTER DATABASE/REBUILD LOG command completed after around 36hrs and reported:

Warning: The log for database 'dbname' has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were.
You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.

We then ran a DBCC CHECKDB (took about 13hrs) which was successful. Let's just say that we've all learnt the importance of database backups (and granting project managers access to the server…).

Best Answer

Never detach a Suspect database. Anyway, how did you attach the database after detaching it? You used CREATE DATABASE with FOR ATTACH_REBUILD_LOG option?

These commands should have done the trick:

ALTER DATABASE recovery_test_2 SET EMERGENCY;   
ALTER DATABASE recovery_test_2 SET SINGLE_USER;  

DBCC CHECKDB (recovery_test_2, REPAIR_ALLOW_DATA_LOSS) 
WITH NO_INFOMSGS, ALL_ERRORMSGS;

I wrote a post for this situation:

SQL 2005/2008 Database Recovery Procedure – Log File Deleted (Part 3)

You asked about the difference between:

  • DBCC CHECKDB ('<dbname>', REPAIR_ALLOW_DATA_LOSS) and
  • ALTER DATABASE <dbname> REBUILD LOG ON (NAME=<dbname>,FILENAME='<logfilepath>')

The thing is that you can run both to rebuild the log file, but with CHECKDB you rebuild the log and check database for integrity errors as well.

Also the second (alter database) will not work if there were active transactions (not written to disk) when the log file was lost. At start-up or attach, SQL Server will want to perform recovery (rollback and rollforward) from the log file which is not there. It happens when a disk crashes or an unexpected shutdown of the server occurs and the database is not cleanly shutdown. I guess it was not your case and all sorted out good for you.

  1. DBCC CHECKDB (DBNAME, REPAIR_ALLOW_DATA_LOSS) run on a database in emergency status checks the database for inconsistency errors, tries first to use the log file to recover from any inconsistencies. If this is missing, the transaction log is rebuilt.

  2. ALTER DATABASE REBUILD LOG ON... is an undocumented procedure and requires a subsequent DBCC CHECKDB to fix any errors.

Related Question