February 18, 2014 at 12:36 pm
I need help with fixing corrupted page.
I've got into this trouble yesterday , when it was required to enable TDE on one of the DB copies. The TDE encryption hanged with encryption_state=2 and percent_complete=0. Records were added into suspect_pages and backup with CHECKSUM failed with error.
That's where I get so far and where I ran out of ideas how to recover the corrupted page:
1.
Query "select * from msdb.dbo.suspect_pages" returns:
database_idfile_idpage_idevent_typeerror_countlast_update_date
5119193112014-02-17 23:19:47.997
5119193312014-02-17 23:19:48.010
2.
dbcc traceon(3604)
go
dbcc page([MyDB],1,19193,3)
go
returns:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
PAGE: (121:7471201)
BUFFER:
BUF @0x00000000A1FEB300
bpage = 0x00000000A1C88000 bhash = 0x0000000000000000 bpageno = (1:19193)
bdbid = 11 breferences = 3 bcputicks = 0
bsampleCount = 0 bUse1 = 3840 bstat = 0xc00809
blog = 0x21432159 bnext = 0x0000000000000000
PAGE HEADER:
Page @0x00000000A1C88000
m_pageId = (121:7471201) m_headerVersion = 101 m_type = 0
m_typeFlagBits = 0x2e m_level = 0 m_flagBits = 0x53
m_objId (AllocUnitId.idObj) = 6881396 m_indexId (AllocUnitId.idInd) = 101
Metadata: AllocUnitId = 28429423626944512 Metadata: PartitionId = 0
Metadata: IndexId = -1 Metadata: ObjectId = 0 m_prevPage = (105:7536755)
m_nextPage = (105:4456558) pminlen = 111 m_slotCnt = 99
m_freeCnt = 111 m_freeData = 110 m_reservedCnt = 46
m_lsn = (7536713:7143497:109) m_xactReserved = 117 m_xdesId = (98:6357108)
m_ghostRecCnt = 108 m_tornBits = 3932261
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED
PFS (1:16176) = 0x40 ALLOCATED 0_PCT_FULL DIFF (1:6) = NOT CHANGED
ML (1:7) = NOT MIN_LOGGED
Msg 2514, Level 16, State 5, Line 3
A DBCC PAGE error has occurred: Invalid page type - dump style 3 not possible.
3. DBCC CHECKDB returns no errors.
4. DB is verion is SQL2008R2.
5. Db option "Page Verify" set to "TORN_PAGE_DETECTION".
6. Database recovery is Simple. And I found that this page was corrupted since the beginning of the time. So I don't have a healthy copy or good backup for this DB.
The database size is just about 8GB. Most likely it will not be a problem to dump database , recreate it and reload the whole db.
Unfortunately I have about 15 instances of this database with all sorts of derived processes like replication,.. configured.
It will take me forever to reload and setup all these instances. So best solution for me is to find out how to recover this page.
My question is: Do I have a single chance to fix this page? Or maybe it is relatively safe to drop or even manually overwrite the page.
Thanks,
November 15, 2016 at 1:06 pm
Hi Toothpicker!
Did you ever find a solution for this particular issue? I am in the exact same boat at a new company. There is a corrupt page in a seed db that DBCC CHECKDB didn't recognize and this has been used to create a gazillion other databases which are all unable to be encrypted.
Thanks,
JB
November 23, 2016 at 10:35 pm
There is a corrupt page in a seed db that DBCC CHECKDB didn't recognize
Consistency checks only run on the pages in the database that are allocated. If a page isn't allocated to anything, then the 8192 bytes of it are meaningless and can't be interpreted. As long as a page is allocated, it will be consistency checked by DBCC CHECKDB, including testing the page checksum, if it exists. A corruption can seem to 'disappear' if a corrupt page is deallocated at the time a DBCC CHECKDB runs.
Did you ever find a solution for this particular issue?
To fix the corruption issue of your database page you can take the help of third party SQL repair tool[/url]. Download the demo version first to see if the tool fits your need.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply