DBCC CHECKDB Questions on general practice

Question

Post reply

DBCC CHECKDB Questions on general practice

taseedorf

SSC Eights!

Points: 917
More actions
January 27, 2014 at 8:11 am

#287734

I'm going to set up DBCC CHECKDB to check all databases on a server with about 50 databases, the largest being about 200 GB.
I've read the guides, blogs, etc.. but have no found this question answered - does running this prevent backups from taking place or anything like that? Does it block other applications or users from doing certain things while it is being run? VCC writer? I'm curious because that will impact when I can run it during the maintenance window timeframe...
Thanks.

Viewing 10 posts - 1 through 9 (of 9 total)

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

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

taseedorf (1/27/2014)
does running this prevent backups from taking place or anything like that?

No

Does it block other applications or users from doing certain things while it is being run?

No.

That said, CheckDB is incredibly IO-intensive and a CPU hog as well. Anything running concurrently with it is likely to take a performance hit, especially if it is also IO intensive. Personally I wouldn't run backups concurrently with CheckDB, but that's partially because I like to know whether or not the database is corrupt before taking a backup, not after.

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

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 2

I'm with Gail. If it's too resource intensive for your workload, run the physical only on the servers. However, make sure you restore a backup and run the full DBCC CHECKDB on another server.

taseedorf SSC Eights! Points: 917 More actions · Answer 3

Thanks, I guess I had read similar things but I like advice from a human who has some cool tag under their title 🙂

We don't have another box to mount the backups to... so I think I will try to use physical only option weekly and monthly, do a full check on our "maintenance day" where our users know stuff may be kind of slow..

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

Be careful. If you do that you really need to retain all backups (full, diff and log) for a month otherwise there's the chance that you could find corruption and not have a good option for fixing it (other than 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

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

taseedorf SSC Eights! Points: 917 More actions · Answer 5

taseedorf

SSC Eights!

Points: 917

January 27, 2014 at 9:09 am

#1683610

Do most people run DBCC check DB daily? Weekly?

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

A rule of thumb is run them often enough so you can recover quickly if corruption found, so if you keep 3 days worth of backups on the server, run it every 3 days.

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

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 7

I try to run daily (We do at SSC), but weekly at the latest. Once you automate it, it's not bad.

There are people that run monthly, or less frequently (including never). The issue, as Gail mentioned, is that it can live in backups for a long time. Personally until I have a clean DBCC, I want all backups since the last clean DBCC.

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

taseedorf (1/27/2014)
Do most people run DBCC check DB daily? Weekly?

Often enough that restoring from a clean backup is always an option. So if you retain your backups for a week, run it at least weekly. If you retain backups for 3 days, run it at least every 3 days.

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

Nadrek SSC-Insane Points: 20039 More actions · Answer 9

taseedorf (1/27/2014)
Do most people run DBCC check DB daily? Weekly?

As often as fits into my maintenance windows. If it runs within my "slowdown OK" daily maintenance window, I run it daily. If it doesn't, I drop down to physical only daily and try that, saving the WITH EXTENDED_LOGICAL_CHECKS, DATA_PURITY checks for weekend "slowdown OK" windows. If physical only daily takes too long, I get upset, and then consider oddball schedules - check all DB's but the largest on odd numbered days, check the largest on even numbered days, etc.

Note that the part after running it is checking the results!