Database query store in Error State

OutThere2 1 Reputation point
2022-08-31T16:21:37.997+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,117 questions
{count} votes

4 answers

Sort by: Most helpful
  1. Erland Sommarskog 104.2K Reputation points MVP
    2022-08-31T21:41:54.287+00:00

    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?


  2. PandaPan-MSFT 1,906 Reputation points
    2022-09-01T06:47:52.357+00:00

    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);
    236775-image.png

    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;

    236833-image.png
    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"


  3. Shri Shahapurkar 0 Reputation points
    2023-10-20T00:27:29.2366667+00:00
    0 comments No comments

  4. Shri Shahapurkar 0 Reputation points
    2023-10-20T00:29:49.0566667+00:00
    0 comments No comments