(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.)
This myth (and derivatives) are very common among non-DBAs as so many Windows problems can be fixed by rebootng the computer (yes, I still see this on servers, Windows 7 etc – try changing the terminal services port number without a reboot).
Myth #21: database corruption can be fixed by restarting SQL Server or rebooting the Windows server or detaching/attaching the database.
FALSE
None of these operations will cause SQL Server to fix a corruption. A corrupt page needs to be restored or repaired in some way – neither of which occur when SQL Server, Windows, or the physical machine is restarted, nor when a corrupt database is detached.
In fact, detaching and then trying to re-attach a corrupt database might be one of the worst things you can do if the corruption is such that the database cannot have crash recovery run on it (i.e. it is in the SUSPECT or RECOVERY_PENDING state) – as part of the process of attaching a database that needs crash recovery is… to run crash recovery – and if it can't be done, the attach fails. This is when the hack-the-database-back-in trick becomes necessary (see my blog post TechEd Demo: Creating, detaching, re-attaching, and fixing a suspect database). Don't ever detach a corrupt database.
Now – here are some interesting behaviors that could look like rebooting fixes the corruption:
-
If the corruption was just a corrupt page image in memory, but the on-disk image of the page is not corrupt, the corruption will seem to have been fixed by rebooting.
-
If the corruption was real, but you did something else as part of the reboot that caused that page to no longer be allocated, the corruption will seem to have been fixed by rebooting. This is the same as the myth I debunked a while ago in the blog post Misconceptions around corruptions: can they disappear?
-
If the I/O subsystem is rebooted too, and an I/O was 'stuck' somehow in the I/O subsystem (e.g. a persistent stale read issue) then the corruption will seem to have been fixed by rebooting. This isn't really fixing the corruption, this is allowing a broken I/O subsystem to recover. The I/O subsystem is still broken. I've seen this case maybe three or four times in my life.
Bottom line – to recover from corruption, you need some combination of backups and/or a redundant system to failover to. Rebooting is not the answer and will almost invariably just waste time.
4 thoughts on “A SQL Server DBA myth a day: (21/30) corruption can be fixed by restarting SQL Server”
I’m glad you added those "could look like rebooting fixes" examples. My first reaction to your post was "people really believe this?" – however after seeing those examples I can understand why somebody might come to such a conclusion.
Corruption is real and thankfully rare. I’ve seen I/O subsystems start shredding data due to Driver or Firmware problems (usually a mismatch, one gets updated without the other). It’s a real bummer when that happens. The first reaction to this is "what is going on?! Where did the data go? how can that query possibly return that dataset??!" In SS2000 these problems could go unnoticed for awhile – I really appreciate the new page checksum in 2005+
Most of our corruption is of the "internal" type – usually after a data import. It’s fun to see dates from the year "80."
The best restore plan begins with a good backup.
"as so many Windows problems can be fixed by rebootng the computer."
Ouch! It is oh so sad to see this here! I think we need to start a Windows Admin myth-a-day series. For the love of all-that-is-good, we are not in Win9x land anymore. :)
See Mr. Russinovich for details…
Ha – you’d be surprised how many times I have to reboot either an XP laptop, Win7 machine or Win2k8 server to fix some rogue process. Or, for instance, changing the port number that terminal services connects to on a Win2k8 server – reboot. Not an admin myth at all. I’ll tell Mark next time I see him.
I’m sorry, but I busted out laughing. :) We’ve been having problems at my workplace with our servers being apparently corrupted causing them to send us home early the last few days. And I find this hilarious because of the number of times we’ve had trouble with the server dishing us two files at a time or missing crucial information and our IT department basically shrugged it’s shoulders and said “We tried changing your username and giving you a new computer. Just restart your computer if it happens again~”
Next time I think I’m going to give a sly smile and ask “Have you tried changing the terminal services port number without rebooting? :)”
;D