[Company Logo Image] 

Home Up Contents Coffee Break Credits Glossary Links Search

 
How to repair the Query Store on SQL Azure Database

 

 

Home
Analysis Services
Azure
CLR Integration
High Availability
Open Source
Security
SQL Server 2008
SQL Server 2012
SQL Server 2014
SQL Server 2016
SQL Server 2017
SQL Server 2019
Tips
Troubleshooting
Tuning

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.

 

 

 

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.