August 12, 2013 at 1:36 pm
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.
August 13, 2013 at 1:34 am
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.
August 13, 2013 at 1:48 am
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
August 13, 2013 at 7:44 am
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.
August 13, 2013 at 7:54 am
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.
August 13, 2013 at 8:03 am
I'm creating a maintenance plan to check database integrity through SQL Server Enterprise manager, please see attached images.
August 13, 2013 at 8:17 am
http://support.microsoft.com/kb/290622
Uncheck "Attempt to repair minor problems"
August 13, 2013 at 8:20 am
e4d4 (8/13/2013)
http://support.microsoft.com/kb/290622Uncheck "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
August 13, 2013 at 8:51 am
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.
August 13, 2013 at 9:35 am
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
August 13, 2013 at 9:45 am
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.
Ok, let me start over. Hopefully if I give you a little more background you'll see where I'm coming from.
I'm technicially new to this job as a DBA and just recently started monitoring this system. It seems that no one was running any data integrity or indexing checks at all before only daily backups, not even backups on the system dbs. One of the first steps I took was to check on the backup jobs and incorporated backups on the system dbs, I also established data and index checks on a routine basis. The index checks are running without any problems on the user dbs, fragmentation is looking good and I was able to manage to run a job once a week on the user databases that run the DBCC ChECKDB against them. The system databases were the only ones that I had left. Backups are running fine with out any errors, however the data integrity is not running logging errors. That's what started this post.
I don't know for "sure" that my backups on the system dbs are good since I haven't restored any of them. I'd like to run the data integrity to see if there's anything wrong with them at all. If it does then I'll continue running my backups as ususal, if it doesn't then since I don't have really any good backups then I'll consider moving the databases to a new sql server. That's the solution I'm trying to avoid.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply