Applies ToSQL Server 2016 Express - duplicate (do not use) SQL Server 2016 Standard - duplicate (do not use) SQL Server 2016 Web - duplicate (do not use)

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:

Cumulative Update 1 for SQL Server 2016

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:

Latest cumulative update for SQL Server 2016

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 CLEARAlso, 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.

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.