January 20, 2009 at 10:18 am
After a major incident where we had to move 2 sql instances to a new box, the DBCC CHECKDB on master reports 5962 consstency errors on one of the instances. Also reindexing on master fails.
The instances run fine now. Is it safe to use checkdb with repair options on the master.
Checkdb reports a lot of Msg 8909 errors
January 20, 2009 at 1:25 pm
Do you have a backup of master? repair is not the recommended solution, especially not for a system database.
Can you please run the following, copy the results to a text file, zip it and attach it to your post?
DBCC CHECKDB ('master') WITH NO_INFOMSGS, ALL_ERRORMSGS
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
January 21, 2009 at 2:29 am
Hi thanks for your reply,
The master has already been restored from backup. Restoring an older backup (older than 2 weeks) is not an option, because of all the changes that have been made since.
The instance and all applications are running fine.
Attached the results of DBCC CHECKDB with NO_INFOMSGS, ALL_ERRORMSGS
What are the risks to just leave it and what are the options for a save repair?
Thanks for your help
January 21, 2009 at 4:04 am
Update:
I've restored last nights backup of master as a userdatabase and run DBCC CHECKDB ALLOW_DATA_LOSS.
This solves the consistency errors.(REPAIR_FAST and REPAIR_REBUILD didn't solve anything)
I can also reindex.
What's the best option (after backing up the master):
-Restore this userdatabase as the master
-or run DBCC CHECKDB ('master', allow_data_loss)
January 21, 2009 at 6:30 am
xanthos (1/21/2009)
Update:I've restored last nights backup of master as a userdatabase and run DBCC CHECKDB ALLOW_DATA_LOSS.
This solves the consistency errors.
Yes, it's fixed the corruption, but do you know what data that statement has discarded and from what tables?
-Restore this userdatabase as the master
-or run DBCC CHECKDB ('master', allow_data_loss)
I don't think you'll be able to restore the user database back as master. Even if you could, I would not recommend it. You don't know what data the checkDB has deleted and lost data in the master database may have unpleasant consequences in the future.
What changes have you made to master that you can't restore an older backup? Typically master contains logins, linked servers and references to the user databases. That should all be re-creatable. Are you putting user tables in the master database?
I'm going to refer this to an expert on corruption. If this was my server, I'd restore the older backup and redo any necessary changes, because if there's problems with master, the entire instance will fail.
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
January 21, 2009 at 8:04 am
Hi Gail,
Thanks for your suggestions. Checkdb is running OK now!!!!!! Without taking the instance down!!!!
Check these results:
CHECKDB found 0 allocation errors and 0 consistency errors in database 'master'
I've run DBCC CHECKDB ('master', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS on the master. The results stated that it deleted ojects of a table, that turned out to be a user table that my predecessor had created. This table was not in use any more.
One more error was generated for dataseid 6. This was an admin db to store profiler trace files.
Deleting the user table in master and retaching the database, caused DBCC CHECKDB to run succesfully.
Restoring an older backup was no option because all datafiles had been moved to different volumes and application support had already created a lot of new logins. It would mean a lot of downtime. Time I wouldn't heve been given.
Thanks for all your help
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply