April 1, 2009 at 2:19 am
Hello everybody
This is my first post on this forum so please forbear with me.
We are running SQL Server2005 SE SP3 on Windows Server 2005 R2 SP2
in an virtual environment (ESX3)
Last week one of our databases stops working (marked as suspect).
We recover the database from the backup and it continues to work.
So we created a testdatabase from the same backup and started searching
for a reason. We run a DBCC CHECKDB ('[DB-Name]')WITH ALL_ERRORMSGS, NO_INFOMSGS
and got 29 consistency errors in a table. The first error was:
Msg 8978, Level 16, State 1, Line 2
Table error: Object ID 1378103950, index ID 1, partition ID 72057594053984256,
alloc unit ID 72057594061717504 (type In-row data). Page (1:110797) is missing
a reference from previous page (1:415285). Possible chain linkage problem.
After identifying the table we run DBCC CHECKTABLE (tablename) WITH ALL_ERRORMSGS, NO_INFOMSGS
and got the same 29 errors. The first six errors were:
Msg 8978, Level 16, State 1, Line 2
Table error: Object ID 1378103950, index ID 1, partition ID 72057594053984256, alloc unit ID
72057594061717504 (type In-row data). Page (1:110797) is missing a reference from previous page
(1:415285). Possible chain linkage problem.
Msg 8928, Level 16, State 1, Line 2
Object ID 1378103950, index ID 1, partition ID 72057594053984256, alloc unit ID 72057594061717504
(type In-row data): Page (1:415285) could not be processed. See other errors for details.
Msg 8944, Level 16, State 17, Line 2
Table error: Object ID 1378103950, index ID 1, partition ID 72057594053984256, alloc unit
ID 72057594061717504 (type In-row data), page (1:415285), row 0. Test (columnOffsets->offTbl
[varColumnNumber] <= (nextRec - pRec)) failed. Values are 2299 and 518.
Msg 8944, Level 16, State 17, Line 2
Table error: Object ID 1378103950, index ID 1, partition ID 72057594053984256, alloc unit
ID 72057594061717504 (type In-row data), page (1:415285), row 0. Test (columnOffsets->offTbl
[varColumnNumber] <= (nextRec - pRec)) failed. Values are 2299 and 518.
Msg 8944, Level 16, State 17, Line 2
Table error: Object ID 1378103950, index ID 1, partition ID 72057594053984256, alloc unit
ID 72057594061717504 (type In-row data), page (1:415285), row 0. Test (columnOffsets->offTbl
[varColumnNumber] <= (nextRec - pRec)) failed. Values are 2299 and 518.
Msg 8976, Level 16, State 1, Line 2
Table error: Object ID 1378103950, index ID 1, partition ID 72057594053984256, alloc unit
ID 72057594061717504 (type In-row data). Page (1:415285) was not seen in the scan although
its parent (1:73433) and previous (1:454393) refer to it. Check any previous errors.
We tested all our backups but in all of our backups the table is inconsistent.
In our testdatabase we run DBCC CHECKTABLE REPAIR_ALLOW_DATA_LOSS and the
table becomes clean. Unfortunately we lost 12 records. Is there a way to get this table
clean without the loss of data?
Many Thx for your feedbacks
April 1, 2009 at 3:21 am
Its a corruption on the leaf level pages of the index, if you run REPAIR_ALLOW_DATA_LOSS obviously it would delete the data that it could to repair the database. Basically, REPAIR_ALLOW_DATA_LOSS is for non-clustered indexes.
Have you got a clean backup? That is the only way you can get back the data you want with a minimal loss.
April 1, 2009 at 3:25 am
Am sure Paul will be around in sometime until then
Check out Paul's blog:
April 1, 2009 at 6:27 am
Hello again
First thank you for your reply.
We have a clean backup, but it is very old because nobody noticed
that there are consistency problems although there is a consistency check
all night. 🙁
I've read the blog you told me and i did the following:
I took my error message...
Msg 8928, Level 16, State 1, Line 2
Object ID 1378103950, index ID 1, partition ID 72057594053984256, alloc unit ID 72057594061717504 (type In-row data): Page (1:415285) could not be processed. See other errors for details.
And run
DBCC TRACEON (3604);
DBCC PAGE ('[Databasename]', 1, 415285, 3);
GO
The result was:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
PAGE: (1:415285)
BUFFER:BUF @0x039BC83C
bpage = 0x4E2BA000 bhash = 0x00000000 bpageno = (1:415285)
bdbid = 7 breferences = 0 bUse1 = 11100
bstat = 0xc00009 blog = 0x52152159 bnext = 0x00000000
PAGE HEADER:
Page @0x4E2BA000
m_pageId = (1:415285) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x200
m_objId (AllocUnitId.idObj) = 363 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594061717504
Metadata: PartitionId = 72057594053984256 Metadata: IndexId = 1
Metadata: ObjectId = 1378103950 m_prevPage = (1:454393) m_nextPage = (1:110797)
pminlen = 220 m_slotCnt = 12 m_freeCnt = 4884
m_freeData = 8165 m_reservedCnt = 0 m_lsn = (215262:10404:579)
m_xactReserved = 0 m_xdesId = (0:89727496) m_ghostRecCnt = 0
m_tornBits = 588952100
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED
PFS (1:412488) = 0x40 ALLOCATED 0_PCT_FULL DIFF (1:6) = NOT CHANGED
ML (1:7) = NOT MIN_LOGGED
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Did i understand it right, that all these record in this and in referenced pages to this page 415285 disappear
while Repair_Allow_Data_Loss?
Thanks for your anew feedback...
Regards Greg
April 1, 2009 at 7:53 am
Hi,
Yes, that's right it's corruption in the clustered index which is the data itself, you would be able to repair it but with some amount of data loss.
Why is your backup old?What is your backup strategy?
April 1, 2009 at 8:44 am
gregorybrunner (4/1/2009)
In our testdatabase we run DBCC CHECKTABLE REPAIR_ALLOW_DATA_LOSS and thetable becomes clean. Unfortunately we lost 12 records. Is there a way to get this table
clean without the loss of data?
Since you have no clean backup, no there is not.
I would suggest, going forward, you schedule regular integrity checks and check their results so that these kind of problems can be detected early while the 'restore from backup' option is still an option.
Additionally, you may wish to examine your IO subsystem for errors as the vast majority of corruption problems are related to the IO subsystem
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 1, 2009 at 11:44 pm
Hello everybody
Thank you for your replies.
@krishna-2 Potlakayala
On these case there is a daily full backup. The backups stay 90 days in our
backend backup system. The problem is that the last backup in which the
database is clean is older then one month. Also there are daily consistency
checks but the alarmed mailbox was orphaned. So if I restore the table from
the last good backup I will lose more data.
However, thank you all for your effort and answers.
Regards Greg
April 2, 2009 at 3:02 am
No problem 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply