System Assertion Failed during Restoration

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

System Assertion Failed during Restoration

Author

Topic

ciran_patil@yahoo.com
Starting Member

17 Posts

Posted - 2008-04-25 : 10:43:56

Hi All,

I am trying to restore sql 2000 database backup on sql 2005. I get below error.

SQL Server Assertion: File: <mdupgrad.cpp>, line = 3342 Failed Assertion = 'f' No object for Index!. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or

Error: 928, Severity: 20, State: 1.

During upgrade, database raised exception 3624, severity 20, state 1, address 000000000247E5A1. Use the exception number to determine the cause

Error: 3624, Severity: 20, State: 1.

I feel this could be due to upgrade from sql 2000 to sql 2005.
I could restore other 2000 databases successfully to 2005. However only this particular database is throwing this error.

Can anyone advise on this please.

Thanks in Advance

Kiran

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-04-25 : 14:27:52

moved from SQL Server Development (2000) since this forum is monitored by sql server product team

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-25 : 14:31:20

Kiran, did you run DBCC CHECKDB on the problem database like the error suggests? If so, did it have any errors? If so, post the entire output here.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

ciran_patil@yahoo.com
Starting Member

17 Posts

Posted - 2008-04-25 : 14:42:35

Yes, DBCC CHECKDB didn't gave any error.SQL version on which I am trying to restore is SQL 2005 with SP2.

I tried running upgrade advisor wizard, it advised me to change the filegrowth of the files to autogrow. I tried even that. Up on analysis, what I feel is , it is failing at recovery state. Restoration is happening properly.

I tried restoring it with no recovery option. It was successfull (keeping databse in Restoring state). Then I tried RESTORE DATABASE <DBNAME> with Recovery. Again same error.

I will be putting the output of DBCC CHECKDB soon here.

Thanks for looking in to this.

ciran_patil@yahoo.com
Starting Member

17 Posts

Posted - 2008-04-25 : 14:46:13

quote:
Originally posted by ciran_patil@yahoo.com

Yes, DBCC CHECKDB didn't gave any error.SQL version on which I am trying to restore is SQL 2005 with SP2.

I tried running upgrade advisor wizard, it advised me to change the filegrowth of the files to autogrow. I tried even that. Up on analysis, what I feel is , it is failing at recovery state. Restoration is happening properly.

I tried restoring it with no recovery option. It was successfull (keeping databse in Restoring state). Then I tried RESTORE DATABASE <DBNAME> with Recovery. Again same error.

I will be putting the output of DBCC CHECKDB soon here.

Total no of data files for this database is 105. Can this be the reason of error ?

Thanks for looking in to this.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2008-04-25 : 15:36:12

Sounds like the metadata in the 2000 database is logically corrupt (i.e. there's a mismatch between system tables, rather than a physical corruption in one of the tables). Running DBCC CHECKDB on it in 2000 won't find this - as 2000 CHECKDB doesn't include DBCC CHECKCATALOG. I only made that change to CHECKDB in 2005.

Try running DBCC CHECKCATALOG on the 2000 database. What does that return?

Paul Randal
SQL Server MVP, Managing Director, SQLskills.com

ciran_patil@yahoo.com
Starting Member

17 Posts

Posted - 2008-04-25 : 16:10:09

Paul,

DBCC CHECKALLOC looks clean...Find the end result below

File 106. Number of extents = 1768, used pages = 14142, reserved pages = 14144.
File 106 (number of mixed extents = 0, mixed pages = 0).
Object ID 99, Index ID 0, data extents 1, pages 6, mixed extent pages 0.
Object ID 1069559194, Index ID 0, data extents 1767, pages 14136, mixed extent pages 0.
Total number of extents = 994953, used pages = 7694340, reserved pages = 7958511 in this database.
(number of mixed extents = 396, mixed pages = 2055) in this database.
CHECKALLOC found 0 allocation errors and 0 consistency errors in database.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2008-04-25 : 17:36:11

Yup. What I need is the output is the need from DBCC CHECKCATALOG.

Paul Randal
SQL Server MVP, Managing Director, SQLskills.com

ciran_patil@yahoo.com
Starting Member

17 Posts

Posted - 2008-04-28 : 09:10:52

Hi Paul,

Please find the output of DBCC CHECKCATALOG below and advise. I think this is the output you were expecting.

Server: Msg 2513, Level 16, State 2, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSCOLUMNS' and 'SYSOBJECTS'.
DBCC results for 'EOApp'.
Server: Msg 2513, Level 16, State 2, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 1258695782 (object '1258695782') does not match between 'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 2, Line 1
Table error: Object ID 1258695782 (object '1258695782') does not match between 'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSINDEXES' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSINDEXES' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSINDEXES' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSINDEXES' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSINDEXES' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSINDEXES' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSINDEXES' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSINDEXES' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSINDEXES' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSINDEXES' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSINDEXES' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSINDEXES' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSINDEXES' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 1
Table error: Object ID 801646149 (object '801646149') does not match between 'SYSINDEXES' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 11, Line 1
Table error: Object ID 1258695782 (object '1258695782') does not match between 'SYSINDEXES' and 'SYSOBJECTS'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Thanks
Kiran

Thanks

Kiran Patil

ciran_patil@yahoo.com
Starting Member

17 Posts

Posted - 2008-04-28 : 09:21:32

I have deleted few entries from syscolumns and sysindexes (Mismatching) and ran DBC CHECKALLOC again, and this time no errors for DBCC CHEKALLOC.

I am taking fresh backup now and will try to restore on 2005. Will update the result soon.

Thanks

Kiran Patil

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2008-04-28 : 09:47:05

Yup - that's what I expected. Why do you keep running DBCC CHECKALLOC? Do you mean DBCC CHECKCATALOG?

Paul Randal
SQL Server MVP, Managing Director, SQLskills.com

ciran_patil@yahoo.com
Starting Member

17 Posts

Posted - 2008-04-28 : 10:00:17

Hi Paul,

Yes, sorry I mean DBCC CHECKCATALOG :-))
Backup is complete and restoration on 2005 is 50 % complete.
Lets see the result.

Thanks

Kiran Patil

ciran_patil@yahoo.com
Starting Member

17 Posts

Posted - 2008-04-28 : 10:18:23

Hey Paul,

That worked...Thanks a lot for your help.
I could restore databse on sql 2005 successfully.

Now tell me, how did you suspect that, it could be problem with system tables :-)

Thanks

Kiran Patil

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2008-04-28 : 11:14:07

Because I wrote DBCC CHECKDB (seriously) :-)

Paul Randal
SQL Server MVP, Managing Director, SQLskills.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources