Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Running DBCC CHECKDB on system databases Expand / Collapse
Author
Message
Posted Monday, August 12, 2013 1:36 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 8, 2014 9:14 AM
Points: 163, Visits: 471
This question is intended for SQL Server 2000, please advise if this is the wrong forum, I didn't find one for that.

I need to run the DBCC CHECKDB on my system databases. I have schedule a job to run at night, however, the job is failing and with the following output.

Executed as user: username. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.

I ran the command SELECT * FROM [msdb].[dbo].[sysdbmaintplan_history] AS sh to search for more information on the maintenance plan history and the message stated "[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode." Therefore, I was wondering if this is the correct way to address this problem. If I want to check the integrity on my system databases would the following script be correct and appropriate? Thank you in advance.

USE master;
GO

ALTER DATABASE [model]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

DBCC CHECKDB(N'model')
GO

ALTER DATABASE [model]
SET MULTI_USER;
GO


ALTER DATABASE [msdb]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

DBCC CHECKDB(N'msdb')
GO

ALTER DATABASE [msdb]
SET MULTI_USER;
GO


Also, if you need to run the DBCC CHECKDB against the master database, do you switch to another database then run the alter command to single mode and then the command?

Like this:

USE msdb;
GO

ALTER DATABASE [master]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

DBCC CHECKDB(N'master')
GO

ALTER DATABASE [master]
SET MULTI_USER;
GO


Thank you a bunch.
Post #1483450
Posted Tuesday, August 13, 2013 1:34 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 8:38 AM
Points: 223, Visits: 1,725
Why you run repair and not only check "Repair statement not processed."? To repair the db must be in single mode, to check can be online.
Post #1483592
Posted Tuesday, August 13, 2013 1:48 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:12 AM
Points: 43,016, Visits: 36,176
You can't set master to single user mode and you don't need single user mode to run a normal checkDB.

Why are you running repair? What's damaged? Do you know what repair will do?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1483595
Posted Tuesday, August 13, 2013 7:44 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 8, 2014 9:14 AM
Points: 163, Visits: 471
You can't set master to single user mode and you don't need single user mode to run a normal checkDB.

I'm running SQL Server 2000 and according to the error log I need to run the command DBCC CHECKDB against a database while it's in single mode. If the database is set to multi_user the error shows in the log.

Why are you running repair? What's damaged? Do you know what repair will do?

I'm running the command to check for data integrity, I'm hoping nothing is damage and the DBCC CHEKCDB hopefully will tell me that.
Post #1483738
Posted Tuesday, August 13, 2013 7:54 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 8:38 AM
Points: 223, Visits: 1,725
No. To run
dbcc checkdb('master')

db can be online. Error log says, that you are trying to run one of: dbcc checkdb repair
Show script that you use.
Post #1483745
Posted Tuesday, August 13, 2013 8:03 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 8, 2014 9:14 AM
Points: 163, Visits: 471
I'm creating a maintenance plan to check database integrity through SQL Server Enterprise manager, please see attached images.

  Post Attachments 
image1.gif (4 views, 15.07 KB)
image2.gif (4 views, 15.08 KB)
Post #1483750
Posted Tuesday, August 13, 2013 8:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 8:38 AM
Points: 223, Visits: 1,725
http://support.microsoft.com/kb/290622
Uncheck "Attempt to repair minor problems"
Post #1483758
Posted Tuesday, August 13, 2013 8:20 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:12 AM
Points: 43,016, Visits: 36,176
e4d4 (8/13/2013)
http://support.microsoft.com/kb/290622
Uncheck "Attempt to repair minor problems"


This. It's the stupidest option I've ever seen in a maintenance plan (back up tail of log being close)

If this is happening, it means that there's corruption in master however. How are your backups?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1483763
Posted Tuesday, August 13, 2013 8:51 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 8, 2014 9:14 AM
Points: 163, Visits: 471
Very interesting indeed. I unselected the option and run it tonight and see what happens. However, if I removed this option how am I suppose to repair any errors if found? I guess manually?

As far as my other backups, they all seem ok. This box only keeps two main databases that will be replace by the end of the year. I just want to make sure it runs till then.

Thanks for your comments and advice.
Post #1483784
Posted Tuesday, August 13, 2013 9:35 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:12 AM
Points: 43,016, Visits: 36,176
HildaJ (8/13/2013)
However, if I removed this option how am I suppose to repair any errors if found? I guess manually?


By restoring a clean backup. Repair is usually a last resort when you don't have backups and it does just about nothing on master anyway as repair doesn't fix system tables.

How do you know your backups (of master) are good? Been restoring them and running checkDB on the restored copy? If not, then you have no guarantee those backups are even usable.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1483821
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse