SQL Server Query Store switched to READ_ONLY: find the reason and restart capture

Symptom: the Query Store reports in SSMS stop showing recent activity, a regression you are chasing has no data, or a monitoring check shows actual_state_desc = READ_ONLY while you configured READ_WRITE.

While Query Store is read-only you can still read what it already holds, but new queries, plans and runtime stats are not captured. The application keeps running, so nobody notices until the data is needed. Automatic plan correction relies on the same data, so treat its view as stale too.

#Check the state and the reason

Run this in the affected database:

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

Read the two state columns first:

#Decode readonly_reason

readonly_reason is a bit map, so more than one cause can be set at once. This query expands it:

SELECT b.bit_value, b.meaning
FROM sys.database_query_store_options AS o
CROSS APPLY (VALUES
    (1,      'Database is read-only'),
    (2,      'Database is in single-user mode'),
    (4,      'Database is in emergency mode'),
    (8,      'Database is a readable secondary replica'),
    (65536,  'Reached MAX_STORAGE_SIZE_MB'),
    (131072, 'Distinct statements reached the internal memory limit'),
    (262144, 'In-memory items waiting to persist reached the internal limit'),
    (524288, 'Database reached its disk size limit')
) AS b(bit_value, meaning)
WHERE o.readonly_reason & b.bit_value <> 0;

#Map the reason to the fix

#Fix a size limit (65536)

Storage size is only checked when Query Store flushes to disk, every DATA_FLUSH_INTERVAL_SECONDS (default 900). With SIZE_BASED_CLEANUP_MODE = AUTO, cleanup starts at about 90 percent of the limit and stops near 80 percent. Under a heavy workload it can fall behind and flip to read-only temporarily, so one short occurrence may clear itself.

If it has not recovered, raise the limit:

ALTER DATABASE [YourDb]
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 2048, SIZE_BASED_CLEANUP_MODE = AUTO);

ALTER DATABASE [YourDb]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

Then force a flush and check again:

USE [YourDb];
EXEC sp_query_store_flush_db;

SELECT actual_state_desc, readonly_reason, current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

The 2048 above is an example, not a recommendation. Microsoft publishes no general target size and says the capture policy matters more than the limit. On Azure SQL Database the maximum allowed value is 10,240 MB. On SQL Server and Managed Instance the limit isn't strictly enforced, so current_storage_size_mb can go past it.

#Find what is filling it

A workload that sends literal values in its SQL creates a new query text and plan for each variation. Compare the distinct query hashes to the total:

SELECT COUNT(*) AS total_queries,
       COUNT(DISTINCT query_hash) AS distinct_hashes,
       SUM(CASE WHEN query_parameterization_type = 0 THEN 1 ELSE 0 END) AS not_parameterized
FROM sys.query_store_query;

If distinct_hashes is close to total_queries, your workload is generating different queries, typically ad hoc SQL.

Also check query_capture_mode_desc. ALL was the default on SQL Server 2016 and 2017 and captures everything, so databases that came from those versions or were restored from them often still use it. stale_query_threshold_days matters too: the current default is 30, and older databases may still carry 367.

#Tighten the capture policy

ALTER DATABASE [YourDb]
SET QUERY_STORE (
    QUERY_CAPTURE_MODE = AUTO,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30)
);

On SQL Server 2019 and later, or on Azure SQL, you can use a custom policy when AUTO still captures too much. These are the documented defaults. Change them gradually in small steps:

ALTER DATABASE [YourDb]
SET QUERY_STORE (
    QUERY_CAPTURE_MODE = CUSTOM,
    QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100
    )
);

The lasting fix for literal-heavy SQL is parameterizing it in the application. Where you can't change the code, Microsoft lists forced parameterization as an option. Test it first, because it changes plans for the whole database.

#Purge old ad hoc queries and save forced plans first

Removing a query also removes its plans and runtime stats, so record what is forced before you delete anything:

SELECT p.query_id, p.plan_id, q.object_id, p.force_failure_count,
       p.last_force_failure_reason_desc
FROM sys.query_store_plan AS p
JOIN sys.query_store_query AS q ON q.query_id = p.query_id
WHERE p.is_forced_plan = 1;

This loop deletes ad hoc queries that haven't run for seven days and skips anything with a forced plan:

DECLARE @id bigint;
DECLARE c CURSOR LOCAL FAST_FORWARD FOR
    SELECT q.query_id
    FROM sys.query_store_query AS q
    WHERE q.object_id = 0
      AND q.last_execution_time < DATEADD(DAY, -7, SYSDATETIMEOFFSET())
      AND NOT EXISTS (SELECT 1 FROM sys.query_store_plan AS p
                      WHERE p.query_id = q.query_id AND p.is_forced_plan = 1);
OPEN c;
FETCH NEXT FROM c INTO @id;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_query_store_remove_query @id;
    FETCH NEXT FROM c INTO @id;
END;
CLOSE c;
DEALLOCATE c;

Run it off-peak. Query Store lives in the user database, so these deletes do real work there.

Caution

ALTER DATABASE [YourDb] SET QUERY_STORE CLEAR; wipes all Query Store history for that database, including the data behind your forced plans and any baseline you would compare a regression against. Use it only as a last resort, and only after saving the forced plan list above.

#Query Store is in ERROR state

First try setting read-write mode. If the state stays ERROR, Microsoft's recovery on SQL Server 2017 and later is to turn Query Store off, run the consistency check, and turn it back on:

USE [YourDb];
ALTER DATABASE [YourDb] SET QUERY_STORE = OFF;
EXEC sp_query_store_consistency_check;
ALTER DATABASE [YourDb] SET QUERY_STORE = ON;
ALTER DATABASE [YourDb] SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

If that doesn't work, or you are on SQL Server 2016, the remaining option is SET QUERY_STORE CLEAR followed by read-write mode.

#Prevent it next time

Add a scheduled check that alerts when any database isn't in the state you asked for. It reads every online database you can access:

CREATE TABLE #qs (db sysname, desired nvarchar(60), actual nvarchar(60),
                  readonly_reason bigint, used_mb bigint, max_mb bigint);

DECLARE @sql nvarchar(max) = N'';
SELECT @sql += N'INSERT #qs SELECT N' + QUOTENAME(name, '''') +
    N', desired_state_desc, actual_state_desc, readonly_reason,
        current_storage_size_mb, max_storage_size_mb
      FROM ' + QUOTENAME(name) + N'.sys.database_query_store_options;'
FROM sys.databases
WHERE is_query_store_on = 1 AND state_desc = 'ONLINE' AND HAS_DBACCESS(name) = 1;
EXEC (@sql);

SELECT *, used_mb * 100 / NULLIF(max_mb, 0) AS pct_used
FROM #qs
WHERE actual <> desired OR used_mb * 100 / NULLIF(max_mb, 0) >= 85;

DROP TABLE #qs;

• Run it from a SQL Agent job and alert on any returned row. Put readonly_reason in the alert text.
• Use QUERY_CAPTURE_MODE = AUTO and SIZE_BASED_CLEANUP_MODE = AUTO on every production database.
• Check stale_query_threshold_days and query_capture_mode_desc after restores and upgrades, since those settings travel with the database.
• Review forced plans monthly. A non-zero force_failure_count means a plan you rely on isn't being applied.