Why do Sql Server 2005 maintenance plans use the wrong database for dbcc checkdb?

Question

This is a problem I have seen other people besides myself having, and I haven't found a good explanation.

Let's say you have a maintenance plan with a task to check the database, something like this:

USE [MyDb]
GO
DBCC CHECKDB with no_infomsgs, all_errormsgs

If you go look in your logs after the task executes, you might see something like this:

08/15/2008 06:00:22,spid55,Unknown,DBCC CHECKDB (mssqlsystemresource) executed by NT AUTHORITY\SYSTEM found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
08/15/2008 06:00:21,spid55,Unknown,DBCC CHECKDB (master) executed by NT AUTHORITY\SYSTEM found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.

Instead of checking MyDb, it checked master and msssqlsystemresource.

Why?

My workaround is to create a Sql Server Agent Job with this:

dbcc checkdb ('MyDb') with no_infomsgs, all_errormsgs;

That always works fine.

08/15/2008 04:26:04,spid54,Unknown,DBCC CHECKDB (MyDb) WITH all_errormsgs<c/> no_infomsgs executed by NT AUTHORITY\SYSTEM found 0 errors and repaired 0 errors. Elapsed time: 0 hours 26 minutes 3 seconds.

Answer 1

For starters, always remember that GO is not a SQL keyword; it is merely a batch seperator that is (generally) implemented/recognized by the client, not the server. So, depending on context and client, there really is no guarantee that the current database is preserved between batches.

Answer 2

If you are using a maintenance plan you'd probably be better off use the check database integrity task. If you really want to run you own maintenance written in t-sql then run it using a step in a job, not in a maintenance plan and the code above will work ok. Like Stu said the GO statement is client directive not a sql keyword and only seems to be respected by isql, wsql, osql, etc, clients and the sql agent. I think it works in DTS packages. Obviously, not in DTSX, though.

Answer 3

@Rob, both the "Check database integrity" task and manually entering the Sql result in the incorrect behavior. I think you (and @Stu) are right about

USE [MyDb]
GO

not working as expected, but that still doesn't explain why both master and mssqlsystemresource get checked instead.

Answer 4

You have a check datasbase integrity task and you double-clicked it choose MyDb and when the plan runs it only checks master?? weird. Are you sure you don't another plan running?