Freigeben über

When should you rebuild the transaction log?

  • Artikel

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...

Comments

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

  • Anonymous
    June 18, 2006
    Emergency mode repair? In a couple of previous posts I explained how the two worst things you could do...

  • Anonymous
    December 29, 2006
    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)?

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

  • Anonymous
    November 26, 2007
    The comment has been removed

  • Anonymous
    October 09, 2008
    The comment has been removed