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