Query Store Error State

,

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

Rate

Share

Share

Rate