CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Vallo
Vallo

Reputation: 1967

Don't rollback on cancelling a delete query

Is there a way to disable the rollback when a delete query is cancelled? So whatever rows where deleted remain deleted and those not deleted remain there?

I know it breaks ACID, but I made a mistake and it's been 3 hours cancelling that query...and I don't want those rows to be returned. Just so that in the future if this happens again, I won't lock the table if I cancel.

Thanks!

Upvotes: 1

Views: 2945

Answers (2)

TommCatt
TommCatt

Reputation: 5636

I know Sql Server hates cursors, but this seems like a good time to try one. There are several benefits here that would far outweigh the extra processing time:

  • You are obviously deleting a very large number of rows. Doing this in one delete statement locks probably the entire table for a long time (beyond a certain point, Sql Server will skip row locking and just try to lock the whole table). Deleting the rows one at a time greatly relieves this locking issue.
  • You can have the delete inside a try inside a loop. When an error occurs, write the details to a log table. This will provide you much needed information about what is causing the error so you can figure out how to fix it.
  • In the end, you've deleted and committed all the rows that can be deleted and you have a record of just the problematic record(s). It don't get no better than that.
  • Cursors are slow? Yeah, but if you had used one, you'd have finished with this long ago.

I know that Sql Server developers consider cursors as Evil Incarnate. They are not. They are a tool and like any tool should be enthusiastically used whenever the benefits outweigh the costs.

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

Not directly no.

What generally works is to split the delete into batches and run a WHILE loop until everything is done:

SET ROWCOUNT 10000
SELECT top 1 * from sys.objects --prime ROWCOUNT

WHILE @@ROWCOUNT > 0
BEGIN
    DELETE FROM TABLExxx WHERE 1=0 --Delete conditions
END

Which will delete 100001 rows at a time, so when you cancel you'll only undo a bit of your work.

110000 is entirely arbitrary - you might want to tune this up or down based on your own data, how long each batch takes and how much work you're happy to lose if you do cancel the script.

Upvotes: 0

Related Questions