SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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

Filed under: Blog Tagged: administration, sql server, syndicated

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...