SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DB Corruption recovery


DB Corruption recovery

Author
Message
ThomG
ThomG
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)SSC Guru (227K reputation)

Group: General Forum Members
Points: 227669 Visits: 46339
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, MVP, M.Sc (Comp Sci)
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


ThomG
ThomG
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 7
Thank you - I was afraid that was the answer.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search