0

I'm in a situation where a few Tables of a Database (MSSQL2016 - 13.0.5081.1) are corrupted (selecting data failes at some point) There is no valid backup available (the error seems to be present in the DB for a long time) However, i finally end up by executing the DBCC CHECKDB command with the REPAIR_ALLOW_DATA_LOSS option. But this fails too. The output returns messages like the following a couple of times:

Meldung 8909, Ebene 16, Status 1, Zeile 2 Tabellenfehler: Objekt-ID 0, Index-ID -1, Partitions-ID 0, Zuordnungseinheits-ID -7451885722783318016 (Unknown-Typ), Seiten-ID (1:290902) enthält eine falsche Seiten-ID im Seitenheader. PageId-Wert im Seitenheader = (9978:208048830). Vor dem Beheben dieses Fehlers müssen zunächst andere Fehler behoben werden.

In english the message should be something like this...

Table error: Object ID 0, index id -1, partition ID 0, allocation unit ID -7451885722783318016 (unknown type), page id (1: 290902) contains an incorrect page ID in the page header. PageId value in page header = (9978: 208048830).          Before resolving this error, other errors must be resolved first.

But what does this mean:

 Before resolving this error, other errors must be resolved first.

Which oher errors? - This messages returns a few times for differnt pages but no hint to other errors. I am aware that this whole situation will end up in dataloss.. i have to live with that.

Does anybody know how to step further?

UPDATE:

As in the comments mentioned i tried to evaluate the pageinfo with the traceflag 3604 enabled:

PAGE: (9978:208048830)

BUFFER:

BUF @0x000001D69F08F000

bpage = 0x000001D440C64000 bhash = 0x0000000000000000
bpageno = (1:290902) bdbid = 12 breferences = 0 bcputicks = 0 bsampleCount = 0
bUse1 = 16138 bstat = 0x809 blog = 0x1215a
bnext = 0x0000000000000000 bDirtyContext = 0x0000000000000000 bstat2 = 0x0

PAGE HEADER:

Page @0x000001D440C64000

m_pageId = (9978:208048830) m_headerVersion = 202
m_type = 214 m_typeFlagBits = 0x86 m_level = 148
m_flagBits = 0xe010 m_objId (AllocUnitId.idObj) = -1788167502
m_indexId (AllocUnitId.idInd) = 39061 Metadata: AllocUnitId = -7451885722783318016 Metadata: PartitionId = 0 Metadata: IndexId = -1 Metadata: ObjectId = 0 m_prevPage = (54490:-85112207) m_nextPage = (63487:2133407350)
pminlen = 44485 m_slotCnt = 15506 m_freeCnt = 40634 m_freeData = 52980
m_reservedCnt = 43709 m_lsn = (-1571835192:-1449823006:20555)
m_xactReserved = 12100 m_xdesId = (60082:-2118893827)
m_ghostRecCnt = 18723 m_tornBits = -1316355089 DB Frag ID = 1

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED
PFS (1:283080) = 0x40 ALLOCATED 0_PCT_FULL
DIFF (1:6) = NOT CHANGED ML (1:7) = NOT MIN_LOGGED

The m_type returns a suspect value of 214 - which i'm unable to match to one the follow valid page types:

1 – data page
2 – index page
3 and 4 – text pages
8 – GAM page
9 – SGAM page
10 – IAM page
11 – PFS page
13 - Boot page
15 - File header page
16 - diff map apge
17 - ML map page
18 - deallocated page
19 - Temp page
20 - Pre-Allocated page für bulk operations
Cadburry
  • 111
  • 4
  • Out of curiosity, did you try page restore rather than `REPAIR_ALLOW_DATA_LOSS`? https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-pages-sql-server?view=sql-server-2017 Also, what type of page is it? Do `dbcc traceon(3604); dbcc page('yourDBName', 1, 290902, 0);` and look for `m_type` in the output. You can find the decoder ring here: https://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-page/. – Ben Thul Sep 02 '19 at 15:38
  • @BenThul Thanks! Unfortunately, a page restore is not possible, because there is no backup available where the suspect pages are valid.. For the 'm_type' i have updated my question – Cadburry Sep 03 '19 at 06:46
  • Hmm... based on the information in https://www.sqlskills.com/blogs/paul/finding-table-name-page-id/, that object id of 0 is troubling. – Ben Thul Sep 04 '19 at 04:52
  • @BenThul yea i see - but for my understandings... "object 0" is always invalid... normally ids for objects start with "1" in mssql... – Cadburry Sep 04 '19 at 12:04
  • You're right. But you've also got corruption, so there's something weird about that page. From the article, ObjectID = 0 could be The table that the page was part of has been deleted since the page corruption was logged The system catalogs are corrupt in some way The page is corrupt and so incorrect values were used to look up the metadata – Ben Thul Sep 05 '19 at 00:33

0 Answers0