Corrupt SQL 2k DB, need help! - 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

Corrupt SQL 2k DB, need help!

Author

Topic

ajbuster
Starting Member

17 Posts

Posted - 2006-07-06 : 14:57:29

Have a corrupt SQL database that I am unable to repair using any of the repair options with DBCC CHECKDB. Below is the output from the repair_allow_data_loss option.

I can't seem to figure out how to go about fixing this. Any takers?!

Server: Msg 8946, Level 16, State 12, Line 2
Table error: Allocation page (1:24264) has invalid PFS_PAGE page header values. Type is 0. Check type, object ID and page ID on the page.
Server: Msg 8921, Level 16, State 1, Line 1
CHECKTABLE terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page (1:24264). Test (m_headerVersion == HEADER_7_0) failed. Values are 0 and 1.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page (1:24264). Test ((m_type >=DATA_PAGE && m_type <= UNDOFILE_HEADER_PAGE) || (m_type == UNKNOWN_PAGE && level == BASIC_HEADER)) failed. Values are 0 and 101.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page (1:24264). Test (m_freeData >= PAGEHEADSIZE && m_freeData <= (UINT)PAGESIZE - m_slotCnt * sizeof (Slot)) failed. Values are 0 and 8192.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page (1:24264). Test (m_headerVersion == HEADER_7_0) failed. Values are 0 and 1.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page (1:24264). Test ((m_type >=DATA_PAGE && m_type <= UNDOFILE_HEADER_PAGE) || (m_type == UNKNOWN_PAGE && level == BASIC_HEADER)) failed. Values are 0 and 101.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page (1:24264). Test (m_freeData >= PAGEHEADSIZE && m_freeData <= (UINT)PAGESIZE - m_slotCnt * sizeof (Slot)) failed. Values are 0 and 8192.
Server: Msg 8998, Level 16, State 1, Line 1
Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to verify database ID 7 pages from (1:24264) to (1:32351). See other errors for cause.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page (1:32352). Test (m_headerVersion == HEADER_7_0) failed. Values are 0 and 1.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page (1:32352). Test ((m_type >=DATA_PAGE && m_type <= UNDOFILE_HEADER_PAGE) || (m_type == UNKNOWN_PAGE && level == BASIC_HEADER)) failed. Values are 0 and 101.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page (1:32352). Test (m_freeData >= PAGEHEADSIZE && m_freeData <= (UINT)PAGESIZE - m_slotCnt * sizeof (Slot)) failed. Values are 0 and 8192.
Server: Msg 8998, Level 16, State 1, Line 1
Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to verify database ID 7 pages from (1:32352) to (1:40439). See other errors for cause.
DBCC results for '8602955'.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The repair level on the DBCC statement caused this repair to be bypassed.
The system cannot self repair this error.
The system cannot self repair this error.
The repair level on the DBCC statement caused this repair to be bypassed.
The system cannot self repair this error.
The system cannot self repair this error.
The system cannot self repair this error.
The repair level on the DBCC statement caused this repair to be bypassed.
CHECKDB found 8 allocation errors and 3 consistency errors not associated with any single object.
DBCC results for 'sysobjects'.
There are 1395 rows in 27 pages for object 'sysobjects'.
CHECKDB found 8 allocation errors and 3 consistency errors in database '8602955'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

ajbuster
Starting Member

17 Posts

Posted - 2006-07-06 : 15:11:40

Based on some things I've just found in SQL 2005's BOL, looks like restore may be the only option. :( Can anyone confirm that, or is there still hope to possibly repair this? Unfortunately the company that has asked me to repair this only had a backup from 6 months ago!

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2006-07-06 : 15:59:15

There is no other option apart from restoring. As you've correctly gathered, these are PFS pages that are corrupt and there is no way to repair them (documented, undocumented or manually).

A lesson for the company in having a valid backup strategy...

What root-cause analysis did they/you do? I've never seen back-to-back PFS pages corrupt except when a whole swathe of a disk has been destroyed. Can you post DBCC PAGE outputs from them?

DBCC TRACEON (3604)
go
DBCC PAGE (7, 1, 24264, 1)
go
DBCC PAGE (7, 1, 32352, 1)
go

And do the same for some pages between these two (but don't post the results) to see if the pages look like they've been trashed with zeroes.

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.)

ajbuster
Starting Member

17 Posts

Posted - 2006-07-06 : 16:13:21

Thanks for confirming Paul!

As for the root/cause analysis, I'm actually not the one working directly with the company in question. A co-worker was attempting to fix it and asked for my assistance, and all I know is from an email stating "their server crashed."! Beyond that I have no further details I'm afraid.

I ran the statements for some pages in between, and indeed they all were hosed with 0's. Here are the results from the DBCC Page statements for the two pages in question:

Server: Msg 8939, Level 16, State 5, Line 1
Table error: Object ID 0, index ID 0, page (1:24264). Test (m_headerVersion == HEADER_7_0) failed. Values are 0 and 1.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page (1:24264). Test ((m_type >=DATA_PAGE && m_type <= UNDOFILE_HEADER_PAGE) || (m_type == UNKNOWN_PAGE && level == BASIC_HEADER)) failed. Values are 0 and 101.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page (1:24264). Test (m_freeData >= PAGEHEADSIZE && m_freeData <= (UINT)PAGESIZE - m_slotCnt * sizeof (Slot)) failed. Values are 0 and 8192.

PAGE: (1:24264)
---------------

BUFFER:
-------

BUF @0x00E9F7C0
---------------
bpage = 0x1FF3E000 bhash = 0x00000000 bpageno = (1:24264)
bdbid = 7 breferences = 1 bstat = 0x9
bspin = 0 bnext = 0x00000000

PAGE HEADER:
------------

Page @0x1FF3E000
----------------
m_pageId = (1:24264) m_headerVersion = 0 m_type = 0
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0
m_objId = 0 m_indexId = 0 m_prevPage = (0:0)
m_nextPage = (0:0) pminlen = 0 m_slotCnt = 0
m_freeCnt = 0 m_freeData = 0 m_reservedCnt = 0
m_lsn = (0:0:0) m_xactReserved = 0 m_xdesId = (0:0)
m_ghostRecCnt = 0 m_tornBits = 0

Allocation Status
-----------------
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED
PFS (1:24264) = 0x0 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

DATA:
-----

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Server: Msg 8939, Level 16, State 5, Line 1
Table error: Object ID 0, index ID 0, page (1:32352). Test (m_headerVersion == HEADER_7_0) failed. Values are 0 and 1.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page (1:32352). Test ((m_type >=DATA_PAGE && m_type <= UNDOFILE_HEADER_PAGE) || (m_type == UNKNOWN_PAGE && level == BASIC_HEADER)) failed. Values are 0 and 101.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page (1:32352). Test (m_freeData >= PAGEHEADSIZE && m_freeData <= (UINT)PAGESIZE - m_slotCnt * sizeof (Slot)) failed. Values are 0 and 8192.

PAGE: (1:32352)
---------------

BUFFER:
-------

BUF @0x00E9F780
---------------
bpage = 0x1FF3C000 bhash = 0x00000000 bpageno = (1:32352)
bdbid = 7 breferences = 1 bstat = 0x9
bspin = 0 bnext = 0x00000000

PAGE HEADER:
------------

Page @0x1FF3C000
----------------
m_pageId = (1:32352) m_headerVersion = 0 m_type = 0
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0
m_objId = 0 m_indexId = 0 m_prevPage = (0:0)
m_nextPage = (0:0) pminlen = 0 m_slotCnt = 0
m_freeCnt = 0 m_freeData = 0 m_reservedCnt = 0
m_lsn = (0:0:0) m_xactReserved = 0 m_xdesId = (0:0)
m_ghostRecCnt = 0 m_tornBits = 0

Allocation Status
-----------------
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED
PFS (1:32352) = 0x0 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

DATA:
-----

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2006-07-06 : 16:28:07

Cool - that confirms that it was a hardware issue. Are all their drivers and firmware up-to-date?

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.)

ajbuster
Starting Member

17 Posts

Posted - 2006-07-06 : 16:30:35

From my partner's account, sounds like they already have a new server they are going to be using moving forward, so we aren't going to worry about the original server in question!

Thanks so much for your help Paul.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2006-07-06 : 16:38:50

No problem. Be sure to impress on your partner the urgency for this company to get a viable backup strategy in place.

See http://blogs.msdn.com/sqlserverstorageengine/archive/category/13591.aspx for a bunch more details.

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.)

jocampo
Starting Member

48 Posts

Posted - 2006-07-12 : 16:06:08

Hi Paul

Stupid question: how did you know that from the given data? i mean...that it was a hardware issue?

Regards,

JC

quote:
Originally posted by paulrandal

Cool - that confirms that it was a hardware issue. Are all their drivers and firmware up-to-date?

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.)

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2006-07-12 : 16:18:49

Not a stupid question at all - there are only a few people round the world who can do this (and having written CHECKDB, I'm one of them). It's unfortunate that its so complicated because people often make the wrong choice about what to do - if only they'd know more about what CHECKDB is telling them.

That was an easy one. Both PFS pages are trashed - the odds of only the two PFS pages being trashed (either by h/w or by some hitherto unknown hideous SQL bug) are extremely small so I surmised h/w as the problem. By checking whether pages in between the two PFS pages have also been trashed, I can confirm that theory. Something caused a whole swathe of a disk (or set of disks if we're talking RAID) to be toasted - it's usually h/w.

Hope this explains.

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.)

jocampo
Starting Member

48 Posts

Posted - 2006-07-13 : 08:47:48

Can you "quote" those lines or use bold? ... please ...

quote:
Originally posted by paulrandal

Not a stupid question at all - there are only a few people round the world who can do this (and having written CHECKDB, I'm one of them). It's unfortunate that its so complicated because people often make the wrong choice about what to do - if only they'd know more about what CHECKDB is telling them.

That was an easy one. Both PFS pages are trashed - the odds of only the two PFS pages being trashed (either by h/w or by some hitherto unknown hideous SQL bug) are extremely small so I surmised h/w as the problem. By checking whether pages in between the two PFS pages have also been trashed, I can confirm that theory. Something caused a whole swathe of a disk (or set of disks if we're talking RAID) to be toasted - it's usually h/w.

Hope this explains.

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.)

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2006-07-13 : 13:16:55

I don't follow you...

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.)

jocampo
Starting Member

48 Posts

Posted - 2006-07-13 : 14:55:30

which part in the code you found that ... which line ...

quote:
Originally posted by paulrandal

I don't follow you...

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.)

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2006-07-13 : 16:49:19

I'm still not following you - what code?

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.)

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-07-13 : 21:40:38

i assume he's asking you by just looking at the code if there are some keywords that he can also look at that will help him identify it's hardware issue

just like with the sql error messages

--------------------
keeping it simple...

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-07-14 : 12:36:45

jocampo,

These lines, I'd suspect:

2nd line:
"Table error: Allocation page (1:24264) has invalid PFS_PAGE page header values. Type is 0. Check type, object ID and page ID on the page."

About line 12 or so:
"Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to verify database ID 7 pages from (1:24264) to (1:32351). See other errors for cause."

Ken

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2006-07-16 : 15:42:03

Ah - I get it - maybe I wasn't being smart that day. If you look at all the errors, you'll see that they all say 'values are 0 and xx'. The fact that they're all complaining the that the erroneous value is zero says to me that the page has been overwritten with zeroes (usually hardware, but not conclusive). The fact that two successive PFS pages 8088 pages apart have been overwritten leads me to conclude that somethings written zeroes over a wide area of the disk - that's why I asked for DBCC PAGEs of some pages in the interval between the two PFS pages. Given that they are also full of zeros, it's almost 100% that it was a hardware issue.

Hope that makes more sense.

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.)

Maertenz
Starting Member

3 Posts

Posted - 2007-03-05 : 09:31:30

Hello, we seem to have the same problem as described above ... but we have a current backup.
Yet, after restoring the backup, the problem persists!
What to do now ? Export and re-import with DTS?

Greetings
Maertenz

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-03-05 : 10:00:17

Then I'd say that the backup also has the corruption in (i.e. you backed up a corrupt database). How long before the backup was taken did you last run DBCC CHECKDB?

Can you post the complete output from DBCC CHECKCDB (yourdb) WITH ALL_ERRORMSGS, NO_INFOMSGS?

Paul Randal
Principal Lead Program Manager, Microsoft SQL Server Core Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
http://blogs.msdn.com/sqlserverstorageengine/default.aspx

Maertenz
Starting Member

3 Posts

Posted - 2007-03-05 : 11:35:29

Hi Paul,
complete output is:

"Server: Nachr.-Nr. 8909, Schweregrad 16, Status 1, Zeile 1
Table error: Object ID 0, index ID 0, page ID (1:137496). The PageId in the page header = (0:0).
Server: Nachr.-Nr. 8998, Schweregrad 16, Status 1, Zeile 1
Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to verify database ID 7 pages from (1:137496) to (1:145583). See other errors for cause.
CHECKDB found 2 allocation errors and 0 consistency errors not associated with any single object.
CHECKDB found 2 allocation errors and 0 consistency errors in database 'CB_MCC_PROD'.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-03-05 : 11:55:18

Yup - same problem. How about the answer to the other questions?

Have you checked for h/w problems? SQL error log, Windows event log, h/w diagnostics, firmware up-to-date?

Thanks

Paul Randal
Principal Lead Program Manager, Microsoft SQL Server Core Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
http://blogs.msdn.com/sqlserverstorageengine/default.aspx

Maertenz
Starting Member

3 Posts

Posted - 2007-03-05 : 12:07:51

Hi,

it is/was a hardware problem. the question is: what can i do now? it is nearly impossible to restore a 7(or more)-day-old backup.

(interestingly, it did not yet cause a problem to the end-user. it just appeared within the "db-optimizing" in the db-maintenance-plan...)

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources