Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Master database failing integrity check

Status
Not open for further replies.
tmunson99

tmunson99

MIS
Jun 18, 2004
62
US
My Master databases is failing the integrity check from the database maintenance plan. I also ran dbcc checkdb on the Master database and also received the following errors.

CHECKDB found 0 allocation errors and 5 consistency errors in table 'sysmessages' (object ID 36).

CHECKDB found 0 allocation errors and 1 consistency errors in table '(Object ID 11272192)' (object ID 11272192).

CHECKDB found 0 allocation errors and 1 consistency errors in table '(Object ID 635859997)' (object ID 635859997).
DBCC results for 'dtproperties'.

What is the best and safest method to fix this? I do have backups of the Master database but I don't know what the repercussions of restoring this is.
 
Sort by date Sort by votes
There should be a note in there about what repair level needs to be used to repair the database. What is the level is said to use? You'll want a full backup first.

Restoring your master database is a major undertaking.

After restoring the master database you'll need to detach all your user databases and reattach them. They should all come in is suspect mode. Any user accounts, error messages, linked servers that you have created since the backup will be lost. As will any fixed server role changes.
What are the names of the objects that have the error. Object 36 is sysmessages (as shown above), what are the other two?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

[noevil]
(Not quite so old any more.)
 
Upvote 0 Downvote
This is the entire output of dbcc checkdb - sorry it's so lengthy. I appreciate your help!

Server: Msg 8928, Level 16, State 1, Line 1
Object ID 36, index ID 0: Page (1:352) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 36, index ID 0, page (1:352). Test (m_freeCnt == freeCnt) failed. Values are 3282 and 8046.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 36, index ID 0, page (1:352). Test (emptySlotCnt == 0) failed. Values are 25 and 0.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 36, index ID 0: Page (1:353) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 36, index ID 0: Page (1:354) could not be processed. See other errors for details.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 11272192, index ID 0, page ID (1:354). The PageId in the page header = (0:11272256).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 635859997, index ID 1223, page ID (1:353). The PageId in the page header = (59072:1744840166).
DBCC results for 'master'.
DBCC results for 'sysobjects'.
There are 1386 rows in 23 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 106 rows in 4 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 4990 rows in 93 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 26 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 2127 rows in 1065 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 2 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 826 rows in 4 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 14 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysproperties'.
There are 0 rows in 0 pages for object 'sysproperties'.
DBCC results for 'sysdepends'.
There are 5571 rows in 33 pages for object 'sysdepends'.
DBCC results for 'sysreferences'.
There are 0 rows in 1 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 1 pages for object 'sysfulltextcatalogs'.
DBCC results for 'sysfulltextnotify'.
There are 0 rows in 0 pages for object 'sysfulltextnotify'.
DBCC results for 'sysdatabases'.
There are 19 rows in 1 pages for object 'sysdatabases'.
DBCC results for 'sysxlogins'.
There are 35 rows in 1 pages for object 'sysxlogins'.
DBCC results for 'sysdevices'.
There are 6 rows in 1 pages for object 'sysdevices'.
DBCC results for 'sysmessages'.
There are 3750 rows in 150 pages for object 'sysmessages'.
CHECKDB found 0 allocation errors and 5 consistency errors in table 'sysmessages' (object ID 36).
DBCC results for 'sysconfigures'.
There are 38 rows in 1 pages for object 'sysconfigures'.
DBCC results for 'sysservers'.
There are 1 rows in 1 pages for object 'sysservers'.
DBCC results for 'syslanguages'.
There are 33 rows in 3 pages for object 'syslanguages'.
DBCC results for 'syscharsets'.
There are 114 rows in 33 pages for object 'syscharsets'.
DBCC results for 'sysaltfiles'.
There are 38 rows in 8 pages for object 'sysaltfiles'.
DBCC results for 'sysfilegroups'.
There are 1 rows in 1 pages for object 'sysfilegroups'.
CHECKDB found 0 allocation errors and 1 consistency errors in table '(Object ID 11272192)' (object ID 11272192).
DBCC results for 'spt_committab'.
There are 0 rows in 1 pages for object 'spt_committab'.
DBCC results for 'spt_provider_types'.
There are 25 rows in 1 pages for object 'spt_provider_types'.
CHECKDB found 0 allocation errors and 1 consistency errors in table '(Object ID 635859997)' (object ID 635859997).
DBCC results for 'dtproperties'.
There are 0 rows in 1 pages for object 'dtproperties'.
DBCC results for 'MSreplication_options'.
There are 2 rows in 1 pages for object 'MSreplication_options'.
DBCC results for 'spt_datatype_info_ext'.
There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
DBCC results for 'spt_datatype_info'.
There are 36 rows in 1 pages for object 'spt_datatype_info'.
DBCC results for 'spt_server_info'.
There are 29 rows in 1 pages for object 'spt_server_info'.
DBCC results for 'spt_monitor'.
There are 1 rows in 1 pages for object 'spt_monitor'.
DBCC results for 'spt_values'.
There are 730 rows in 7 pages for object 'spt_values'.
DBCC results for 'spt_fallback_db'.
There are 0 rows in 0 pages for object 'spt_fallback_db'.
DBCC results for 'spt_fallback_dev'.
There are 0 rows in 0 pages for object 'spt_fallback_dev'.
DBCC results for 'spt_fallback_usg'.
There are 0 rows in 0 pages for object 'spt_fallback_usg'.
CHECKDB found 0 allocation errors and 7 consistency errors in database 'master'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (master ).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 
Upvote 0 Downvote
I should add that all my user databases and front ends are running fine. I have 5 different databases/systems on this server.
 
Upvote 0 Downvote
Your master database has some problems. You can try a DBCC CHECKDB ('master', repair_allow_data_loss). Unfornitually it will loose data when it does this. And this being master that would be very bad.

I'm not sure what those other two objects are. If this were me I'd run:
Code: 
DBCC CHECKDB ('master', REPAIR_REBUILD)
GO
and see if it is able to repair the problems in the two objects besides 36. Since 36 is the sysmessages table you can easily correct that table by using the messages.sql file on your SQL CD. That file will reload all the default messages into your sysmessages table. Any custom error messages that are lost will need to be put back in.

If the REPAIR_REBUILD isn't able to fix the other two problems I'd be looking at restoring the master database from a good backup.

You'll need to bring the SQL Server up in single user mode to run dbcc checkdb against the master database.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

[noevil]
(Not quite so old any more.)
 
Upvote 0 Downvote
I ended up calling Microsoft. It was worth the $245. Based on the repair level suggested, their suggestion was to restore the master database. It actually wasn't that bad. I took backups of all the databases (just in case). They had me run a script that created a stored procedure that scripted out all the logins in case they were lost. TechKnowledge 246133. Then I stopped SQL server and brought it back up in single user mode and restored the master database from a recent GOOD backup that I had. Luckily no users, databases, or other significant objects had been added since the backup. My backup of the master database was only a week old. When I brought SQL back up a second time all was good.
 
Upvote 0 Downvote
Excellent, glad it worked out. Sometimes called MS is just the best bet.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

[noevil]
(Not quite so old any more.)
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

CodeWell
  • Locked
  • Question
SQL Server 2017 Install Issue - Database Not in Configuration Manager
Replies
0
Views
657
CodeWell
CodeWell
baran121
  • Locked
  • Question
take a database from system databse place
Replies
2
Views
606
iambob
iambob
bethabernathy
  • Locked
  • Question
Front End Access 365 Database dsn Connection Fails when attempting to link to 2016 SQL server
Replies
1
Views
553
dhookom
dhookom
torquinian
  • Locked
  • Question
Error 0x80131904 when trying to connect to database
Replies
0
Views
553
torquinian
torquinian
RRinTetons
  • Locked
  • Question
Logging into a database using SSMS from non-domain machine
Replies
1
Views
599
fredericofonseca
fredericofonseca

Part and Inventory Search

Sponsor

Back
Top