0

I'm using Microsoft SQL Server 2016 (SP2-GDR) (KB4505220) - 13.0.5101.9 (X64).

One of our databases seem to have a run away Query Store with over 90 gb stored. I want to disable QS, but cannot do so because disable queries get blocked.

After a server reboot, the database got stuck in something related to query store loading, so every query you ran was blocking in that db due to QDS_LOADDB.

ALTER DATABASE someDb SET QUERY_STORE = OFF and ALTER DATABASE someDb SET QUERY_STORE CLEAR ALL are likewise stuck against some SQL Server internal-process which was unkillable.

After i enabled trace flags 7745 + 7752, i could at least move the query store loading into async load, which doesn't block regular queries. But ALTER DATABASE someDb SET QUERY_STORE = OFF still blocks due to QUERY STORE ASYNC LOAD command.

The question, how do i disable Query Store on such a run away database? I have a bad feeling that the "QUERY STORE ASYNC LOAD" will never finish

1 Answer 1

2

Did you try ?

ALTER DATABASE [dbname] SET QUERY_STORE = OFF (FORCED)
2
  • Yeah, it's not available in my server version (incorrect syntax near FORCED). But if everything fails, i might be able to patch it to the latest SP Commented Feb 21 at 13:11
  • 1
    yeah I'd definitely think of patching, you have quite a few security patches missing. this syntax came in CU14 of SP2 sqlskills.com/blogs/erin/… In the link below you can see all of the builds (including the remote code execution fixes ;-) ) sqlserverbuilds.blogspot.com/#sql2016x Commented Feb 21 at 13:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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