When should you rebuild the transaction log?

When should you rebuild the transaction log?

Rate This
  • Comments 6

10 points if you answered "don't be daft, never!" and minus several million is you answered anything else. Yes, if you have no backups and your hardware has corrupted your transaction log then you have no choice but to rebuild it, but this should never happen because you all have backup strategies, right?

Do you know what rebuilding the log does? It's very simple - rip out the transaction log with no regard for an unrecovered transactions and create a new one. This usually results in corruption to database structures and transactional inconsistencies - nice, eh?

In SQL Server 2000, the DBCC command to do this is undocumented and unsupported (except under guidance from Product Support when all other possibilities have been exhausted). When a log is rebuilt, a message is written to the SQL error log and the Windows event log.

In SQL Server 2005, the messages are printed and the fact that it happened is persisted for all time in the database itself. We also removed the DBCC syntax and added different (but still undocumented and unsupported) syntax. We didn't do this to be awkward, but it made sense to have the syntax in a different part of the T-SQL language, and given that its undocumented and unsupported, no-one should be relying on the old syntax.

Here's a contrived example - imagine you're at an ATM transferring $1000 from your checking account to your savings account. On the SQL Server in the bank's datacenter, the transaction happens in two parts - update the record in the checking accounts table with the balance minus $1000 and then update the record in the savings account table with the balance plus $1000. But, disaster strikes! A work crew outside the datacenter accidentally cuts the power and the machine hosting SQL Server powers down. The ATM displays one of those infuriating "We're sorry, our computers are unavailable at present" and you walk away grumbling but think nothing more about it.

Meanwhile, the power's been restored to the datacenter and SQL Server is going through crash recovery. The partially completed transaction on your account should rollback and essentially credit back the $1000 to your checking account. But, for reasons only known to himself, the new DBA at the bank decides to rebuild the log to get the system up faster. Woah! The unrecovered portion of the log includes the unrecovered transaction on your accounts - kiss goodbye to your $1000...

Another simple example involves an insert into a table with a single non-clustered index. Under the covers, the insert happens in two parts - insert the record into the table and then insert the non-clustered index record. Imagine a similar disaster recovery situation as I described above occuring after the table insert has been done but not the non-clustered index insert. If the log is rebuilt, then the table and its index are not in sync - corruption!

Product Support will recommend rebuilding the transaction log only if something has corrupted it and you have no backups (plus gently reminding you to get a backup strategy). Before they do so they'll make sure you're aware of the possibly consequences of doing it and advise you to run DBCC CHECKDB to determine whether the database is in a structurally and transactionally consistent state. If not, you'll most likely need to run REPAIR_ALLOW_DATA_LOSS and then begin the process of working out what data was lost (and start dealing with irate customer?)

Now, I can only say 'get a backup strategy!' so many times, and there are always going to be people who don't take this advice and get themselves into trouble. For that reason, we created a new feature in SQL Server 2005 called Emergency Mode Repair.

More on that in a couple of posts...

Leave a Comment
  • Please add 5 and 2 and type the answer here:
  • Post
  • If you ever visit the forums you will learn many ways to manage your SQL Server. Some of these are good...
  • Emergency mode repair? In a couple of previous posts I explained how the two worst things you could do...
  • what to do when mdf & log are bad  and if dbcc REPAIR_ALLOW_DATA_LOSS cause assertion at logmgr.cpp with expression (minLSN.m_fSeqNo < lfcb->lfcb_fSeqNo)?

  • If you ever visit the forums you will learn many ways to manage your SQL Server. Some of these are good

  • Hey Paul, you say "don't be daft, never!"

    I have a sql 2005 express database that is 1.4 gb ,,, the transaction file grew to 84 gb ... sql is pathetic!!!

    Oii, enough already!!! there is no way that the transaction file should with default swettings grow to that ridiculous size. the database is backed up every day. It is not mission critical to the extent of a live transaction bank account database or airline reservation database. Even if it was, the size of the transaction file should NOT grow to 60 times the size of it's database. You have nothing to be proud of or smart about ... FIX IT!!!

    shalom, Robert

  • Hello Dear

    First, thanks for this article!

    To bad to see poeple with no background knowledge,  trying to blame you.

    "Don't be daft, never!" is just a great and clean statement.

Page 1 of 1 (6 items)