1

Today I have found 3 production databases writing this message to SQL Server logs :

The Query Store in database XXX has an invalid structure for internal table plan_persist_plan, possibly due to schema or catalog inconsistency.

DBCC CHECKDB runs daily. Today it did not find any inconsistency in all databases. So I executed :

DBCC CHECKTABLE ('sys.plan_persist_plan')

which returns a similar output on all 3 impacted databases :

DBCC results for 'sys.plan_persist_plan'. There are 82193 rows in 1580 pages for object "sys.plan_persist_plan". DBCC execution completed. If DBCC printed error messages, contact your system administrator.

As all seems to be fine, I finish with :

ALTER DATABASE someDB SET QUERY_STORE CLEAR ALL
ALTER DATABASE someDB SET QUERY_STORE (OPERATION_MODE = READ_WRITE)

Query Store is sized 2 to 5 GB, and configured with the very same options on all prod databases.

Does anyone encounter a similar issue ? I see several messages in sys.messages about this :

SELECT  *
FROM    sys.messages
WHERE   text LIKE 'The Query Store%'
AND language_id = 1033
2
  • Can you post the entire error, including state. Dec 28, 2022 at 21:35
  • The error number is 12429 its severity is 16. The message is found in SQL Server logs, so I don't have a state. The XXX above stands for the real name of the database, which I cannot share. Dec 29, 2022 at 9:38

1 Answer 1

2

I encountered that error in another Q&A where I manually inserted "invalid" data into the query store-related base tables using the Dedicated Admin Connection (DAC). The data was "invalid" because I was inserting query store data about object IDs that didn't exist in the target database.

Not that I think that specific thing is happening here. That would be weird.

What I am saying is that I don't think the "inconsistency" mentioned in the error message necessarily indicates corruption.

This is purely speculation, but it's possible that similar inconsistencies could happen with schema changes happening at high concurrency, or if you have global trace flags 7745 and 7752 (reference: Use Query Store in mission-critical servers during such changes and a restart or failover occurs.

By the way, you mentioned

The message is found in SQL Server logs, so I don't have a state.

You should typically see the state in the SQL Server logs. It showed up in my test case from the linked Q&A:

Error: 12434, Severity: 20, State: 56. The Query Store in database Sandbox is invalid, possibly due to schema or catalog inconsistency.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.