SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

Database Superhero’s Blog

Tracy is the Database Superhero for ChannelAdvisor. She has spent over 20 years in IT and has used SQL Server since 1999. Tracy covers all aspects of administration and deals heavily with performance tuning and high availability and disaster recovery. Tracy is a co-organizer of a Special Interest Group (SIG) dedicated to advanced DBA topics in our local user group TriPass. She is also the founder of http://WeSpeakLinux.com. Before she worked full-time as a DBA she was formally a developer and network administrator. She also tinkered with databases in middle/high school to keep her sports card collection organized.

Comments

Leave a comment on the original post [tracyboggiano.com, opens in a new window]

Loading comments...