A customer emailed me a couple of weeks ago with something that sounded, on the face of it, like a haunting.
He’d been running ALTER DATABASE ... SET QUERY_STORE = ON. The command succeeded. About ten seconds later, Query Store was off again. He’d tried this several times over several days and got the same result every time. His question was fair enough: who or what is turning this off?
Nobody was. Nothing was. The database had a Query Store in ERROR state, and once you know what you’re looking at, the fix takes a minute.
This post covers how to confirm that’s what you’re dealing with, why the state reverts within seconds of you enabling it, and the two ways out — one that keeps your query history and one that doesn’t.
Check the Query Store state on every database
Before theorising, read the state of every database on the instance. Not just the one that’s complaining. The comparison is half the diagnosis.
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql = @sql + N'
USE ' + QUOTENAME(name) + N';
SELECT
DB_NAME() AS [Database],
desired_state_desc,
actual_state_desc,
readonly_reason,
current_storage_size_mb,
max_storage_size_mb
FROM sys.database_query_store_options;'
FROM sys.databases
WHERE state = 0 AND database_id > 3;
EXEC sp_executesql @sql;The results came back like this:
| Database | Desired State | Actual State | Readonly Reason | Current Size (MB) | Max Size (MB) |
|---|---|---|---|---|---|
| msdb | OFF | OFF | 0 | 0 | 100 |
| AppDB1 | OFF | ERROR | 0 | 121 | 2048 |
| AppDB2 | OFF | OFF | 0 | 0 | 100 |
| AppDB3 | READ_WRITE | READ_WRITE | 0 | 3 | 2048 |
| AppDB4 | OFF | OFF | 0 | 0 | 100 |
| AppDB5 | READ_WRITE | READ_WRITE | 0 | 898 | 2048 |
Five databases fine, one in ERROR. Good. A single-database problem is a much smaller problem than an instance-wide one.
Two numbers in that row did most of the work for me.readonly_reason was 0, which kills off all the boring explanations in one go. And the database was using 121 MB out of 2048, so it wasn’t a space problem either.ERROR state, no read-only reason, plenty of headroom. That combination means the Query Store metadata inside the database is corrupt. It isn’t misconfigured. It’s broken.
What the readonly_reason values mean
Worth committing these to memory, because a non-zero value here sends you somewhere completely different. The column is a bitmask, so you can see combinations.
| Value | Meaning |
|---|---|
| 1 | Database is in read-only mode |
| 2 | Database is in single-user mode |
| 4 | Database is in emergency mode |
| 8 | Database is a secondary replica in an availability group |
| 65536 | Query Store has reached MAX_STORAGE_SIZE_MB |
| 131072 | The number of statements has exceeded the internal limit |
| 262144 | In-memory items waiting to be persisted exceeded the limit |
The value 8 is the one that catches people out. On an Always On secondary replica, Query Store is read-only by design and there is nothing to fix. If you’re chasing a “Query Store won’t write” complaint in an AG environment, check that first and save yourself an afternoon.
A readonly_reason of 65536 is also not this problem — that’s a sizing conversation, not a corruption one.
Why Query Store reverts to ERROR after you enable it
This is the bit that makes the customer’s description make sense.
Query Store keeps its data in internal tables inside the user database itself. When you turn it back on, SQL Server doesn’t simply set a flag and move on. It runs a recovery pass across those internal tables first. If that pass decides the data is inconsistent, it abandons the attempt and puts the database’s Query Store back into a non-operational state.
So the sequence the customer saw was real. Command succeeds. Recovery runs. Recovery fails. Off again. Ten seconds, near enough.
As for how it got corrupt in the first place: Query Store runs background cleanup, and if a failover or a service restart lands while cleanup is halfway through its work, the internal tables can end up in a shape the recovery logic refuses to accept. It’s a race condition. You don’t cause it and you can’t reliably reproduce it.
Their environment had failed over recently. There it is.
Fix it with sp_query_store_consistency_check
sp_query_store_consistency_check runs a repair over those internal tables. It’s been there since SQL Server 2016 SP2 and 2017, and it should always be your first move because it doesn’t throw anything away.
USE AppDB1;
GO
EXEC sp_query_store_consistency_check;
GO
ALTER DATABASE AppDB1 SET QUERY_STORE = ON;
ALTER DATABASE AppDB1 SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
GO
SELECT actual_state_desc, desired_state_desc
FROM sys.database_query_store_options;Both columns should read READ_WRITE.
Then wait a minute and check again. I mean it. The entire character of this bug is that it takes a few seconds to reassert itself, so if you check instantly and walk away happy, you have learned nothing.
One warning: on a database with a large Query Store this can run for a while, and it takes locks on the internal tables while it does. Not a thing to fire off casually at 3pm on a busy production box.
Clearing Query Store, and what it costs you
If the consistency check doesn’t get you there, you clear it out and start again.
USE AppDB1;
ALTER DATABASE AppDB1 SET QUERY_STORE = OFF;
ALTER DATABASE AppDB1 SET QUERY_STORE CLEAR ALL;
ALTER DATABASE AppDB1 SET QUERY_STORE = ON;
ALTER DATABASE AppDB1 SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
SELECT actual_state_desc, desired_state_desc
FROM sys.database_query_store_options;This works. It also destroys every runtime statistic, every query, every plan, and every bit of history the store had collected.
And your forced plans. That’s the one people forget until it’s too late. If you’ve forced a plan on that database to hold a regression down, go and write it down before you clear anything:
SELECT p.plan_id, p.query_id, q.query_hash, p.plan_forcing_type_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;Those plan IDs don’t survive the clear. You’ll need the workload to run for a while and repopulate the store before you can force anything again, and if that query was only behaving because of the forced plan, you’re going to feel it in the gap.
How to stop it happening again
Patch the instance. There have been fixes to Query Store cleanup and consistency behaviour across the 2016, 2017 and 2019 cumulative updates. If you’re running a build from a few years back, check that before anything else.
Leave SIZE_BASED_CLEANUP_MODE on AUTO. Turn it off and the store fills up, goes read-only, and someone ends up running manual cleanup instead. Manual cleanup during a maintenance window is precisely the operation that races with a failover.
Size MAX_STORAGE_SIZE_MB properly. The 100 MB default isn’t enough for a real workload. The two active databases above were set to 2 GB and one of them was already at 898 MB.
And the one that actually matters: monitor actual_state_desc, not desired_state_desc.
Nearly every Query Store health check I’ve seen confirms that it’s supposed to be on. Very few confirm that it is. That gap is where this whole problem lives, and it can sit there for months while you assume you’re collecting plan history. You find out during an incident, at the exact moment you go looking for the data that would have told you what changed, and there’s nothing there.
The fix in this case took a minute. Not noticing would have been the expensive part.
Common questions
Why does Query Store turn itself off after I enable it?
Because the enable isn’t finishing. SQL Server runs a recovery pass over Query Store’s internal tables when you switch it on, and if that data is inconsistent the pass fails and the state drops back. It looks like something is disabling it a few seconds later. Nothing is.
What does actual_state ERROR mean in sys.database_query_store_options?
It means Query Store is non-operational because its internal metadata is damaged, as opposed to being deliberately off or read-only. Check readonly_reason at the same time — if it’s non-zero, you have a different and usually simpler problem.
Does sp_query_store_consistency_check delete my query history?
No. It repairs the internal tables in place. That’s why it’s the first thing to try. Only QUERY_STORE CLEAR ALL wipes history, and it takes your forced plans with it.
Is it safe to run sp_query_store_consistency_check on production?
Functionally yes, but it holds locks on Query Store’s internal tables and can run for a long time on a large store. Treat it as a maintenance window operation on a busy system.
Why is Query Store read-only on my availability group secondary?
That’s expected. readonly_reason will be 8. Secondary replicas can’t write to Query Store, and there’s nothing to repair.