How to repair the Query Store on SQL Azure Database.
Date created: October 17, 2017.
Once a SQL Azure database runs out of space the Query Store may switch to read
only mode and you may not be able to enable back the Query Store. You can verify
Query Store is on read only mode with below query, on the results you should be
able to see the "actual_state_desc"
saying "read_only".
SELECT desired_state_desc, actual_state_desc, readonly_reason,
current_storage_size_mb,
max_storage_size_mb
FROM sys.database_query_store_options
If you try to enable the Query Store as shown below.
ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE)
the following error may appear:
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.
To solve this issue let's first try to clear the Query Store:
ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE CLEAR;
GO
Now let's run a consistency check against the Query Store.
ALTER DATABASE [QueryStoreDB] SET QUERY_STORE = OFF;
GO
sp_query_store_consistency_check
GO
ALTER DATABASE [QueryStoreDB] SET QUERY_STORE = ON;
GO
That should solve the issue.
![[Company Logo Image]](images/SQLCofee.jpg)
