A SQL Server DBA myth a day: (16/30) corruptions and repairs

(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 quick 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 myth-buster 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!

3 thoughts on “A SQL Server DBA myth a day: (16/30) corruptions and repairs

  1. I’ve seen similar complaints about people who used Force_Service_Allow_Data_Loss. They were surprised that they lost data on the principal that went down after forcing service on the unsynchronized mirror.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.