November 9, 2009 at 2:50 pm
DBCC CheckDB Results:
Msg 2511, Level 16, State 2, Line 2
Table error: Object ID 4, index ID 1, partition ID 262144, alloc unit ID 262144 (type In-row data). Keys out of order on page (1:36), slots 30 and 31.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'sys.sysrowsetcolumns' (object ID 4).
Msg 7995, Level 16, State 1, Line 2
Database 'ServiceCenter': consistency errors in system catalogs prevent further DBCC checkdb processing.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'ALLOCATION' (object ID 99).
CHECKDB found 0 allocation errors and 2 consistency errors in database 'ServiceCenter'.
When I run
DBCC TRACEON (3604)
GO
DBCC PAGE (ServiceCenter, 1, 36, 3); -- slot 30
go
I get:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
PAGE: (1:36)
BUFFER:
BUF @0x02C7901C
bpage = 0x05B1A000 bhash = 0x00000000 bpageno = (1:36)
bdbid = 6 breferences = 0 bUse1 = 38277
bstat = 0x1c00009 blog = 0x21212159 bnext = 0x00000000
PAGE HEADER:
Page @0x05B1A000
m_pageId = (1:36) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 4 m_indexId (AllocUnitId.idInd) = 0 Metadata: AllocUnitId = 262144
Metadata: PartitionId = 262144 Metadata: IndexId = 1 Metadata: ObjectId = 4
m_prevPage = (1:3417) m_nextPage = (1:3450) pminlen = 34
m_slotCnt = 125 m_freeCnt = 3221 m_freeData = 5017
m_reservedCnt = 0 m_lsn = (6097:49:9) m_xactReserved = 0
m_xdesId = (0:1368212) m_ghostRecCnt = 0 m_tornBits = 143369
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED
PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
Slot 30 Offset 0x4b6 Length 37
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x33ECC4B6
00000000: 10002200 00005b79 8b100101 3f000000 †.."...[y....?...
00000010: 3f000000 00000000 ee4a0000 00000000 †?........J......
00000020: 05000600 c0††††††††††††††††††††††††††.....
Slot 30 Column 0 Offset 0x4 Length 8
rowsetid = 72357260237144064
Slot 30 Column 1 Offset 0xc Length 4
rowsetcolid = 63
Slot 30 Column 2 Offset 0x10 Length 4
hobtcolid = 63
Slot 30 Column 3 Offset 0x14 Length 4
status = 0
Slot 30 Column 4 Offset 0x18 Length 8
rcmodified = 19182
Slot 30 Column 5 Offset 0x20 Length 2
maxinrowlen = 5
Slot 31 Offset 0x4db Length 37
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x33ECC4DB
00000000: 10002200 00005b79 8b100100 40000000 †.."...[y....@...
00000010: 40000000 00000000 f14a0000 00000000 †@........J......
00000020: 05000600 c0††††††††††††††††††††††††††.....
Slot 31 Column 0 Offset 0x4 Length 8
rowsetid = 299666199216128
Slot 31 Column 1 Offset 0xc Length 4
rowsetcolid = 64
Slot 31 Column 2 Offset 0x10 Length 4
hobtcolid = 64
Slot 31 Column 3 Offset 0x14 Length 4
status = 0
Slot 31 Column 4 Offset 0x18 Length 8
rcmodified = 19185
Slot 31 Column 5 Offset 0x20 Length 2
maxinrowlen = 5
Not sure what to do. Help!
November 9, 2009 at 3:17 pm
That's a critical system table that you can't repair and you can't rebuild the index to fix this. Something corrupted the cluster key in the table - from the pattern of the corruption, it's either bad memory, a bug, or I/O subsystem.
The two keys in records 30 and 31 have values 72357260237144064 and 299666199216128. In hex, these are:
101108B795B0000 and
__1108B795B0000 respectively.
The different is in the first byte of the hex value. Now, depending on the key value of rows 29 and 32, one of these is correct, and one is wrong - by only the leading byte. If you look in the hex dump of the records, you can see that at byte 11 in the record, they differ by one byte.. 01 vs. 00. My guess is the second value is wrong.
I don't know of any bugs that would cause this, so that leaves bad memory or something wrong with the I/O subsystem. Have you seen any other problems?
Thanks
PS The way to recover from this is to restore from you backups OR drop the table that this allocation unit is part of and recreate it again.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
November 10, 2009 at 6:20 am
I have not seen any other problems but I have not really gone into the event viewer to see if there is any other system related issues so that is a possiblity. This came from his newest backup that I restored. He does have older backups. When you say "restore", are you talking about restoring that page from a previous backup? Here are slots 29 and 32:
Slot 29 Offset 0x491 Length 37
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x33ECC491
00000000: 10002200 00005b79 8b100100 3e000000 †.."...[y....>...
00000010: 3e000000 00000000 f14a0000 00000000 †>........J......
00000020: 05000600 c0††††††††††††††††††††††††††.....
Slot 29 Column 0 Offset 0x4 Length 8
rowsetid = 299666199216128
Slot 29 Column 1 Offset 0xc Length 4
rowsetcolid = 62
Slot 29 Column 2 Offset 0x10 Length 4
hobtcolid = 62
Slot 29 Column 3 Offset 0x14 Length 4
status = 0
Slot 29 Column 4 Offset 0x18 Length 8
rcmodified = 19185
Slot 29 Column 5 Offset 0x20 Length 2
maxinrowlen = 5
Slot 32 Offset 0x500 Length 37
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x33ECC500
00000000: 10002200 00005b79 8b100100 41000000 †.."...[y....A...
00000010: 41000000 00000000 f14a0000 00000000 †A........J......
00000020: 05000600 c0††††††††††††††††††††††††††.....
Slot 32 Column 0 Offset 0x4 Length 8
rowsetid = 299666199216128
Slot 32 Column 1 Offset 0xc Length 4
rowsetcolid = 65
Slot 32 Column 2 Offset 0x10 Length 4
hobtcolid = 65
Slot 32 Column 3 Offset 0x14 Length 4
status = 0
Slot 32 Column 4 Offset 0x18 Length 8
rcmodified = 19185
Slot 32 Column 5 Offset 0x20 Length 2
maxinrowlen = 5
November 10, 2009 at 6:28 am
So slots 29 and 32 match 31. Would this cause the metadata error I receive below?
Ran:
Use ServiceCenter
Go
dbcc checktable ("dbo.erchistory")
Go
And got:
Msg 8901, Level 16, State 17, Line 2
Table error: Object ID 277576027 has inconsistent metadata. This error cannot be repaired and prevents further processing of this object.
CHECKTABLE found 0 allocation errors and 1 consistency errors in table 'ERCHistory' (object ID 277576027).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
November 10, 2009 at 1:28 pm
ok, so it was the first value that is wrong - 50/50 chance until I saw the surrounding slots.
Yes - this is the direct cause of the error you posted. The sysrowsetcolumns table lists all the columns for a table, and one of the columns has the wrong cluster key in the table. You could try doing a single-page restore of that page from a full backup without the corruption in, and then restoring all log backups to bring it up to the same point in time as the rest of the database.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
November 10, 2009 at 2:00 pm
Ok I checked the older database and that page is ok. I have a clean DBCC CheckDB on the one from 07/06/2009. I do have one problem though and that is both databases are set for simple recovery mode so I don't have access to restore page correct?
Thanks you very much. I really appreciate it. I would only be this far with your help. Watched your tech ed video on data corruption and it has helped me tremendously.
Thanks again.
November 10, 2009 at 2:12 pm
Glad you enjoyed the video - that's a fun session!
Hmm - yup, you can't do single-page restore on a SIMPLE mode database. Best bet may be to select all the data out into a new table, if you can, and then drop the old table. Not sure how the metadata layer will behave with that corruption in there though - not something I've tried. I suspect it will barf.
If you're feeling very brave, you could manually fix the corrupt byte using a hex-editor on that page - that's the quickest way to solve the problem.
Let me know how it goes.
Cheers
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
November 10, 2009 at 7:45 pm
Well you were right. It definately did barf. It does not like that at all. So now I will try your other suggestion in regards to the hex editor. I will let you know.
Thanks for your help.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply