THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

Be aware: Sp2, maint plans and CHECKDB

Tony Rogerson brought to my attention that with sp2, a plan that does CHECKDB seems to always do it against the master database. Technicalities seems to be in how SSIS executes SQL commands, separates batches and connection pooling (sp_reset_connection).

I created a plan to do CHECKDB against all databases, and this is what I found in my errorlog file (irrelevant info removed):

DBCC CHECKDB (master) WITH no_infomsgs executed by ...
DBCC CHECKDB (mssqlsystemresource) WITH no_infomsgs executed by...
DBCC CHECKDB (master) WITH no_infomsgs executed by ...
DBCC CHECKDB (mssqlsystemresource) WITH no_infomsgs executed by...
DBCC CHECKDB (master) WITH no_infomsgs executed by ...
DBCC CHECKDB (mssqlsystemresource) WITH no_infomsgs executed by...
....

See the problem? I haven't seen any official words about this yet, so all I can say is to check your errorlog file after a maint plan has been executed, and if you don't see the proper databases, create a regular Agent job for this instead.

Published Thursday, March 08, 2007 10:44 AM by TiborKaraszi
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Uri Dimant said:

Tibor

Yes , it's true, I took a  look at View_TSQL and it shown that it uses correct scope for selected databases. I have not seen this behaviour  before I installed SP2a

March 8, 2007 4:49 AM
 

TiborKaraszi said:

Hi Uri,

Yes, I'm sceptical about the View T-SQL option. I know that was is displayed for us isn't (neccesarily) the same as what is actually executed.

For instance a reorg task doesn't show a cursor looping each table, it will show a reorg command for each table *currently* in the database.

And in this case, the View TSQL show us:

USE [master]

GO

DBCC CHECKDB WITH NO_INFOMSGS

GO

USE [model]

GO

DBCC CHECKDB WITH NO_INFOMSGS

We know that GO isn't a SQL command, so this cannot be the SQL submitted by SSIS. This is why I prefer to use Profiler to see *what is actually* executed by SSIS maint plans.

March 8, 2007 9:30 AM
 

Elliot said:

Just a FYI -

If you use DBCC CHECKDB (User::Variable) it will work

November 16, 2012 6:15 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement