9

I recently updated our 2016 SQL Server to SP2 and the most recent CU (KB4458621) released in August 2018. Just in the last day or so, I noticed that I have some blocking going on. I can't kill the SPID b/c it isn't a user process. According to SP_WHO2, the command is "Query Store ASYN". I've tried purging the data and disabling the query store via Script and through the UI. Nothing seems to work and it just spins and then starts to cause more blocking. Is anyone else having this issue? Can anyone please help me figure out how to disable the query store successfully? SP_WhoIsActive @show_System_SPIDS = 1 results below (query store results only)

SP_WhoIsActive 1 SP_WhoIsActive 2

UPDATE - This is now caused TempDB drive to fill up. Going to try a reboot in a few hours and see if that resolves the issue. Will keep you posted.

Thanks, Nate

Nate
  • 91
  • 4

2 Answers2

4

Based on your explanation, I believe this is related to KB4461562, and was resolved in the latest Cumulative Update 5.

FIX: Transactions and log truncation may be blocked when you use Query Store in SQL Server 2016 and 2017

Randolph West
  • 3,733
  • 13
  • 27
1

I am having the same problem with SQL Server 2016 SP2 CU12, it seems that the FIX included in Cumulative Update 5 does not solve it.

I have seen that there is also KB4511715:

FIX: Filled transaction log causes outages when you run Query Store in SQL Server 2016 and 2017

And that is fixed in SQL Server 2016 SP2 CU8, but considering that my version is SP2 CU12, it seems that it does not work as expected either.

At the moment we have scheduled a restart of the Instance and an update to the latest CU to see if it is solved.

Fran Lens
  • 21
  • 2