This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(153.6, 99%, 24%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJA%3C/text%3E%3C/svg%3E)
I tried to turn of the query store on a large transaction database we have, using the command ALTER DATABASE <MyDatabaseName> SET QUERY_STORE = OFF (FORCED)
The query ran for 12 hours before we tried to kill it - now it's been in rollback for 24 hours. It's blocking all backups and index jobs.
Is it safe to restart the sql service or will that result in a huge recovery time? Why would this command take so long to run and/or roll back?
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(28.799999999999997, 2%, 12%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESX%3C/text%3E%3C/svg%3E)
Hi @Jason Allen ,
We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it is not help, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(22.400000000000002, 35%, 12%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EHU%3C/text%3E%3C/svg%3E)
I will NOT advice you to restart the SQL Service, it will re-do the rollback again and DB recovery may take longer.
What does "SELECT @@version" report? This issue seems somewhat familiar., but it may have been fixed.
For now, I doubt that you have much choice but to restart SQL Server.
Hi @Jason Allen ,
>Is it safe to restart the sql service or will that result in a huge recovery time?
Normally, it will not be faster and not be safe, because you have to start the recovery again from the beginning of the transaction and determine where to start repairing the database. There may be data corruption in the process.
Therefore, it is not recommended to restart sql service.
However, if your environment has node failover, you can refer to this blog:https://blog.sqlauthority.com/2020/03/30/sql-server-unable-to-disable-query-store-for-always-on-database/
>Why would this command take so long to run and/or roll back?
Query Store does take up resources to run, it needs to collect information about the query, and build its data storage in the SQL Server database, so it is recommended to try to use the tool during non-working hours.
Also, you can gather information from DMVs to check whether the process is deadlocked or blocked.
Please refer to this blog: https://learn.microsoft.com/en-us/troubleshoot/sql/performance/understand-resolve-blocking
Best regards,
Seeya
If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Query Store does take up resources to run, it needs to collect information about the query, and build its data storage in the SQL Server database, so it is recommended to try to use the tool during non-working hours.
What on Earth are you trying to say here? That we should keep Query Store disabled during office hours? Doesn't that defy the entire idea with the feature?
There are workloads which Query Store does not play well, but if you don't have such workload, you should have Query Store running 24 hours a day. It is a tremendous asset if you get a performance problem.