Database query store in Error State

Question

Hello,

Version - Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64)

Tried to turn it ON and command shows it completed successfully but isn't ON when checked.

We ran the sys.database_query_store_options and query shows the query store is in Error state and OFF

In SQL Server Log we see the following error:

Error: 12429, Severity: 20, State: 10.

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

We tried the ALTER DATABASE MyDatabase SET QUERY_STORE CLEAR ALL

Questions:

1. Can we restart SQL Server Service or SQL Server and expect the database to come back up in usable condition? We don't see anything in suspect_pages and dbcc checkdb is clear except for the query store error posted above.
2. Would updating to Service Pack 3 address issues with the Query Store?
3. Is there anything else we can try to fix Query Store that we haven't tried besides restarting the SQL Server Service or rebooting the SQL Server?

Thank you,
Sue

Answer 1

I would not expect a restart of SQL Server to help. Updating to SP3 may prevent the issue from occurring, but maybe not heal the database, if there already is corruption.

What possibly could help would be turn off Query Store entirely for the database and then re-enable it.

Have you run DBCC CHECKDB on the database?

Answer 2

Hi @OutThere2 ,
You can open the properties of your database and change the operation mode（in the Query Store）into Read Write.
And use the following order to start the database query store again:
ALTER DATABASE <database_name>
SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

Then you can use the following order to see if it works successfully.
USE [test];
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
max_storage_size_mb, readonly_reason, interval_length_minutes,
stale_query_threshold_days, size_based_cleanup_mode_desc,
query_capture_mode_desc
FROM sys.database_query_store_options;

Best regards
Jong

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment"

Answer 3

This resolved it for us

https://www.sqlservercentral.com/blogs/query-store-error-state-314

Answer 4

This resolved the issue for us

https://www.sqlservercentral.com/blogs/query-store-error-state-314