07 March 2007 13:01 tonyrogerson

SP2 - Check Database Task on Maint only ever does master

No, this isn't the time interval/cleanup bug - its another and its not fixed in the hotfix. 

(Now on connect so please vote for it! https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=262122)

I'm not sure whether this was introduced with SP2 because I've not access to RTM or SP1 at the moment (perhaps somebody can feedback).

Anyway, if you create a maintenance plan and use the 'Check Database Integrity' task and selecting multiple / all databases. If you then run the saved plan the plan only ever checks the master database - you can see this behaviour through Profiler.

What this means out in the field is that your databases are not being checked for consistency so, if a corruption is in there you'll never know until a query based problem occurs, which frankly you'll often not notice (another blog post soon on that).

Essentially the task issues the following SQL against the server you are checking...

USE [FileLoad]

 

go

exec sp_reset_connection

go

DBCC CHECKDB WITH NO_INFOMSGS

 

go

exec sp_reset_connection

go

USE [SnowdonTEST]

 

go

exec sp_reset_connection

go

DBCC CHECKDB WITH NO_INFOMSGS

 

go

exec sp_reset_connection

go

USE [CheckDBPractice]

 

go

exec sp_reset_connection

go

DBCC CHECKDB WITH NO_INFOMSGS

 

go

Fine, however, what is happening is this...

Connect occurs

USE FileLoad

Context of database is correct set

Disconnect occurs

Connect occurs

Context of database is now master

DBCC CHECKDB WITH NO_INFOMSGS

Discconnect occurs

Whoops! Because the DBCC CHECKDB does not have the specific database provided it relies on the database context; it would appear that the GO is causing a logout/login because I've also checked the Execute SQL task and the same behaviour is there too.

Workround so far (from me) is to manually code the DBCC CHECKDB and make sure you don't rely on database context when using GO!

 

Filed under:

Comments

# re: SP2 - Check Database Task on Maint only ever does master

07 March 2007 15:09 by sqldbatips

I've checked an RTM server (Build 1399) and this is issue is not present so it seems to be a regression bug. I haven't tested SP1 yet.

# Another Maintenance Plan bug - Database Integrity Checks

07 March 2007 15:19 by sqldbatips.com blog

Tony has picked up on another issue with SP2 in regard to maintenance plans in that integrity checks

# How to create a corrupt database using BULK INSERT/ UPDATE and BCP - SQL Server as a HEX editor.

This entry shows you how to use a combination of BULK INSERT, UPDATE and BCP queryout to create a database

# SQL 2005 SP2 hot fix from windows update takes over 10 minutes to run!

Don't get caught out with this mess! My client has 3 servers, 2 use database mirroring (one a principal,

# re: SP2 - Check Database Task on Maint only ever does master

06 April 2007 16:00 by Razvan Socol

I think this bug is fixed by the updates contained in the KB articles 934458 and 934459.

Razvan

# re: SP2 - Check Database Task on Maint only ever does master

06 April 2007 16:27 by tonyrogerson

Hi Razvan,

Yes, the links are...

http://www.microsoft.com/downloads/details.aspx?familyid=D09CF5C4-FEC4-4322-9FEE-06A43401CF0C&displaylang=en

http://www.microsoft.com/downloads/details.aspx?amp;displaylang=en&familyid=A7C903FE-35A7-4BB2-8E73-932770099711&displaylang=en

Amazingly you have to pick the right one depending on what build you are on; why they didn't just put one patch out I don't know.

I've not had chance to install these patches yet but with the flack they've had over SP2 I'm confident they've done some proper thorough testing this time....

Tony.