Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

As part of our nightly maintenance, we include the Check Database Integrity Task which is an encapsulation of the DBCC CHECKDB command. It's beginning to take a considerable amount of time due to the size of our database, and after reading up on the command at MSDN, we decided to take their advice:

Therefore, using the PHYSICAL_ONLY option may cause a much shorter run-time for DBCC CHECKDB on large databases and is recommended for frequent use on production systems. We still recommend that a full run of DBCC CHECKDB be performed periodically. The frequency of these runs depends on factors specific to individual businesses and production environments.

So we want to change our plan to run DBCC CHECKDB with the PHYSICAL_ONLY option every night, and then without it once a week. Is there any way to get SQL Server to include the PHYSICAL_ONLY option in the integrity task? Can I somehow modify the T-SQL produced by the task? I can't figure out how to include the option as part of the maintenance plan.

share|improve this question
add comment

1 Answer 1

up vote 3 down vote accepted

No, the Maintenance Plan tasks are very limited.

I recommend using this maintenance script, which has an option for PHYSICAL_ONLY.

Also, consider reducing the frequency of running the checks. Is it really necessary to run them every night? This doesn't just use SQL Server resources -- it's also extra wear & tear on your SAN. Just something to think about.

share|improve this answer
    
+1 I agree 100% –  Martin Sjöberg Aug 13 '12 at 16:39
    
Alright, that's what I'll do. We might just switch it to once a week overall. Thanks! –  Jason Aug 14 '12 at 18:10
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.