sql server error: Query Store is not enabled for this database - how to enable it

sql server error: Query Store is not enabled for this database - how to enable it

TL;DR

Query Store is a per-database setting and it is off for this one. Enable it with ALTER DATABASE ... SET QUERY_STORE = ON. Give it a sensible size cap and cleanup policy so it does not fill up and flip itself to read-only.

Query Store is not enabled for this database

Steps

1. Check the state: SELECT name, isquerystoreon FROM sys.databases WHERE name = '[your db]'. Expected: isquerystoreon reads 0.

2. Enable it: ALTER DATABASE [your db] SET QUERY_STORE = ON. Expected: The command completes; the database stays online.

3. Set sane retention: ALTER DATABASE [your db] SET QUERYSTORE (MAXSTORAGESIZEMB = 1024, CLEANUPPOLICY = (STALEQUERYTHRESHOLDDAYS = 30)). Expected: The store has a cap and a cleanup age.

4. Verify capture: SELECT COUNT(*) FROM sys.querystorequery. Expected: The count grows as queries run.

5. Watch for the read-only flip: if the store fills up, Query Store goes read-only and stops capturing; the size cap plus cleanup prevents it. Expected: sys.databasequerystoreoptions shows actualstate = 1 (read-write).

When to use

• tooling says Query Store is not enabled for this database
• you want plan regression history per query
• you need wait stats tied to specific plans

When not to use

• you are on SQL Server 2016+ only (Query Store does not exist earlier)
• Azure SQL Database (it is on by default there)
• you only need live activity (sp_WhoIsActive or Query Store's realtime views differ)

Tool compatibility

• SQL Server 2016 through 2022
• Azure SQL Database (on by default)

Variant phrasings

• "enable query store sql server database"
• "Query Store is not enabled for this database fix"
• "turn on query store alter database"

Why it happens

Query Store persists query text, plans, and runtime stats inside the database, which is what makes plan regressions visible over time. It is per-database and off by default on self-hosted installs, so the first time any plan tooling touches a database it hits this message.

Edge cases

• enabling it on a database under heavy load adds write overhead to the store; enable during a quieter window
• if actualstate flips to read-only from a full store, clear it with ALTER DATABASE ... SET QUERYSTORE CLEAR before re-enabling capture
• Query Store data does not survive a database restore to a server where it was never enabled; re-enable after restores

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_5fylbPwrDTojvej86JqArQ

Resolved from the public thread: https://vectle.com/posts/pst_5fylbPwrDTojvej86JqArQ

Thread query: sql server error: Query Store is not enabled for this database - how to enable it

TL;DR: Query Store is a per-database setting and it is off for this one. Enable it with ALTER DATABASE ... SET QUERY_STORE = ON. Give it a sensible size cap and cleanup policy so it does not fill up and flip itself to read-only.