We recently discovered some of our databases in an Error State for SQL Server. Microsoft has indicated this is a race condition when failover/restart happens on QDS cleanup. They are working on a bug fix for it. For now, it suggested you set up something that checks your databases to see if it enters an error state and automatically runs the code below especially if you running 2017 with Automatic Plan Tuning.
IF EXISTS (SELECT * FROM sys.database_query_store_options WHERE actual_state=3)
BEGIN
BEGIN TRY
ALTER DATABASE [QDS] SET QUERY_STORE = OFF
Exec [QDS].dbo.sp_query_store_consistency_check
ALTER DATABASE [QDS] SET QUERY_STORE = ON
ALTER DATABASE [QDS] SET QUERY_STORE (OPERATION_MODE = READ_WRITE)
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
END
I have wondered if there is an error raised when Query Store fails. I haven’t been able to find one.
Nope there isn’t one that I have been able find. All you can do is query across the dbs and check the status and correct. Our company did write a PowerShell module to fix this if you are interested.
I am interested in your solution. Does your T-SQL work just as well(the script in this post)? How often do you run the solution and is it an SQL Agent job?
Thanks
Chris
If you want to run it as a SQL Agent job you can run the following code to cover all the databases where query store has been turn on your server:
DECLARE @SQL AS NVARCHAR(MAX) = ”
SELECT @SQL += REPLACE(‘USE [{{DBName}}]
IF EXISTS (SELECT * FROM sys.database_query_store_options WHERE actual_state=3)
BEGIN
BEGIN TRY
ALTER DATABASE [{{DBName}}] SET QUERY_STORE = OFF
Exec [QDS].dbo.sp_query_store_consistency_check
ALTER DATABASE [{{DBName}}] SET QUERY_STORE = ON
ALTER DATABASE [{{DBName}}] SET QUERY_STORE (OPERATION_MODE = READ_WRITE)
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
END’
,'{{DBName}}’, name)
FROM sys.databases
WHERE is_query_store_on = 1
;
EXEC (@SQL);
Also, the frequency is going to depend on how important the data is to you. If you have APC then you may want to run every hour to keep your performance more consistent.
Thanks. I got busy last week and will start to try this out later this week.
Chris