Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ahmeds08
Aged Yak Warrior
737 Posts |
 Posted - 2013-04-10 : 04:31:40
|
| 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
35 Posts |
Posted - 2013-04-11 : 11:39:05
|
| 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. |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-04-11 : 11:45:23
|
| 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. |
|
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2013-04-12 : 06:23:07
|
| OVC/Russel,Thanks for your inputs.mohammad.javeed.ahmed@gmail.com |
|
|
|
prett
Posting Yak Master
212 Posts |
Posted - 2013-04-16 : 00:26:32
|
| 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 |
|
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2013-04-16 : 02:29:34
|
| 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 |
|
|
|
|
|
|
DBCC CHECKDB