December 10, 2007 at 9:45 am
Symtoms
can't shrink db, have to restart server after a couple of hours of use due to unable to enter/retrieve data. Part of this may be caused by large tlog file.
ex. db file is 291meg
tlog file is 23 gig
I believe backups probably have same corruption.
question: can I restore just pages with issues, and if I go back far enough in backups will it be smart enough to tell me if data is out of date.
question: Is there a way see what data is messed up, so I can determine if I just want to allow it to be deleted.
This is a small relatively simple database, a couple of main table related to each other and other tables for misc. stuff.
~ 100,000 records
I tried doing a export of tables to flat file, the main one gave me errors and didn't do it also.
This shows up in Errorlog
2007-12-08 16:00:52.23 spid16s Table error: IAM page (1:218) (object ID 2021582240, index ID 1, partition ID 413961390391296, alloc unit ID 413961390391296 (type In-row data)) is out of the range of this database.
These show up when i run a command in the query windows to display errors.
Msg 8928, Level 16, State 6, Line 1
Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown): Page (1:218) could not be processed. See other errors for details.
CHECKDB found 1 allocation errors and 0 consistency errors not associated with any single object.
Msg 8906, Level 16, State 1, Line 1
Page (1:217) in database ID 5 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.
Msg 2575, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (1:218) is pointed to by the next pointer of IAM page (0:0) in object ID 2021582240, index ID 1, partition ID 413961390391296, alloc unit ID 413961390391296 (type In-row data), but it was not detected in the scan.
Msg 7965, Level 16, State 2, Line 1
Table error: Could not check object ID 2021582240, index ID 1, partition ID 413961390391296, alloc unit ID 413961390391296 (type In-row data) due to invalid allocation (IAM) page(s).
Msg 8906, Level 16, State 1, Line 1
Page (1:218) in database ID 5 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL'.
Msg 8939, Level 16, State 28, Line 1
Table error: Object ID 2021582240, index ID 1, partition ID 413961390391296, alloc unit ID 413961390391296 (type In-row data), page (1:218). Test (m_slots[0].GetOffset () == PAGEHEADSIZE && m_type == IAM_PAGE) failed. Values are 0 and 96.
Msg 8939, Level 16, State 29, Line 1
Table error: Object ID 2021582240, index ID 1, partition ID 413961390391296, alloc unit ID 413961390391296 (type In-row data), page (1:218). Test (Align(m_slots[-1].GetOffset ()) == Align(m_slots[0].GetOffset () + sizeof(IAMHEADER) + sizeof(DataRecHdr)) && m_type == IAM_PAGE) failed. Values are 0 and 94.
Msg 8939, Level 16, State 28, Line 1
Table error: Object ID 2021582240, index ID 1, partition ID 413961390391296, alloc unit ID 413961390391296 (type In-row data), page (1:218). Test (m_slots[0].GetOffset () == PAGEHEADSIZE && m_type == IAM_PAGE) failed. Values are 0 and 96.
Msg 8939, Level 16, State 29, Line 1
Table error: Object ID 2021582240, index ID 1, partition ID 413961390391296, alloc unit ID 413961390391296 (type In-row data), page (1:218). Test (Align(m_slots[-1].GetOffset ()) == Align(m_slots[0].GetOffset () + sizeof(IAMHEADER) + sizeof(DataRecHdr)) && m_type == IAM_PAGE) failed. Values are 0 and 94.
Msg 8906, Level 16, State 1, Line 1
Page (1:222) in database ID 5 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.
CHECKDB found 6 allocation errors and 3 consistency errors in table 'tblPrices' (object ID 2021582240).
CHECKDB found 7 allocation errors and 3 consistency errors in database 'pcs'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (pcs).
December 10, 2007 at 10:41 am
If this is important, production data, call PSS at Microsoft.
DO NOT RUN ALLOW_DATA_LOSS as it's behavior is unpredictable and does not necessarily help.
This is a complex issue, and we can't really give you good advice. You need someone that can work with you in real-time to determine what can be saved and what might not be. I'd be sure you have enough disk space on this or a spare server to restore some backups and run tests.
December 10, 2007 at 12:45 pm
Actually its not that complex. There's a single page that's corrupt - page (1:218) - and it happens to be the head of an IAM chain, which is leading to all the other errors.
What kind of backups do you have available? From your log size, I'm guessing you're in FULL recovery mode and you're only taking full database backups (which is why your log is growing continually). If you start taking log backups, your log will be able to be truncated (and then you'll need to manually shrink it - you've probably got horrible VLF fragmentation in there too if its been auto-growing for a while). Anyway, you should be able to take a (large) log backup and then do a single page restore of the IAM page using the last full backup plus the log backup. I did an old blog post while at MS on doing this - see here - and I need to post an updated version this week on my new blog.
This will be what PSS tells you too. And, once everything's done, do some root-cause analysis to figure out why the corruption occured in the first place - see here for some directions.
Hope this helps.
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply