(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.)
Short and quicky today as in the dictionary under 'busy' it says 'See Paul Randal today' – but I can't neglect my readers so have to get a blog post in… :-)
Myth #16: variety of myths around corruptions and repairs…
All of them are FALSE
There are a bunch of things I hear over and over around what repair can and cannot do, what can cause corruptions, and whether corruptions can disappear. A bunch of these I've already written up in blog posts over the last few years so rather than regurgitate the same stuff, this mythbuster post is some interesting links to keep you happy.
Firstly, around what repair can and cannot do. I wrote a blog post Misconceptions around database repair that covers 13 separate myths and misconceptions – from whether you can run repair separately from DBCC CHECKDB (no!) to whether REPAIR_ALLOW_DATA_LOSS will cause data loss (I'm confused as to why the name is confusing :-).
Secondly, I've heard many people complaining the DBCC CHECKDB shows corruptions which then 'disappear' when they run DBCC CHECKDB again. There's a very good reason for this – the database pages that were exhibiting corruptions are no longer part of the allocated set of pages in the database by the time DBCC CHECKDB is run a second time – so they don't show as corruptions. I explain this in great detail in the blog post Misconceptions around corruptions: can they disappear?.
Lastly, there's a pervasive myth that interrupting a long-running operation (like shrink, index rebuild, bulk load) can cause corruption. No. Unless there's a corruption bug in SQL Server (which happens sometimes, but rarely), nothing you can do from T-SQL can cause corruption. I wrote a detailed blog post on this a couple of years ago – see Search Engine Q&A #26: Myths around causing corruption.
Hope these are useful to you – got some good posts coming up next week in the series!