SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 DBCC CHECKDB
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ahmeds08
Aged Yak Warrior

India
534 Posts

Posted - 04/10/2013 :  04:31:40  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
Dear Experts,
I would like to know what would be the best time to run DBCC CHECKDB on production servers and how often should i run it.
I have around 8 servers with total 150 databases,so restoring the prod backup to non prod servers and running DBCC CHECKDB on non prod servers will be a very big work.


Thanks,
Javeed.

mohammad.javeed.ahmed@gmail.com

ovc
Starting Member

Romania
35 Posts

Posted - 04/11/2013 :  11:39:05  Show Profile  Reply with Quote
Hi Javeed,

i would run the DBCC CHECKDB if possible during the maintenance window and before taking a full/differential database backup. It does not really make sense to perform a backup of a corrupt database.

The DBCC CHECKDB goes to the whole database content and loads it step by step into memory and checks the integrity. During this period you might see that the page life expectancy is dropping (if the database are big). So the suggestion would be is to run it if possible during the time when no or fewer memory intensive queries are running.

I hope this answers your question.
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5071 Posts

Posted - 04/11/2013 :  11:45:23  Show Profile  Visit russell's Homepage  Reply with Quote
If you don't have a maintenance window large enough, then you can do them on a rotating schedule.

We restore to a non-production server and run it there.

Also, I disagree with ovc. Always backup databases. A backup of a corrupt database is a lot better than no backup at all.
Go to Top of Page

ahmeds08
Aged Yak Warrior

India
534 Posts

Posted - 04/12/2013 :  06:23:07  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
OVC/Russel,
Thanks for your inputs.

mohammad.javeed.ahmed@gmail.com
Go to Top of Page

prett
Posting Yak Master

USA
181 Posts

Posted - 04/16/2013 :  00:26:32  Show Profile  Visit prett's Homepage  Reply with Quote
Large database takes long time for CheckDB checking. In order to compensate for this, there are a few other options:

Option 1: Have a long down time to accomodate the checking.

Option 2: Restore a recent backup to a test server and have it check there (this will not find any *new* storage errors)

Option 3: Split the database up into filegroups and run DBCC CHECKFILEGROUP and stagger that out on different days run different filegroups.

For more indepth information on this, please check Paul Randal's blog about this here: http://www.sqlskills.com/blogs/paul/post/CHECKDB-From-Every-Angle-Consistency-Checking-Options-for-a-VLDB.aspx
Go to Top of Page

ahmeds08
Aged Yak Warrior

India
534 Posts

Posted - 04/16/2013 :  02:29:34  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
Hi Prett,
My largest database is around 200 GB.
will also have a look at paul randal's blog.

Thanks.

mohammad.javeed.ahmed@gmail.com
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000