0

I activated query store about a month ago with the settings below. Now, every morning starting up SSMS I find the Query store folder gone and query store disabled. Initially I used 1 Gb size, but changed it to five once it started disabling. This did not alleviate the problem. I found nothing about this with google. I'm using SSMS 18.9.2. and SQL Server 14.0.3411.3. What could be causing this problem? Last server reboot was over a week ago and I've activated it twice since, so this does not seem to be the cause.

Query store parameters

7
  • what does SELECT actual_state_desc, desired_state_desc, current_storage_size_mb, max_storage_size_mb, readonly_reason FROM sys.database_query_store_options; show ? Sep 27, 2021 at 13:02
  • Do you deploy from Visual Studio? If the database project settings there don't have Query Store enabled then it will disable it to match. Sep 27, 2021 at 13:37
  • @JonathanFite No, it's a Dynamics Nav database, which i have tampered with directly from SSMS. But the structure (e.g. indexes) is maintained from the Nav development environment. Could it be that it resets when our ERP partner drops some other changes into the production database? I would find it far fetched that such an operation would reset all parameters, but I'll have to ask.
    – mpn275
    Sep 27, 2021 at 14:29
  • @StephenMorris-Mo64 I activated it this morning, and your query once again shows that actual_state_desc=ERROR and desired_state_desc=OFF. Current and desired sizes are 910 and 5000, and readonly_reason = 0. So it has apparently run into some error. Would you happen to know how to find out what error has been encountered?
    – mpn275
    Sep 27, 2021 at 14:34
  • An externally managed database? I wouldn't put it past them to turn off Query Store on the regular to prevent you from doing what you are doing (i.e. easily forcing specific plans). Sep 27, 2021 at 14:36

1 Answer 1

0

Apparently there is a native procedure to fix this, upwards from SQL Server 2017. I found the below code from a site I no longer remember, which fixed the issue for me, at least for now:

USE db_name
IF EXISTS (SELECT * FROM sys.database_query_store_options WHERE actual_state=3)
BEGIN
BEGIN TRY
    ALTER DATABASE [db_name] SET QUERY_STORE = OFF
    EXEC dbo.sp_query_store_consistency_check
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

It cleared the ERROR state and restored the READ_WRITE mode.

2
  • my search engine would suggest it was this one :- tracyboggiano.com/archive/2018/06/query-store-error-state Oct 4, 2021 at 7:25
  • @StephenMorris-Mo64 That could have been it, although I feel I saw the same code in a few places. I ran it without setting the read_write mode explicitly, and even though the desired state was 0 in query store options, actual and desired states were still restored to read_write (2).
    – mpn275
    Oct 4, 2021 at 7:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.