Sql-server – SQL Server 2017 Enterprise, DB has suspect_pages, GAM and SGAM NOT ALLOCATED – am I OK

sql serversql-server-2017

I realize I may get some "horror!" reactions, but bear with me…

There is but one suspect_pages row, and I plugged the info into DBCC PAGE (output shown below). When I saw that both GAM and SGAM show "NOT ALLOCATED", my theory became, "I need not care about it…"

We had the DB up to 5 TB with masses of data to verify. At least 1TB was TRUNCATEd at the exact time when the suspect_pages row was created – all from ONE table.

Having had suspect_pages rows appear during a similar exercise on the same DB several months prior, where DBCC PAGE showed that none of the pages cited existed in the database, we moved on, deleting the suspect_pages rows.

The IndexID is -1, the ObjectID is 0. To me, this means, "there's nothing to see here…"

In the opinion of the experts here, does it look as though it is safe to delete the current suspect_pages row? Is there a DBCC CHECKDB REPAIR% option I should use?

With thanks, and hoping for at least minimal "nice abuse"…

Command:

DBCC TRACEON (3604);
DBCC PAGE ('DB_US', 1, 731783305, 3) WITH TABLERESULTS;
DBCC TRACEOFF (3604);
GO

Produces an error:

(43 rows affected)
Msg 2514, Level 16, State 8, Line 6
A DBCC PAGE error has occurred: Invalid page metadata - dump style 3 not possible.

DBCC PAGE OUTPUT – using output level ZERO and no WITH...:

PAGE: (1:456793585)

BUFFER:

BUF @0x000002F2DA54D680

bpage = 0x00000246815A2000          bhash = 0x0000000000000000          bpageno = (1:731783305)
bdbid = 6                           breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 56821                       bstat = 0x809
blog = 0x15ab215a                   bnext = 0x0000000000000000          bDirtyContext = 0x0000000000000000
bstat2 = 0x0                        

PAGE HEADER:


Page @0x00000246815A2000

m_pageId = (1:456793585)            m_headerVersion = 1                 m_type = 2
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x2200
m_objId (AllocUnitId.idObj) = 235350m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057609461825536                                Metadata: PartitionId = 0
Metadata: IndexId = -1              Metadata: ObjectId = 0              m_prevPage = (1:456793584)
m_nextPage = (1:456793586)          pminlen = 29                        m_slotCnt = 168
m_freeCnt = 32                      m_freeData = 7824                   m_reservedCnt = 0
m_lsn = (722027:450682:278)         m_xactReserved = 0                  m_xdesId = (0:0)
m_ghostRecCnt = 0                   m_tornBits = -1231952543            DB Frag ID = 1

Allocation Status

GAM (1:731572992) = NOT ALLOCATED   SGAM (1:731572993) = NOT ALLOCATED  PFS (1:731777976) = 0x0   0_PCT_FULL
DIFF (1:731572998) = CHANGED        ML (1:731572999) = NOT MIN_LOGGED

Best Answer

To quote Paul Randal on the matter:

We’re interested in the output beginning Metadata:. Those fields are not stored on the page itself...

If you see the ObjectId is 0, that means there was no metadata found. This could be because:

  • 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

In any case, you’ll need to wait for DBCC CHECKDB to complete to know the extent of the damage.

It doesn't sound like you're in the clear, so I'd probably push on getting a suitable environment to check a full backup on.

Related Question