(Mis)Using DBCC Page

In one of my presentations recently I was recommending DBCC CHECKDB on every database every day. I realize that isn’t always possible or practical, so I noted that if you don’t have resources on your production server, or enough spare hardware, you should at least run it on every database once a month. At least on the database you care about.

Someone in the audience asked if they could just script DBCC PAGE on every page in the database instead. I wasn’t sure if that was accurate, but I didn’t think it was. So I asked THE MAN, and he confirmed this doesn’t equate to a DBCC CHECKDB.

I won’t attempt to give a complete explanation, mostly because I’m sure I’d miss something or be incorrect, but I will tell you how I feel about this, based on what I know.

CHECKDB performs an extensive evaluation of not only all objects (and hence their pages), but also their linkages. It performs a more complete check by default, but you can add the PHYSICAL_ONLY flag to speed things up and limit the checks to just the physical structures and allocations. PHYSICAL_ONLY also skips Filestream checks.

The DBCC PAGE command, undocumented, works, but it doesn’t really examine if the links and relationships between pages are correct.

I can’t say that DBCC PAGE couldn’t be use to detect corruption or find issues, but I wouldn’t depend on it. YMMV, but I wouldn’t use this as a substitute for CHECKDB.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.