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: SQL Server