August 12, 2010 at 10:24 am
I use DBCC CHECKDB nightly before I reorg or rebuild indexes on LARGE tables.
Recently I was asked "Does anyone actually do this in real life?"
It adds about 1.25 hours to the length of the job.
Comments?
Ed Otto
Suppport Developer
Synovia Inc.
Indianapolis, IN
August 12, 2010 at 10:31 am
It is used by people who have a desire to know if their database is corrupt while they still have time to do something to fix it without a catastrophic loss of data.
August 12, 2010 at 10:41 am
Absolutely!
Unless you are 100% totally certain that your IO subsystem is perfectly configured, perfectly stable, that no disks are ever going to go wrong, no controllers ever glitch, no filter drivers ever misbehave you need to run that on a regular basis. That's the only way to tell if you have corruption in your database (other than waiting for a user to trip over it and trigger a high severity error)
Take a read through the data corruption forums here and look at the horror stories of people who didn't run that regularly and, when their databases got corrupted, found out too late to recover without losing 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
August 12, 2010 at 11:21 am
To answer a different interpretation of the question - no, people do not generally run CHECKDB before doing a nightly index rebuild.
How often should you run CHECKDB? It depends on you and your ability to absorb the resource hit of running it. See my CHECKDB From Every Angle blog post series for lots of info and FAQs (start at the oldest posts first).
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
August 12, 2010 at 11:32 am
I'm fond of running it before my full backups (providing there's enough time available). That way I know I'm not backing up a corrupt database.
My personal rule-of-thumb - CheckDB should be run often enough that restoring from backup is always an option if corruption is detected. Hence if you retain two weeks of backups, you should be running checkDB (or a combination of checkAlloc, CheckCatalog and CheckTable) at least once a fortnight. Less often than that and you risk not having a clean backup to restore from if necessary (or not having the log backups necessary to restore to point-in-time)
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
August 12, 2010 at 11:47 am
Nowadays I tend to recommend taking the backup, restoring it somewhere else and then running CHECKDB on the restored copy of the database. It checks that your database is corruption free without the resource hit on the prod system, and checks your backup is ok too.
And I like to recommend at least once a week.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
August 12, 2010 at 5:09 pm
I do this daily in all databases that I have.
Fabrício França Lima
MCITP – Database Administrator
https://www.fabriciolima.net/Blog
August 13, 2010 at 6:24 am
You could run DBCC CHECKDB against the database after you have performed one of your routine test restores.
Mine's the coat with the Kevlar back protector in it ... :Whistling:
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply