Symptoms
Query Store automatic data cleanup may fail on SQL Server 2016 editions other than Enterprise and Developer. Therefore, if data is not manually purged, space that is used by the Query Store will grow over time until the configured limit is reached. If this issue is not resolved, disk space allocated for the error logs will also fill up, because every attempt to run cleanup will produce a dump file. The cleanup activation period depends on the workload frequency. However, it does not exceed 15 minutes.
Resolution
The fix for this issue is included in the following cumulative update for SQL Server:
Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:
Workaround
To work around this issue if you plan to use Query Store on editions other than Enterprise and Developer, you have to explicitly turn off cleanup policies. You can do this either from SQL Server Management Studio (Database Properties page) or by using the following Transact-SQL script:
ALTER DATABASE database_name SET QUERY_STORE (OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 0), SIZE_BASED_CLEANUP_MODE = OFF)
Additionally, consider manual cleanup options to prevent Query Store from transitioning to read-only mode. For example, run the following query to periodically clean the dataspace:
ALTER DATABASE database_name SET QUERY_STORE CLEAR
Also, periodically run the following Query Store stored procedures to clean runtime statistics, specific queries, or plans:
-
sp_query_store_reset_exec_stats
-
sp_query_store_remove_plan
-
sp_query_store_remove_query
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
References
Learn about the terminology Microsoft uses to describe software updates.
