This week's been a busy one on the forums and Twitter, with lots of interesting problems people are hitting. One of the things I've noticed is that there are lot's of misconceptions about running repair, so to round out Friday I'm going to run through a list of them for you. Here are the misconceptions, some of which I've had to argue several times with people and eventually resort to 'Look, I wrote the repair code, I'm sorry but you're wrong', which I hate doing:
-
Repair will not cause data loss. It depends. If you have to use REPAIR_ALLOW_DATA_LOSS, then you're going to lose data. That's why the option is named that – seriously.
-
Repair should be run as the default. No. Figure out what's wrong first before deciding what to do about it. If you've got a damaged 1TB clustered index, it's going to get rebuilt by repair. If you don't have an extra 1TB of disk space, it will fail, and then you're back to square one after hours of fruitless effort. You might be able to get away with doing something that doesn't involve taking the database (essentially) offline.
-
You can run repair without running DBCC CHECKDB. No. Repair is an option to one of the consistency-checking commands (DBCC CHECKALLOC, DBCC CHECKTABLE, or DBCC CHECKDB – note that DBCC CHECKFILEGROUP and DBCC CHECKCATALOG don't support repair).
-
As soon as you've run repair, everything's fine. No. You should always run DBCC CHECKDB a second time after running repair, to make sure the first repair fixed everything. Sometimes a corruption prevents some deeper checks being done, and when it's fixed, the next DBCC CHECKDB can run the deeper check and find more corruptions. I call this 'corruption masking'. The other reason is that repair probably just deleted some of your data. What effect is that going to have on the application? What if that's medical patient data? Or insurance records? Or back account details?
-
Repair can always fix everything. No. There are some things that DBCC CHECKDB cannot fix. See CHECKDB From Every Angle: Can CHECKDB repair everything? for the list.
-
Repair is safe to use on system databases. No. It cannot be used on master or tempdb because they cannot be put into single-user mode. You can do it on model, but it's unlikely to have an effect as there aren't any user tables in model (unless you create them) and system tables generally don't get repaired. You can run it on msdb, but it might have strange side-effects. See Is running repair on msdb safe?.
-
You can run repairs online. No. Repairs are always offline, in that the database must be in single-user mode.
-
REPAIR_REBUILD will fix everything. No. REPAIR_REBUILD only fixes problem in nonclustered indexes. In 2005 onwards, REPAIR_FAST does nothing at all.
-
Repairs on a repl Publisher propagate to the Subscribers. No. Anything done by repair is NOT marked for replication. You must reinitialize your Subscribers if you repair a Publisher.
-
Repairs always fix-up constraints. No. It has no idea that constraints exist. After repairing a database with constraints, you should run DBCC CHECKCONSTRAINT to make sure they're still valid.
-
Repairs try to save data. No. It doesn't go out of it's way to delete data, but it doesn't go out of it's way to save data in most cases. 'Delete what's broken and fix up all the links' is my sound-bite explanation of what repair does. Fix things as fast as possible and as provably correct as possible.
-
EMERGENCY mode repair will always work. No. I've seen cases where something broken in the file-system caused it to fail. Don't rely on repair.
-
You can undo repairs. It depends. If you started an explicit transaction, then you can roll everything back if you don't like what it did. People rarely do this though. EMERGENCY mode repair can never be rolled back.
Repair's a dangerous beast and should only be used as a last resort, or to bring a VVVLDB online again potentially much faster than a full restore, when a small amount of data loss can be tolerated. This is just a quick brain-dump of things people get wrong about repair.
Have a great weekend – tomrorow I'll report on last week's survey and kick off a new one. Cheers!
7 thoughts on “Misconceptions around database repair”
Hey, @unclebiguns from Twitter here. The database I had corruption in today was on a VM. Have you noticed any correlation with corruption and VM’s?
great stuff paul as usual :-) ,i just wanted to ask a bout a scenario i am having in 2 TB cutsomer db , dbcc checkdb usually runs for 3 hours once a week ,suddenly for the last monthdbcc checkdb runs for 7 hours and the customer stops it becuase of the performance impacts during his other meintenance tasks during weekend,i urged him not to stop the dbcc checkdb since i have read in one of your blogs ,that this scenario usaully indicates that dbcc checkdb is doing ‘deep dive’ becuase it found some errors ,am i correct in my observation ?
@Jack Not really. I’ve seen some but nothing conclusive. Could be the software virtualization of IOs having an issue when pushed really hard, but that’s just speculation.
@Danny Yup – you’re correct. Sounds like they have corruption!
Hi,
My data file is corrupted, how to find that data file belongs to which file group.
select * from sys.sysfiles. The groupid is the filegroup ID. Then select * from sys.sysfilegroups and you’ll get the filegroup name.
Hi i know this is an old post. So we running a repair on a very large database and it filled up tempdb log file. We are extending the drive but my question is, if the repair fails due to the tempdb log file filling up. When we restart the repair will it start from the beginning or would it at least have repaired some of the errors while it ran?
It’s not the repair that filled the drive, it’s the consistency checks before the repairs. If it did any repairs before it failed, it shouldn’t need to do them again. Much better to restore from backups to avoid losing data and data consistency.