Cannot enable Query Store on SQL Azure database

Question

One of our Azure SQL databases ran out of space recently which I believe resulted in Query Store switching over to "READ_ONLY".

I increased the size of the database however this has not resulted in the status changing even though running this query:

SELECT desired_state_desc, actual_state_desc, readonly_reason, current_storage_size_mb, max_storage_size_mb 
FROM sys.database_query_store_options

Suggests that there is enough space available:

desired_state_desc  actual_state_desc   readonly_reason current_storage_size_mb max_storage_size_mb
READ_WRITE          READ_ONLY           524288          522                     1024

I tried to alter the Query Store status to Read_Write by running this statement (as database server admin user):

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

However, the statement failed with the following error:

User does not have permission to alter database 'QueryStoreDB', the database does not exist, or the database is not in a state that allows access checks.

Has anybody manged to switch SQL Azure Query Store to READ-WRITE so performance statistics start being collected again?

Answer 1

First, let’s try to clear the query store:

ALTER DATABASE [QueryStoreDB]   
SET QUERY_STORE CLEAR;  
GO

If that did not work, let’s run a consistency check.

ALTER DATABASE [DatabaseOne] SET QUERY_STORE = OFF;  
GO
sp_query_store_consistency_check
GO
ALTER DATABASE [DatabaseOne] SET QUERY_STORE = ON;  
GO

Try more options to troubleshoot this issue on this following article: