DBCC CheckDB

Hi:

As part of database maintenance plan, I am using the option (checkbox) for checking the integrity of the database before backing up database or transaction log.

I am not sure when this database integrity check happens (DBCC CheckDB). They seem to be happening at different times not necessarily right before database backup. Does anyone have more details on this?

Also I am backing up my user and system databases every night with database integrity checkup. This is a live eCommerce site. Usually database integrity check should not take long but if it does, will it block users. Is it good idea to do it every night? I was thinking of backing up system database only on weekends.

Thanks

Hi Mike. From the sounds of it, you're referring to a SQL 2000 system I assume.

Can't say really if it's a good idea or not to perform the checkdb nightly, I can surely say it's not a bad thing to do it, other than if it causes problems like you stated (i.e. blocking)...another possible problem could be that it 'thrashes' the buffer pool, and possibly leaves unneeded data in your cache rather than data that is normally there, so in the morning you may see some slowdown on queries until the wanted data resides in cache again.

One thing many people will do on larger, OLTP type systems is perform a checkdb on the production system weekly (or something like that) and perform a nightly checkdb on a restore of the live database on a test server.

As for the schedule of when they occur, if you go to the 'integrity' tab of the existing maintanence plan, at the bottom of the screen there will be a schedule listed there for the integrity check portion of the plan only. Each of the tabs (Optimizations, Integrity, Backup, etc.) have a seperate schedule assigned that defines when that particular operation is begun. Additionaly, you could check the schedule of the SQL Agent Jobs that are created to actually kick off the maintanence tasks.

HTH