Database corruption: QueryStore internal table

This morning, the following email alert was received:


DATE/TIME: 2/28/2018 9:26:42 AM


DESCRIPTION: Attempt to fetch logical page (1:3948712) in database 9
failed. It belongs to allocation unit 72057594045857792 not to
72059184917512192.


COMMENT: (None)


JOB RUN: SQL Sentry 2.0 Alert Trap

Looking in the event log of the secondary replica there are three occurrences of the same message:


Source spid138


Message Attempt to fetch logical page (1:3948712) in database 9
failed. It belongs to allocation unit 72057594045857792 not to
72059184917512192.

Running the following on the secondary replica (2 node synchronous Availability Group):

DBCC TRACEON(3604)
dbcc page (9, 1,3948712,3)
go
DBCC TRACEOff(3604)

Snippet of the results from either replica:

Page @0x00000070DAB8C000

m_pageId = (1:3948712)              m_headerVersion = 1               
m_type = 3 m_typeFlagBits = 0x0                m_level = 0            
m_flagBits = 0x8200 m_objId (AllocUnitId.idObj) = 129   m_indexId
(AllocUnitId.idInd) = 256  Metadata: AllocUnitId = 72057594046382080  
Metadata: PartitionId = 72057594040811520                             
Metadata: IndexId = 1 Metadata: ObjectId = 197575742      
m_prevPage = 0:0)                  m_nextPage = (0:0) pminlen = 0                 
m_slotCnt = 2                       m_freeCnt = 1634 m_freeData = 6568
m_reservedCnt = 0                   m_lsn = (46041:1506360:18)
m_xactReserved = 0                  m_xdesId = (0:0)                  
m_ghostRecCnt = 0 m_tornBits = -99702035              DB Frag ID = 1

Running the following on the primary replica:

select OBJECT_NAME (197575742)

plan_persist_plan

Questions

• Am I right in saying that I have a clustered index corruption of the plan_persist_plan table which is part of Query Store?

-
Is the best/only fix to run the following:

ALTER DATABASE MyDatabase SET QUERY_STORE CLEAR;

-
If #2 is the best fix,

As noted in my comment above I had a similar corruption issue with a query store internal table.

As you yourself have suggested I used ALTER DATABASE MyDatabase SET QUERY_STORE CLEAR; to attempt to fix the issue and that did work fine. In SQL Server 2017, Microsoft added a repair procedure that can be attempted prior to clearing the data: sp_query_store_consistency_check (source)

If you want to preserve the data then probably the only method is to copy the tables - I can't find anyone who has created a script for that.

Usually with corruption I too would be worried about my disks, but in this case I'm a little suspicious that the issue is with query store itself.

StackExchange Database Administrators Q#199023, answer score: 5