Schedule DBCC CheckDB

Question

Schedule DBCC CheckDB

jralston88

SSCommitted

Points: 1931
More actions
June 15, 2012 at 7:29 am

#271419

I'm a relative newbie with SQL Server admin tasks. Recently I noticed that the two prior dba's did not set up DBCC CheckDB for our 2005 and 2008 instances. There are many databases involved. I'm going to set up the maintenance plans and schedule, etc.
My question - how frequent should the DBCC CheckDB be run? Some databases are lightly used. Others are heavily used during business hours and 24 X 7.
Aside from that the System, ReportServer and ReportServerTempDB databases also come to mind for the DBCC CheckDB - again what's the frequency? Do I configure based on the database usage?
The only thing I have going for this situation is that the backups (.bak + .trn) are created daily and are copied via third party software off the SQL Server instances to another storage device.
Any comments / URLs would be appreciated. Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Often enough that should corruption be found, restoring from a clean set of backups is always an option.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

george sibbald SSC Guru Points: 104210 More actions · Answer 2

which means if you can get hold of backups easily and quickly going back say 3 days, run checkdb every 3 days.

---------------------------------------------------------------------

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

And if you discard backups after a week, run checkDB more often than once a week.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

jralston88 (6/15/2012)
I'm a relative newbie with SQL Server admin tasks. Recently I noticed that the two prior dba's did not set up DBCC CheckDB for our 2005 and 2008 instances. There are many databases involved. I'm going to set up the maintenance plans and schedule, etc.
My question - how frequent should the DBCC CheckDB be run? Some databases are lightly used. Others are heavily used during business hours and 24 X 7.
Aside from that the System, ReportServer and ReportServerTempDB databases also come to mind for the DBCC CheckDB - again what's the frequency? Do I configure based on the database usage?
The only thing I have going for this situation is that the backups (.bak + .trn) are created daily and are copied via third party software off the SQL Server instances to another storage device.
Any comments / URLs would be appreciated. Thanks.

CHECKDB is a resource-intense process and running it on instances hosting 24x7 databases may not be a suitable option. For those instances, one option is to restore a backup of the databases to another server, e.g. a non-production server, and running CHECKDB on the database there. There is a slim chance you could see a false-positive, i.e. corruption could be detected in the database that was introduced on the non-production server, but if CHECKDB runs clean then you can be confident your production database is corruption-free.

edit: clarify to use "instance" instead of "database" where relevant

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

jralston88 SSCommitted Points: 1931 More actions · Answer 5

Thanks guys - good information that will be put to use!!