CODEWITHSUNDEEP
sql-serversql-server-2005
Eric Z Beard
Eric Z Beard

Reputation: 38406

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

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.

Upvotes: 0

Views: 1084

Answers (3)

Stu
Stu

Reputation: 15769

For starters, always remember that GO is not a SQL keyword; it is merely a batch separator 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.

Upvotes: 1

Booji Boy
Booji Boy

Reputation: 4582

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?

Upvotes: 0

Booji Boy
Booji Boy

Reputation: 4582

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.

Upvotes: 1

Related Questions