July 26, 2012 at 6:06 am
Hi
I was doing regular maintainance for my DB and I found Zero Suspect Pages in MSDB DB.
Still do u think I should Run CheckDB Command .
My DB Size is 120GB and I can have a Downtime of One day
July 26, 2012 at 6:08 am
All databases should have regular integrity checks run on them.
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
July 26, 2012 at 6:13 am
Other than Suspect pages is there any other thing that I should notice to run CHECKDB???
July 26, 2012 at 6:41 am
You run checkDB to see if you have corruption, not when you have corruption.
All databases should get regular scheduled integrity checks.
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
July 26, 2012 at 12:33 pm
Running CHECKDB causes CHECKTABLE,CHECLALLOC and CHECKCATALOG to be run in addition.
these commands execute a very detailed check on the database for example checktable checks the accuracy of computed columns,indexed views ,File stream pointers, partitioning distribution,... so it's not just the suspect pages
Pooyan
July 26, 2012 at 1:10 pm
Jai-SQL DBA (7/26/2012)
HiI was doing regular maintainance for my DB and I found Zero Suspect Pages in MSDB DB.
Still do u think I should Run CheckDB Command .
My DB Size is 120GB and I can have a Downtime of One day
How do you think pages get into this table? They just appear there all of the sudden, or do you think maybe the page has to be read before getting there? (Hint hint) Maybe this is giving you some thoughts?
Jared
CE - Microsoft
July 26, 2012 at 1:40 pm
July 26, 2012 at 2:02 pm
and if you do not have a long enough window on weekdays you can run a lightweight version of Checkdb by running it with a parameter ==> "with Physical_Only"
Following from Books online:
Physical_Only option 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.
But as all would agree you still got to run a full blown CheckDB once in a while ( if not every weekend) to stay out of trouble in the long term or maybe not having to look for a new job suddenly one fine day.
BTW a Full blown CheckDB takes more time and resources as it performs both physical and logical checks.
At the cost of sounding preachy, I would like to state that the primary job of the DBA is to safeguard databases and their integrity, all other things come secondary ( even performance tuning even though it looks more jazzy thing to do)
There is no better person in the while world than Paul S. Randal when it comes to DBCC CheckDB and that is because he wrote DBCC code himself. Following is a blog from Paul that will shed some light. Take some time out to read it and i guarantee that it will be time well spent.
http://www.sqlskills.com/blogs/paul/category/CHECKDB-From-Every-Angle.aspx
July 26, 2012 at 2:10 pm
You can also restore a weekly copy of the database on another server and run CheckDB against it.
July 26, 2012 at 2:38 pm
Paul Randal writes:
"In 2005, a guy called Paul Randal rewrote DBCC CHECKDB again to use database snapshots to get the consistent view of the database (as a database snapshot automatically provides a transactionally-consistent, point-in-time view of the database). No more nasty transaction log analysis code, not more locks *at all* - as accesses to the source database of a database snapshot never take locks - the buffer pool manages all the possibilities of race conditions. "
Does this mean that it's no longer necessary to restore a DB backup to run DBCC CHECKDB, or does it still consume enough resources to make that step necessary if users complain?
July 26, 2012 at 2:47 pm
dan-572483 (7/26/2012)
Does this mean that it's no longer necessary to restore a DB backup to run DBCC CHECKDB, or does it still consume enough resources to make that step necessary if users complain?
The same guy (Paul) recommends running CheckDB on a restored backup for larger, busier environments...
CheckDB hammers a database. There's no other single command that you can run that does as much IO and uses as much CPU as a full checkDB does. If you've got a large enough maintenance window or a low enough overall load that you can handle it reading every single page in the DB and running expensive checks on all, then you don't need to run it on a restored backup. If you don't have such a maintenance window and running it during your quietest time still results in users screaming, then running it on a restored backup somewhere else is vastly preferable to not running it at all.
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply