February 12, 2011 at 1:27 pm
As a dba, when do I need to run dbcc checkdb?
Is it until someone reporting a database error or corruption?
Thanks
February 12, 2011 at 1:39 pm
Regularly.
When someone complains about corruption it's already too late. To recover from corruption without data loss you need to know when it started so that you know what backup is clean. If you only run it when someone reports corruption, you will have no idea when it started and your recovery options will be limited.
I did a webcast that covered that and other points earlier this week. The recording is available.
http://www.quest.com/events/ListDetails.aspx?ContentID=13506
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
February 12, 2011 at 2:22 pm
Thank you,
I will watch the video later tonight.
For now, could you tell me what usually is the best practice to run dbcc checkdb? every day, off hour or others?
Thanks
February 12, 2011 at 3:38 pm
Anna_SQL (2/12/2011)
For now, could you tell me what usually is the best practice to run dbcc checkdb? every day, off hour or others?
It depends (like many things in SQL)
How large are your maintenance windows?
How stable is your IO subsystem?
How long do you retain your backups?
Daily or weekly is common. On a large DB I used to work with we did it fortnightly, because we didn't have time to do it weekly. I wouldn't suggest less often than that, the longer the intervals, the more difficult it's likely to be to recover.
It is very CPU and IO intensive, so you do not want to run it while users are busy, unless you have lots of spare capacity.
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
February 12, 2011 at 4:04 pm
Anna, to repeat verbatim what Gail just said (private joke :-)), and it is probably covered in her webcast, but for me how long you retain your backups on disk where they are quickly recoverable is an important factor, if you have backups going back 3 days then run dbcc checkdb at least every 3 days so you have a good chance of having a clean backup.
I am going to presume Gails webcast will cover anything further I might say but for the benefit of those in the future who might read this thread please feel free to post back the most important factors that influenced your decision.
---------------------------------------------------------------------
February 13, 2011 at 7:20 pm
Which options (if any) are typically used with checkdb as part of normal database admin work, not in a corruption situation ?
February 13, 2011 at 10:32 pm
My preferences are No_Infomsgs and all_errormsgs. No need to see all those x rows in y pages messages and, if there are errors, you don't want to be limited to the first 200.
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
February 14, 2011 at 3:32 am
PHYSICAL_ONLY has its uses with a VLDB or a shortage of time, but should not be used on Every run of checkdb.
---------------------------------------------------------------------
February 14, 2011 at 7:16 am
Most of my databases are "normal" and are up to 200 gig, but I have one that is 1.2 Terebytes and contains 90% image data. (Voice recording migrated from SQL 2000)
Any thoughts on the best options for this one ?
PHYSICAL_ONLY as mentioned by George ?
February 14, 2011 at 7:26 am
If you always use physical only, you WILL miss many corruptions. It is not an option that you should use onb every execution.
No_infomsgs, all_errormsgs are the normal ones to use.
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
February 14, 2011 at 8:19 am
Take it from me.... run it as often as your system allows.... 7 days a week if you can....
A few weeks ago I found out the hard way...
We inheritied two very old SQL 2000 installations and the one they had a maint plan to run DBCC CheckDB only once a week. It failed with some corruption. With Gails help (thanks Gail again) the corruption was not severe and we were able to fix the db. However, if we weren't able to fix the corruption we would have had absolutely no idea when the corruption happened. So... we would have been restoring 48gig dbs and then running DBCC CHeckDB to see where the corruption happened. This is NOT a place where a DBA wants to be when the users are freaking out about being shut out of a critical DB.
February 14, 2011 at 9:06 am
Thank you all for the suggestions
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply