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.
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:
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)
- Am I right in saying that I have a clustered index corruption of the
plan_persist_plantable 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, is there any good way of preserving the data in Query Store that would be deleted?
- Does this kind of corruption indicate a problem with the IO subsystem?
- I have QueryStore enabled obviously, it has a capacity of 350MB, is in Read-Write mode currently, flush interval 15 minutes, stats collection hourly, Capture mode ALL, Auto size based cleanup, 5 day stale query threshold.
- DB id 9 is a business critical user database
- The error details are Error: 605, Severity: 21, State: 3.
I have checked the Windows System Event log as per the guidance. This has yielded only "Informational" events, no errors.
DBCC CHECKTABLE ('sys.plan_persist_plan');
DBCC results for 'sys.plan_persist_plan'. There are 12562 rows in 240 pages for object "sys.plan_persist_plan". DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I cannot establish the correct command to rebuild the index, the following does not work:
ALTER INDEX PK_plan_persist_plan_cidx ON sys.plan_persist_plan REBUILD;