If I Stop a Long Running Query, Does It Rollback

If I stop a long running query, does it rollback?

no, sql server will not roll back the deletes it has already performed if you stop query execution. oracle requires an explicit committal of action queries or the data gets rolled back, but not mssql.

with sql server it will not roll back unless you are specifically running in the context of a transaction and you rollback that transaction, or the connection closes without the transaction having been committed. but i don't see a transaction context in your above query.

you could also try re-structuring your query to make the deletes a little more efficient, but essentially if the specs of your box are not up to snuff then you might be stuck waiting it out.

going forward, you should create a unique index on the table to keep yourself from having to go through this again.

SQL Server: If I stop a single long running update before it is finished, will it roll back automatically?

If your command to cancel came in time, it was rolled back in its entirety. DML statements are always all or nothing. You should probably check the data to make sure that your cancel did arrive in time. It might have arrived in the last millisecond or so after the transaction was already committed.

How to kill/stop a long SQL query immediately?

What could the reason be

A query cancel is immediate, provided that your attention can reach the server and be processed. A query must be in a cancelable state, which is almost always true except if you do certain operations like calling a web service from SQLCLR. If your attention cannot reach the server it's usually due to scheduler overload.

But if your query is part of a transaction that must rollback, then rollback cannot be interrupted. If it takes 10 minutes then it needs 10 minutes and there's nothing you can do about it. Even restarting the server will not help, it will only make startup longer since recovery must finish the rollback.

To answer which specific reason applies to your case, you'll need to investigate yourself.

need to cancel a long running query on sql server

If I cancel it will it cause a rollback?

Yes, and it likely takes longer than it was running so far.

nd of so, is there a way to cancel the query without causing a rollback?

No. The data now is in an inconsistent state without rollback.

Does a T-SQL transaction get rolled back if cancelled?

If the query hasn't finished before being cancelled then yes, it was rolled back. Either whole update was executed or nothing was changed.

Cancel long running query

Since you begin and commit each transaction separately i.e. Explicit Transactions are used, the rollback will be restricted only to whichever one is running at the moment. The ones before it are already executed and data has been changed in DB.

---

Related Topics