Azure - Automated recommendation management is disabled because Query Store has reached its capacity-CodePudding

We enabled, Automatic tuning in Azure a while ago but when I checked it today, it displays the below message and appears that the Automatic tuning is not funtionaing.

Automated recommendation management is disabled because Query Store has reached its capacity limit and is not collecting new data. Learn more about the retention policies to maintain Query Store so new data can be collected.

Where can I check/set this Query Store capacity?

CodePudding user response：

Only when the Query Store is in read-only mode do we get this warning. Double-check your query store's settings.

Try explicitly setting the read-write mode and rechecking the real state to restore Query Store.

If the situation persists, it implies that the Query Store data has been corrupted and is persisting on the disc. Within the impacted database, run the sp_query_store_consistency_check stored procedure to restore Query Store.

NOTE: Query Store functionality was added to track performance and is currently in development. There are certain restrictions that are well-known.

It currently does not function with read-only databases (Including read-only AG replicas). Because the query store on reading secondary Replicas is read-only, the query store on those secondary replicas is read-only as well. This means that the query store does not collect runtime statistics for queries conducted on those replicas.

If you checked it and it's set to read-write, we're probably dealing with a problem. The best thing could be raising posting it in Microsoft QnA for more insights and help.

CodePudding user response：

Try to set the operation mode to read_write and verify it stays in that state.

ALTER DATABASE [QueryStoreDB]   
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);    
GO  

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,   
max_storage_size_mb, readonly_reason, interval_length_minutes,   
stale_query_threshold_days, size_based_cleanup_mode_desc,   
query_capture_mode_desc  
FROM sys.database_query_store_options;

If that does not work, try to clear the Query Store and set it to read-write operation again with below statements:

ALTER DATABASE [QueryStoreDB]   
SET QUERY_STORE CLEAR;  
GO  

ALTER DATABASE [QueryStoreDB]   
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);    
GO  

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,   
    max_storage_size_mb, readonly_reason, interval_length_minutes,   
    stale_query_threshold_days, size_based_cleanup_mode_desc,   
    query_capture_mode_desc  
FROM sys.database_query_store_options;