DBCC CHECKDB Errors - 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

DBCC CHECKDB Errors

Author

Topic

gregf@designr1.com
Starting Member

6 Posts

Posted - 2008-06-03 : 15:21:09

I have a database that was handed to me which is getting the following errors:

DBCC CHECKDB ('DBName') returns:

Server: Msg 8939, Level 16, State 106, Line 1
Table error: Object ID 1, index ID 0, page (1:37). Test (m_freeCnt == freeCnt) failed. Values are 680 and 788.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:37) with latch type SH. sysobjects failed.

DBCC PAGE (DBName, 1, 37, 1) returns:

Server: Msg 8939, Level 16, State 20, Line 1
Table error: Object ID 99, index ID 0, page (1:2). Test (m_slots[0].GetOffset () == PAGEHEADSIZE) failed. Values are 662 and 96.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 99, index ID 0, page (1:2). Test (Align(m_slots[-1].GetOffset ()) == Align(m_slots[0].GetOffset () + sizeof(GAMHEADER) + sizeof(DataRecHdr))) failed. Values are 190 and 756.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC PAGE (DBName, 1, 2, 1) returns:

Server: Msg 8939, Level 16, State 20, Line 1
Table error: Object ID 99, index ID 0, page (1:2). Test (m_slots[0].GetOffset () == PAGEHEADSIZE) failed. Values are 662 and 96.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 99, index ID 0, page (1:2). Test (Align(m_slots[-1].GetOffset ()) == Align(m_slots[0].GetOffset () + sizeof(GAMHEADER) + sizeof(DataRecHdr))) failed. Values are 190 and 756.
Server: Msg 3624, Level 20, State 1, Line 1
 
Location:	 recbase.cpp:1374
Expression:	 m_nVars > 0
SPID:		 51
Process ID:	 2096

Connection Broken

And finally, DBCC CHECKTABLE (sysobjects) WITH NO_INFOMSGS, ALL_ERRORMSGS gives me:

Server: Msg 8939, Level 16, State 106, Line 1
Table error: Object ID 1, index ID 0, page (1:37). Test (m_freeCnt == freeCnt) failed. Values are 680 and 788.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:37) with latch type SH. sysobjects failed.

I've looked at the MDF with a hex editor and there's definitely data in there. I have the DB attached and in single user/emergency mode, but DBCC isn't having much luck. Help?

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2008-06-03 : 15:32:38

The error cannot be repaired - as its at the leaf level of the clustered index for sysobjects. Which version of SQL Server 2000 are you using (I can tell from the errors that you're not on 2005/2008)

If that's all that's wrong with the database, the odds are you can extract out all the data into a new database with zero data loss. Although you can't run CHECKDB on it, can you run CHECKTABLE on some of the large tables in the database?

Do you have a backup that you can use to restore with no data loss? Any idea why the corruption happened - hardware problems? Power failure?

Thanks

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

gregf@designr1.com
Starting Member

6 Posts

Posted - 2008-06-03 : 15:41:29

I tried

DBCC CHECKTABLE (TBL_CONTACT) WITH NO_INFOMSGS, ALL_ERRORMSGS

(TBL_CONTACT being the table I most want to get to) and got:

Server: Msg 601, Level 12, State 3, Line 1
Could not continue scan with NOLOCK due to data movement.

This is SQL 2000 SP3 Developer Edition.

gregf@designr1.com
Starting Member

6 Posts

Posted - 2008-06-03 : 15:47:20

Honestly, I have no clue what happened to it. I was given an MDF and an LDF and told "do what you can." It appeared that the LDF didn't really belong to the DB, since it was only 1MB. I couldn't attach the DB normally since it wasn't shut down cleanly so I had to do the whole create a new DB with the same name, stop SQL server, copy in the MDF, start SQL server, set the DB in Emergency mode thing.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2008-06-03 : 15:53:02

Hmm - that's not good - it says you've got more metadata corruption.

If you don't have a backup, your only option is to extract as much schema/data as you can into a new database.

Thanks

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

gregf@designr1.com
Starting Member

6 Posts

Posted - 2008-06-03 : 17:11:55

I can recreate the exact database structure, but how do I get the data out? Right now in Object Explorer, there are no tables either User or System. Is there any way for me to create a new healthy DB of the exact same structure and somehow use that framework to access the data in this DB?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-06-03 : 18:34:44

Can try dump data with bcp.

gregf@designr1.com
Starting Member

6 Posts

Posted - 2008-06-03 : 20:35:38

I just tried that and got:

SQLState = 37000, NativeError = 601
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Could not continue scan with NOLOCK due to data movement.

gregf@designr1.com
Starting Member

6 Posts

Posted - 2008-06-03 : 20:39:27

Is there any documentation somewhere on the structure of an MDF file? If I had some basic info, I could probably figure out either a) how to rip the most important data out myself, or b) what's wrong with the header info and maybe fix it.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2008-06-03 : 20:51:48

There are a variety of documents and blog posts that detail the on-disk structures (e.g. Kalen's books, my blog series on Inside The Storage Engine, etc) but you will not be able to fix the system tables.

The error you're getting suggests that there is more corruption in the system tables - CHECKDB/CHECKTABLE will stop after the first gross corruption is found in a system table.

Your options at this point are to manually use DBCC IND and DBCC PAGE to figure out the list of pages comprising your important tables in logical order and get as much info out of the pages one-at-a-time as you can. Unless this is critical data and the database is small, this will take a *long* time to do. Unfortunately this isn't the kind of situation that can be worked over a forum such as this, and Microsoft Product Support won't help with data recovery (I know this as I co-wrote the policy document that prevents them doing it, while I was at MS).

Hope this helps.

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