March 30, 2015 at 11:31 pm
I normally use physical_only option for dbcc checkdb whether its maintenance jobs or running the statement manually. But, trying to understand what is mentioned in BOL, I couldn't tell exactly when I need to run a full check (without physical only). Is there an example of when you'd need to run dbcc checkdb rather than checkdb with physical_only?
From BOL,
"Limits the checking to the integrity of the physical structure of the page and record headers and the allocation consistency of the database. This check is designed to provide a small overhead check of the physical consistency of the database, but it can also detect torn pages, checksum failures, and common hardware failures that can compromise a user's data"
Does Physical_only option cover data consistency? What sort of corruption can physical_only option not detect?
Thanks,
March 31, 2015 at 3:14 am
To be honest, I'd personally recommend you always run it without the physical_only. Yes, the physical_only is faster and uses less CPU, but it doesn't do the logical checks of the database. You do not want to be in the position of finding that you have severe corruption which wasn't picked up because you were running a limited set of checks and are now going to lose data.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 31, 2015 at 7:04 am
What you can do is restore your database to another location and then run the full CHECKDB statement there. You really do need to do the internal logical checks as well as the physical checks. You can have corruption that is purely logical as well as corruption that is purely physical. You must check both.
By restoring the database to a second location not only have you offloaded the full check to save processing on your primary machine, but you've also done a test restore of your backup, which is something else that's good to do.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 31, 2015 at 7:33 am
Thank you Gail and Grant
April 1, 2015 at 5:49 am
I use my own script which works out the day of the week and performs a lightweight physical_only check at peak times and a full check at least once a week
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply