SQL Server 2008 - Run the Check Database Integrity Task with the PHYSICAL_ONLY option?

Question

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.

Answer 1

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.

• Jason: Alright, that's what I'll do. We might just switch it to once a week overall. Thanks!