SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Running DBCC CHECKDB on system databases


Running DBCC CHECKDB on system databases

Author
Message
HildaJ
HildaJ
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1027 Visits: 571
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.
e4d4
e4d4
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1131 Visits: 2399
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218193 Visits: 46278
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, MVP, M.Sc (Comp Sci)
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


HildaJ
HildaJ
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1027 Visits: 571
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.
e4d4
e4d4
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1131 Visits: 2399
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.
HildaJ
HildaJ
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1027 Visits: 571
I'm creating a maintenance plan to check database integrity through SQL Server Enterprise manager, please see attached images.
Attachments
image1.gif (22 views, 15.00 KB)
image2.gif (15 views, 15.00 KB)
e4d4
e4d4
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1131 Visits: 2399
http://support.microsoft.com/kb/290622
Uncheck "Attempt to repair minor problems"
GilaMonster
GilaMonster
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218193 Visits: 46278
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, MVP, M.Sc (Comp Sci)
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


HildaJ
HildaJ
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1027 Visits: 571
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218193 Visits: 46278
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, MVP, M.Sc (Comp Sci)
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search