DB Corruption recovery

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Data Corruption » DB Corruption recovery

DB Corruption recovery

Rate Topic

Display Mode

Topic Options

Author

Message

ThomG

Posted Wednesday, January 11, 2012 11:52 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 08, 2012 4:09 PM
Points: 3, Visits: 7

I was at least glad to find out what I am seeing in the error messages from a great article on this site:

"Damaged allocation pages
Msg 8946, Level 16, State 12, Line 1
Table error: Allocation page (1:2264640) has invalid PFS_PAGE page header values. Type is 0. Check type, alloc unit ID and page ID on the page.
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 13 pages from (1:2264640) to (1:2272727)

In this case, one or more of the database allocation pages are damaged. The allocation pages are used to mark which pages and extents in the database are allocated and which are free. CheckDB will not repair damage to the allocation pages, as it is extremely difficult to work out, without those pages, what extents are allocated and which are not. Dropping the allocation page is not an option as that would discard up to 4GB of data."

I guess the question I have is - where do I go from here? If I export the data and create a new database, can I import the data without importing the corruption? I have been working on this for 6 days now just to get the point I have the server back to between restoring the OS and backups and then working on trying to get the data to a recoverable state. I feel certain that I can get data via export/import but I don't want to do that until I know that corruption will not be included in this transaction.

Here are the error messages I get on this database:
Msg 8946, Level 16, State 12, Line 2
Table error: Allocation page (1:40440) has invalid PFS_PAGE page header values. Type is 0. Check type, object ID and page ID on the page.
DBCC results for 'accesscontrol'.
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.
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 5 pages from (1:40440) to (1:48527). See other errors for cause.
CHECKDB found 1 allocation errors and 0 consistency errors not associated with any single object.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 2133655552, index ID 32557, page ID (1:40440). The PageId in the page header = (0:13246).
CHECKDB found 0 allocation errors and 1 consistency errors in table '(Object ID 2133655552)' (object ID 2133655552).
CHECKDB found 1 allocation errors and 1 consistency errors in database 'AccessControl'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Any help is appreciated.

Post #1234254

GilaMonster

Posted Wednesday, January 11, 2012 12:00 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:41 AM
Points: 38,089, Visits: 30,383

This is not repairable.

Since you have no backups the best you'll be able to do is script out all the objects (some may fail to script due to the corruption), export what data will export (anything that's damaged will fail to export, so you'll probably have to do this in pieces) and recreate the database the best you can.

If it is just that one page that's damaged (its an allocation page) then maybe all your data will export correctly, but there's an 8000 page chunk of your database that can't be checked (because of the damaged page) and there may be more corruption within that section that will cause further errors.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1234265

ThomG

Posted Wednesday, January 11, 2012 1:35 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 08, 2012 4:09 PM
Points: 3, Visits: 7

Thank you - I was afraid that was the answer.

Post #1234340

« Prev Topic | Next Topic »

Permissions