Error in master db(checkdb) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

Error in master db(checkdb)

Author

Topic

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-05-15 : 17:30:43

Gurus
when i run checkdb i get this error
"Database 'master' consistency errors in sysobjects, sysindexes, syscolumns, or systypes prevent further CHECKDB processing."
What can be the cause?Can i fix this with repair option of check db?
Please advice

Regards
Nitin

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2006-05-16 : 01:49:16

You've got a corrupt critical system table in master - specifically, you've got a problem with a leaf-level page of a clustered index of one of the listed tables. You cannot repair these errors with DBCC - you must restore (deleting a page from a system table doesn't make any sense - you'd effectively be deleting metadata).

Hopefully you have a backup of master and have practiced disaster recovery of it?

To determine which table has the problem, please post the output from running the following in master:

DBCC CHECKTABLE (sysobjects) WITH NO_INFOMSGS
go
DBCC CHECKTABLE (sysindexes) WITH NO_INFOMSGS
go
DBCC CHECKTABLE (syscolumns) WITH NO_INFOMSGS
go
DBCC CHECKTABLE (systypes) WITH NO_INFOMSGS
go

Thanks

Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine + SQL Express
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-05-17 : 12:56:40

hi
I understand this.But what is the cause of this error?it keeps on happening once in 45 days.
Regards
Nitin

Kristen
Test

22859 Posts

Posted - 2006-05-17 : 13:12:51

Hardware fault?

Check event log to see if there is any evidence there?

Inappropriate shut down of the server?

Kristen

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-05-17 : 13:24:14

hi
I think you are right.Thanks for the response.
Regards
Nitin

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-05-17 : 13:49:47

Hi
One more thing.When we resore the master from the old back does it overwrite the old master db with replace option?
Regards

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2006-05-17 : 14:10:31

Yes.

Did you figure out the root cause of the problem and which table it was that was corrupt?

Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine + SQL Express
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-05-17 : 14:27:11

Hi
Yes the problem is with syscolumns.I am still looking for the hardware issue.
Regards
Nitin

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2006-05-17 : 15:19:02

Can you post the DBCC CHECKTABLE output?

Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine + SQL Express
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-05-17 : 15:31:53

Hi
This is the output

Server: Msg 8928, Level 16, State 1, Line 1
Object ID 3, index ID 0: Page (1:1079) could not be processed. See other errors for details.
Server: Msg 8940, Level 16, State 1, Line 1
Table error: Object ID 3, index ID 0, page (1:1079). Test (IsAligned (sorted[i].offset)) failed. Address 0xb2 is not aligned.
DBCC results for 'syscolumns'.
There are 4915 rows in 86 pages for object 'syscolumns'.
CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'syscolumns' (object ID 3).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (master.dbo.syscolumns ).

Regards
Nitin

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2006-05-17 : 16:23:20

Thanks. Are you comfortable restoring master and continuing from there? It's possible you could work around this without restoring master but it would be tricky...

Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine + SQL Express
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-05-17 : 17:27:27

Hi Paul
The problem i dnt have a backup of master .But i have backup of others dbs.What else can i do?please guide.
Regards
Nitin

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-05-17 : 17:35:33

Paul
One more thing,to transfer the logins i have create a stored proc on the source server(EXEC master..sp_help_revlogin) ,u must have heard about it.We create this sp on source server and execute it and then execute the output of the sp on destination.I have tried this the logins will be created but will it be mapped to the users?In casethis is the url "http://support.microsoft.com/?id=246133"
Regards

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2006-05-17 : 18:23:12

I don't know about that sp.

I got the impression you did have a backup from your earlier comments.

I suggest you call Product Support to help you work through this.

Thanks

Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine + SQL Express
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-05-19 : 13:47:26

hi paul
just adding to the conversation what effect will this error have on my other databases.Please advice.Itz a production server. and we have around 50 databases on it.Till now i have nt planned reinstalling sql server

Regards
Nitin

Kristen
Test

22859 Posts

Posted - 2006-05-19 : 14:09:32

"what effect will this error have on my other databases"

Have you done a CHECKDB on ALL other databases?

DBCC CHECKDB ('MyDataabseName') WITH ALL_ERRORMSGS, NO_INFOMSGS

Personally, if I was in your shoes, I would be having sleepless nights that the damage to MASTER was about to cause new data in MASTER to suffer collateral damaged, which might impact on existing, healthy, databases.

Kristen

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2006-05-19 : 15:45:40

Call product support to help you with this - you've got too much going on to be getting this supported over a forum with large delays - in the meantime, who knows what's happening to your data...

Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine + SQL Express
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-05-19 : 15:51:03

I understand.Thanks paul and kirsten for your valuable time.
Regards
Nitin

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-05-19 : 17:14:12

Hi
are we sure that this is happening due to some hardware issues?
Regards
Nitin

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-19 : 17:19:15

Stop wasting time on a production problem! Get on the phone with MS immediately. You can't afford to have this problem get worse.

http://support.microsoft.com/default.aspx?scid=fh;EN-US;offerprophone

Tara Kizer
aka tduggan

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-05-19 : 17:22:25

Tara
I understand but my network admin wants to know the reason.coz this is happening for the second time in 60 days.
Regards
Nitin

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources