We have a SQL Server 2016 SP1 CU7 Enterprise server where we enabled the query store on a database. This is a highly used database.
Max Size has been set to 200MB and
Stale Query Threshold is 1 day.
sys.database_query_store_options to check the properties of the query store and everything works perfect as long as
the current_storage_size_mb stays under the 200MB.
Size Based Cleanup Mode to Auto but no cleanup happens, normally a cleanup should be triggered when the query store is 90% full. What happens next is that the query store goes into readonly mode because there is no available space left.
I've tried changing
Query Store Capture Mode to Auto but nothings changes.
If I then change
Stale Query Threshold to 30 days the
actual_state_desc changes to READ_WRITE and the query store starts capturing again. The
current_storage_size_mb just becomes higher than the
max_storage_size_mb and after a while the
actual_state_desc goes back to READ_ONLY.
Can anybody explain this behavior?