I'll be blogging a bunch more about repair after my corruption session this week at TechEd - watch this space!

In my previous post on interpreting CHECKDB output, plus in my DBCC Internals session at TechEd IT Forum yesterday, I mentioned there are some things that CHECKDB can’t repair. In this post I want to go into a bit more detail – based on a post from my old Storage Engine blog.

Before anyone takes this the wrong way - what do I mean by "can't be repaired"? Remember that that purpose of repair is to make the database structurally consistent, and that to do this usually means deleting the corrupt data/structure (that's why the option to do this was aptly named REPAIR_ALLOW_DATA_LOSS – see this post for more explanation on why repair can be bad). A corruption is deemed unrepairable when it doesn't make sense to repair it given the damage the repair would cause, or the corruption is so rare and so complicated to repair correctly that it's not worth the engineering effort to provide a repair. Remember also that recovery from corruptions should be based on a sound backup strategy, not on running repair, so making this trade-off in functionality makes sense.

Here's a few of the more common unrepairable corruptions that people run into along with the reasons they can't be repaired by DBCC.

PFS page header corruption

An example of this is on SQL Server 2005:

Msg 8946, Level 16, State 12, Line 1
Table error: Allocation page (1:13280496) has invalid PFS_PAGE page header values.
Type is 0. Check type, alloc unit ID and page ID on the page.

CHECKDB uses the PFS pages to determine which pages are allocated - and so which pages to read to drive the various consistency checks. The only repair for a PFS page is to reconstruct it - they can't simply be deleted as they're a fixed part of the fabric of the database. PFS pages cannot be rebuilt because there is no infallible way to determine which pages are allocated or not. There are various algorithms I've experimented with to rebuild them, with optimistic or pessimistic setting of page allocation statuses and then re-running the various consistency checks to try to sort out the incorrect choices, but they all require very long run-times. Given the frequency with which these corruptions are seen, and the engineering effort required to come up with an (imperfect) solution, I made the choice to leave this as unrepairable, and I don’t think that will change in future.

Critical system table clustered-index leaf-page corruption

An example of this is on SQL Server 2000:

Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:18645) with latch type SH. sysindexes failed.

And on SQL Server 2005:

Msg 7985, Level 16, State 2, Server SUNART, Line 1
System table pre-checks: Object ID 4. Could not read and latch page (1:51) with
latch type SH. Check statement terminated due to unrepairable error.

In a previous post in the series I described why how and why we do special checks of the clustered indexes of the critical system tables. If any of the pages at the leaf-level of these indexes are corrupt, we cannot repair them. Repairing would mean deallocating the page, wiping out the most important metadata for potentially hundreds of user tables and so effectively deleting all of these tables. That's obviously an unpalatable repair for anyone to allow and so CHECKDB doesn't do it.

Column value corruption

Here's an example of this on SQL Server 2005:

Msg 2570, Level 16, State 3, Line 1
Page (1:152), slot 0 in object ID 2073058421, index ID 0, partition ID 72057594038321152, alloc unit ID 72057594042318848 (type "In-row data"). Column "c1" value is out of range for data type "datetime".  Update column to a legal value.

This is where a column has a stored value that is outside the valid range for the column type. There are a couple of repairs we could do for this:

1. delete the entire record
2. insert a dummy value

#1 isn't very palatable because then data is lost and it’s not a structural problem in the database so doesn't have to be repaired. #2 is dangerous - what value should be chosen as the dummy value? Any value put in may adversely affect business logic, or fire a trigger, or have some unwelcome meaning in the context of the table - even a NULL. Given these problems, I chose to allow people to fix the corrupt values themselves.

Metadata corruption

Here's an example of this on SQL Server 2005:

Msg 3854, Level 16, State 1, Line 2
Attribute (referenced_major_id=2089058478) of row (class=0,object_id=2105058535,column_id=0,referenced_major_id=2089058478,referenced_minor_id=0) in sys.sql_dependencies has a matching row (object_id=2089058478) in sys.objects (type=SN) that is invalid.

This example is relatively benign. There are other examples that will cause CHECKDB to terminate - not as bad as the critical system table corruption example above, but enough that CHECKDB doesn't trust the metadata enough to use it to drive consistency checks. Repairing metadata corruption has the same problems as repairing critical system table corruption - any repair means deleting metadata about one or more tables, and hence deleting the tables themselves. It's far better to leave the corruption unrepaired so that as much data as possible can be extracted from the remaining tables.

Summary

Repair can't fix everything. You may end up having to perform manual and time-consuming data extraction from the corrupt database and losing lots of data because of, say, a critical system table corruption. Bottom line (as usual) - make sure you have valid backups so you don't get into this state!

Last week at SQL Connections someone said that CHECKDB’s output is ‘useless’.  Given that I wrote CHECKDB for SQL Server 2005 I was only mildly offended :-) But there’s a lot of truth in that statement – the error messages from CHECKDB do tell you exactly what’s corrupt in the database but they’re not exactly easy to read and understand unless you’re intimately familiar with the on-disk structures used to store a SQL Server database. It’s pretty easy to understand the output if there’s only one or two error messages, but once you get past 10 or so it becomes extremely difficult to figure out what the actual problem is for normal people (i.e. not CHECKDB geeks like me, Ryan or a handful of senior people in Product Support)

So, how are you supposed to figure out what’s wrong? In this post I want to run through a few tips and tricks you can use.

• If the recommended repair level (at the very bottom of the output) is REPAIR_REBUILD then only non-clustered indexes are damaged. This meanscyou don’t need to run repair (which needs the database in single-user mode) or restore from backups. You can just manually rebuild the damaged indexes to fix the problem, possibly even online if you're running Enterprise Edition and the index doesn't have any LOB columns.
• There are a couple of cases where even if it’s only non-clustered indexes that are damaged, REPAIR_ALLOW_DATA_LOSS may still be the recommended repair option. In this case, check the index ID in all the error messages – if all index IDs are 2 or higher, then it’s only non-clustered indexes that are damaged and you can rebuild them yourself instead of running repair/restore.
• Figure out a list of tables that are affected by looking at the summary messages about how many corruptions were found per table. If each of the tables is isolated on a separate filegroup then these filegroups can be taken offline and restored individually, possibly without interrupting the application workload. If there's no backup, then you can run DBCC CHECKTABLE with repair, for a faster repair operation than running a full CHECKDB.
• Figure out the list of damaged pages. There are a number of error messages that could point to a damaged page – usually 8928 or 2537 – and then you can choose to do single-page restores from your backups to minimize downtime, possibly even online in Enterprise Edition.
• Are there any errors that CHECKDB can’t repair?  If so, you have no choice but to restore from backups or to extract as much info as possible into a new database. Things to look for that mean repair won’t be able to fix everything are:
  • CHECKDB stops early and complains about system table pre-checks failing (errors 7984 – 7988 inclusive)
  • CHECKDB reports any metadata corruption (8992, 8995 errors)
  • CHECKDB reports any errors on PFS page headers (8939, 8946 errors with a possible 8998 error as well)

While I was at Microsoft I wrote two 200-page+ documents detailing all the CHECKDB error messages for SQL Server 2000 and 2005 – the team says they should all be translated into Books Online entries by next summer – a bunch of them are there already. In the meantime, I hope the hints above will take a little of the mystery out of CHECKDB’s output.

This is a follow-on article from two posts:

• Corruption: Last resorts that people try first... where I discussed the two worst things you can do (in my opinion) to a database - rebuilding the transaction log and running REPAIR_ALLOW_DATA_LOSS.
• Search Engine Q&A #4: Using EMERGENCY mode to access a RECOVERY PENDING or SUSPECT database where I introduced EMERGENCY mode and walked through an example script showing its use.

People get themselves into situations where they have no backups (or damaged backups) and the data or log files are damaged such that the only way to access the database is with EMERGENCY mode. In these situations, prior to SQL Server 2005, there was no documented or supported way to fix a database while in EMERGENCY mode - the only guidance could be found on the Internet or from calling Product Support and paying for help. The sequence of events was:

1. Hack the system tables to get the database into 'emergency' mode.
2. Use the undocumented and unsupported DBCC REBUILD_LOG command to build a new transaction log.
3. Run DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS option to fix up corruptions in the data files - both those that may have caused the issue, and those caused by rebuilding the transaction log (e.g. because an active transaction altering the database structure was lost).
4. Figure out what data was lost or is transactionally inconsistent (e.g. because a transaction altering multiple tables was lost) as far as your business logic is concerned
5. Take the database out of emergency mode
6. And then all the other stuff like root-cause analysis and getting a better backup strategy

I decided to add a new feature to SQL Server 2005 called EMERGENCY mode repair that will do steps 2 and 3 as an atomic operation. The reasons for this were:

• Much of the advice of how to do this on the Internet missed steps out (particularly missing step 3!)
• The DBCC REBUILD_LOG command was unsupported and undocumented and we didn't like advising customers to use it
• Adding a documented last-resort method of recovering from this situation would reduce calls to Product Support - saving time and money for customers and Microsoft.

So, when in EMERGENCY mode, you can use DBCC CHECKDB to bring the database back online again. The only repair option allowed in EMERGENCY mode is REPAIR_ALLOW_DATA_LOSS and it does a lot more than usual:

• Forces recovery to run on the transaction log (if it exists). You can think of this as 'recovery with CONTINUE_AFTER_ERROR' - see this post for more details on the real CONTINUE_AFTER_ERROR option for BACKUP and RESTORE. The idea behind this is that the database is already inconsistent because either the transaction log is corrupt or something in the database is corrupt in such a way that recovery cannot complete. So, given that the database is inconsistent and we're about to rebuild the transaction log, it makes sense to salvage as much transactional information as possible from the log before we throw it away and build a new one.
• Rebuild the transaction log - but only if the transaction log is corrupt.
• Run DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS option.
• Set the database state to ONLINE.

It's a one-way operation and can't be rolled back. I always advise taking a copy of the database files before doing this in case something goes wrong or there are unrepairable errors. And if it does? Probably time to update your resume for not having a water-tight backup and disaster-recovery strategies in place. Saying that, I've never seen it fail. I can think of some pathalogical cases where it would fail though (involving the file system itself having problems) but that's really unlikely.

Let's walk-through an example of using it. I'm assuming there's a database called emergencydemo that's in the same state as at the end of the Search Engine Q&A #4 blog post - the database has no log file, is in EMERGENCY mode and the salaries table is corrupt.

First off I'll try bringing the database online, just to see what happens:

ALTER DATABASE emergencydemo SET ONLINE;
GO

File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\emergencydemo_log.LDF" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
Msg 945, Level 14, State 2, Line 1
Database 'emergencydemo' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Failed to restart the current database. The current database is switched to master.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

The first message makes sense - the database knows it needs to be recovered because it wasn't cleanly shut down, but the log file simply isn't there. The second message is from the new feature in 2005 that will automatically create a log file if one is missing on startup or attach - as long as the database was cleanly shut down. The 945 and 5069 errors are self-explanatory but notice that the database gets switched to master underneath us. This has bitten me several times in the past.

Well, I expected that not to work. Let's run emergency-mode repair:

DBCC CHECKDB (emergencydemo, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO

Msg 945, Level 14, State 2, Line 1
Database 'emergencydemo' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

Hmm - looks like the failed ALTER DATABASE statement did change the state - but what to?

SELECT state_desc FROM sys.databases WHERE name='emergencydemo';
GO

state_desc
------------------------------------------------------------
RECOVERY_PENDING

That makes sense I guess. Ok - back to emergency mode and run repair:

ALTER DATABASE emergencydemo SET EMERGENCY;
GO
DBCC CHECKDB (emergencydemo, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO

Msg 7919, Level 16, State 3, Line 1
Repair statement not processed. Database needs to be in single user mode.

EMERGENCY mode is not SINGLE_USER mode - a database needs to be in SINGLE_USER mode for repair to run and EMERGENCY mode allows multiple connections from members of the sysadmin role. You can set SINGLE_USER mode as well as EMERGENCY mode - however, the sys.databases field state_desc will still just say EMERGENCY.

ALTER DATABASE emergencydemo SET SINGLE_USER;
GO
DBCC CHECKDB (emergencydemo, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO

File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\emergencydemo_log.LDF" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
Warning: The log for database 'emergencydemo' 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.

This time it worked. First of all we get the same error as if we tried to bring the database online - that's from the code that's trying to run 'recovery with CONTINUE_AFTER_ERROR' on the transaction log. Next we get a nice long warning that the transaction log has been rebuilt and the consequences of doing that (basically that you need to start a new log backup chain by taking a full backup). If there had been any corruptions we'd have seen the usual output from DBCC CHECKDB about what errors it found and fixed. There's also a bunch of stuff in the error log

2007-10-02 17:21:20.95 spid51      Starting up database 'emergencydemo'.
2007-10-02 17:21:20.96 spid51      Error: 17207, Severity: 16, State: 1.
2007-10-02 17:21:20.96 spid51      FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\emergencydemo_log.LDF'. Diagnose and correct the operating system error, and retry the operation.
2007-10-02 17:21:20.96 spid51      Starting up database 'emergencydemo'.
2007-10-02 17:21:21.10 spid51      Starting up database 'emergencydemo'.
2007-10-02 17:21:21.18 spid51      Warning: The log for database 'emergencydemo' 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.
2007-10-02 17:21:21.18 spid51      Warning: The log for database 'emergencydemo' 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.
2007-10-02 17:21:21.99 spid51      EMERGENCY MODE DBCC CHECKDB (emergencydemo, repair_allow_data_loss) WITH no_infomsgs executed by ROADRUNNERPR\paul found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 1 seconds.

Note that the usual error log entry from running DBCC CHECKDB is preceded by 'EMERGENCY MODE' this time.

Checking the database state:

SELECT state_desc FROM sys.databases WHERE name='emergencydemo';
GO

state_desc
------------------------------------------------------------
ONLINE

we find that it's been brought back online again because everything worked. It's still SINGLE_USER though so let's make it MULTI_USER and see what happened to our table:

ALTER DATABASE emergencydemo SET MULTI_USER;
GO
USE EMERGENCYDEMO;
GO
SELECT * FROM salaries;
GO

FirstName            LastName             Salary
-------------------- -------------------- -----------
John                 Williamson           10000
Stephen              Brown                0
Jack                 Bauer                10000

(3 row(s) affected)

And of course its still corrupt - because even though the transaction log was rebuilt and repaired, the original transaction that changed the salary to 0 never got a chance to rollback becuase I deleted the transaction log (in the previous post).

Now remember, you should only use this as a last resort, but if you do get yourself into trouble, you know there's a command that should be able to help you.

These next few posts are based off part of my Secrets of Fast Detection and Recovery from Database Corruptions session from TechEd and various user groups around the world (see here for a video recording from TechEd). I'll also be doing this session at ITForum in Barcelona in November, and as a live webcast for Microsoft sometime over the next month or so (I'll publicize the date nearer the time). I did a few posts on these subjects last year but now I want to reorder them, add a post about using emergency mode and walk you through some emergency mode demo scripts.

In this post I want to describe the two worst things I think you can do to your database - rebuilding your transaction log and running REPAIR_ALLOW_DATA_LOSS - that people often try doing instead of restoring from their backups.

Rebuilding your transaction log

It's pretty well known that in SQL Server 2000 (and before) there's an undocumented and unsupported command called DBCC REBUILD_LOG. What this does is pretty simple - it deletes the transaction log file(s) and creates a new one. It completely disregards any uncommitted transactions that may exist - it just deletes them. This means that these uncommitted transactions don't get a chance to roll back.

What does this mean? Well, in the best case, the only in-flight transactions were altering user data - so your business logic, inherent and constraint-enforced relationships between tables, and the basic logical integrity of your user data are all broken. In the worst case, the in-flight transactions were altering the structure of the database (e.g. doing a page split) so that fact that they didn't get achance to rollback means the databse may be structurally corrupt!

Here's are two examples (somewhat contrived) that illustrate the possible consequences of rebuilding a transaction log.

1. Logical data loss.

   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. The process is half-way through - $1000 has been debited from your checking account, but not yet credited to your savings account, when 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 credit back the $1000 to your checking account. But the new DBA at the bank decides that its taking too long for the system to come back up. He delets the transaction log and rebuilds it to get the system up faster. Unfortunately, the portion of the transaction log that had not had a chance to recover included the transaction involving your bank account. Even more unfortunately, a checkpoint occured right before the power loss, and the database page containing the updated checking account balance was flushed to disk. Now, when the transaction log is deleted and rebuilt, your transaction can't rollback - because it's simply gone. So the $1000 debit from your checking account is not rolled back - you've lost $1000!!

2. Physical database corruption.

Imagine 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 corresponding 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 - actual phsyical corruption!

REPAIR_ALLOW_DATA_LOSS

The vast majority of the time, REPAIR_ALLOW_DATA_LOSS is the repair level that CHECKDB recommends when it finds corruptions. This is because fixing nearly anything that's not a minor non-clustered index issue requires deleting something to repair it. So, REPAIR_ALLOW_DATA_LOSS will delete things. This means it will probably delete some of your data as well. If, for instance it finds a corrupt record on a data page, it may end up having to delete the entire data page, including all the other records on the page, to fix the corruption. That could be a lot of data. For this reason, the repair level name was carefully chosen. You can't type in REPAIR_ALLOW_DATA_LOSS without realizing that you're probably going to lose some data as part of the operation.

I've been asked why this is. The purpose of repair is not to save user data. The purpose of repair is to make the database structurally consistent as fast as possible (to limit downtime) and correctly (to avoid making things worse). This means that repairs have to be engineered to be fast and reliable operations that will work in every circumstance. The simple way to do this is to delete what's broken and fix up everything that linked to (or was linked from) the thing being deleted - whether a record or page. Trying to do anything more complicated would increase the chances of the repair not working, or even making things worse.

The ramifications of this are that running REPAIR_ALLOW_DATA_LOSS can lead to the same effect on your data as rebuilding a transaction log with in-flight transactions altering user data - your business logic, inherent and constraint-enforced relationships between tables, and the basic logical integrity of your user data could all be broken. BUT, the database is now structurally consistent and SQL Server can run on it without fear of hitting a corruption that could cause a crash.

To continue the contrived example from above, imagine your bank checking and savings accounts just happen to be stored on the same data page in the bank's SQL Server database. The new DBA doesn't realize that backups are necessary for disaster recovery and data preservation and so isn't taking any. Disaster strikes again in the form of the work crew outside the datacenter accidentally cutting the power and the machine hosting SQL Server powers down. This time, one of the drives has a problem while powering down and a page write doesn't complete - causing a torn page. Unfortunately, it's the page holding your bank accounts. As the DBA doesn't have any backups, the only alternative to fix the torn-page is to run REPAIR_ALLOW_DATA_LOSS. For this error, it will delete the page, and does so. In the process, everything else on the page is also lost, including your bank accounts!!

Summary

So, you can see how these two operations are really very, very bad things to do to a database and can cause havoc with your data. And yet people still have to  use these operations because they don't have valid backups...

In the next post I'll introduce EMERGENCY mode and how to use it.

Now that I have a little more time on my hands I've been jumping back into some of the online forums. Last summer I posted on a few bits of bad advice I've seen in the forums but yesterday I was stunned by some of the terrible advice I saw being given out. So, I'd like to post a new and longer collection of some of the bad advice I've seen over the last couple of years (and yesterday!) with some reasoning and better advice.

Run CHECKALLOC then CHECKDB then CHECKTABLES...

There's a common misconception around what CHECKDB actually does. According to the Books Online entry I wrote for SQL Server 2005 (see http://msdn2.microsoft.com/en-us/library/ms176064.aspx), it does the following:

• Runs DBCC CHECKALLOC on the database.
  
• Runs DBCC CHECKTABLE on every table and view in the database.
  
• Runs DBCC CHECKCATALOG on the database.
  
• Validates the contents of every indexed view in the database.
  
• Validates the Service Broker data in the database.
  

This means that the DBCC CHECKALLOC, DBCC CHECKTABLE, or DBCC CHECKCATALOG commands do not have to be run separately from DBCC CHECKDB in SQL Server 2005. Note that in SQL Server 2000, CHECKCATALOG wasn't part of CHECKDB.

Run CHECKALLOC to find out what's wrong...

Scenario: Any kind of corruption, but commonly advised for 823/824 errors. CHECKALLOC only checks the allocation bitmaps so it won't discover corruptions in the vast majority of the database. You should always run a DBCC CHECKDB (with the PHYSICAL_ONLY option if need be to save time/resources) to find out the full extent of corruption in the database.

Just restore from your backups and carry on...

Scenario: corruption. The theory is that restoring from your backups fixes the problem and you'll be fine. In reality this only fixes the symptom of the problem - the real problem is what caused the corruption in the first place. Its perfectly ok to restore from a backup to get the database back up and running again quickly, but you have to make sure that you investigate the cause of the corruption and take steps to make sure it won't happen again - root-cause analysis.

Just run repair...

Most of the time running repair means losing data. Running repair should only really be done when you don't have any valid backups. I can also see a case where you've got corruption and your maximum allowable downtime doesn't allow you to restore from your backups because your backup strategy doesn't allow for quick, fine-grained restores. In that case you may be forced to run repair (and then fix your backup strategy), but really it should always be your last resort as you'll most likely lose data.

Also, if you do end up having to run repair, make a backup of your database first - just in case something goes wrong. You can also wrap the repair statement in an explicit transaction so you can rollback the whole repair operation if you think its losing too much data - but then you're out of options. Once you're done, don't forget to do root-cause analysis too.

Only restore from a backup if repair doesn't work...

This is one I saw yesterday and I couldn't believe it. The advice was to run all the various types of repair and only resort to restoring from a backup if the repairs didn't work. I really don't get this one - why bother taking backups at all if you're always going to exhaust repair options first? Repair should be your last option, not your first.

Just rebuild your transaction log...

Scenario: log corruption. Rebuilding a transaction log is almost guaranteed to lose data and cause corruption so you need to be in really dire need to do this. Again, the solution here should be to restore from your backups. If you don't have backups then you're going to have to do this. For anything before SQL Server 2005, you should contact Product Support to have them walk you through the correct set of steps to do this. For SQL Server 2005, you should use Emergency Mode Repair. I'll discuss this in the next post.

Unbelievably, I've seen this recommended when recovery is taking too long - shut the server down, delete the transaction log and then rebuild it. Yikes!

Try running the other repair options before REPAIR_ALLOW_DATA_LOSS...

Scenario: CHECKDB says to use REPAIR_ALLOW_DATA_LOSS but would like to avoid it. At the bottom of CHECKDB's output is the minimum repair option needed to fix all the corruptions that CHECKDB found. If it says you need to use REPAIR_ALLOW_DATA_LOSS, then that's the only option that will fix all the errors. There's no point trying REPAIR_FAST (which I only left in SQL Server 2005 for backwards compatibility - it does nothing) or REPAIR_REBUILD. What you really should do is restore from your backups, but if you don't have any then you're going to have to bite the bullet and run REPAIR_ALLOW_DATA_LOSS.

Drop all the indexes and create them all again...

Scenario: corruption in non-clustered indexes. The theory is that dropping and recreating the indexes will fix the corruption. Sure - it'll probably get rid of the corruption for a while at least (until whatever caused the corruption happens again). The problem here is that if any of the indexes are enforcing constraints, dropping them means that something could happen that breaks the constraint (e.g. someone inserting a duplicate value). This means that you won't be able to recreate the constraint-enforcing index again.

At the very least try simply rebuilding the indexes. If that doesn't work, you may need to resort to rebuilding them using CHECKDB and the REPAIR_REBUILD option. Regular index rebuilds can read the data from the index using a logical-order scan. However, if the index b-tree structure is corrupted then that won't work. CHECKDB always forces the query processor to ask for an allocation-order scan to avoid corruption problems.

Detach then re-attach your database...

Scenario: suspect database. The theory is that recovery will run again and somehow work the next time and fix whatever caused the database to go suspect in the first place. Here's the bad part - once you detach a suspect database, it's almost inevitable that the attach process will fail because of the original problem. This means you're then stuck with a detached database and you need to resort to hacks to get the database attached again.

The database either went suspect because:

1. the transaction log ran out of space (either the drive ran out of space or the log was not set to autogrow)
2. a page was corrupted that was required for transaction rollback/recovery
3. an internal system operation (e.g. allocating a page) came across a corrupt page (e.g. an allocation bitmap)

None of these things can be fixed by a simple detach/attach. In the first case, you need to give the log more space. Either grow the log file and bring the database online, or detach the database and move it to a new location with more space. See http://support.microsoft.com/kb/224071/ for how to move databases around using detach/attach.

The last two cases require restoration from backups or running some form of repair - no amount of rerunning recovery will fix a corruption.

Options for creating a corrupt database...

Scenario: people want to test their disaster recovery plans and so need to know how to create a corrupt database. The best way to do this is to use an already corrupt database - see my recent post that provides a corrupt 2000 and 2005 database as well as some things to try with them. One of the most common suggestions I've seen for corrupting a SQL Server 2000 database is to manually delete something from the sysindexes or sysobjects tables. I've also described how to do it using a hex editor:

Shutdown the database so the data files aren't locked (don't detach the database because if you corrupt the 'wrong' page you may not be able to attach it again). Pick an offset more than, say, 100 pages into the file (at least 819200 bytes) but make sure its aligned on an 8192 byte boundary (a page boundary). This avoids critical metadata pages and allocation bitmaps so that you'll be able to start the database and run DBCC CHECKDB on it. Write a few bytes of zeroes into the file at the chosen offset and you're almost guaranteed some page header corruption errors.

However, the very worst piece of advice I've ever seen on the Internet was another method that I don't recommend. The advice was to go into the data-center (scary as this shows the poster works for a fairly large company), go up to one of the hard-drives and flick the power switch on and off a few times. Wow! Not only will that cause corruptions but it will also fry the hard-drive...

Summary

The bottom-line is that you need to be very careful when following anyone's advice on the Internet. Many people out there DO know exactly what they're talking about, but many also do not and may help you get more deeply into trouble.