killing a spid without rollback

Question

killing a spid without rollback

don farrell

Mr or Mrs. 500

Points: 575
More actions
January 5, 2005 at 3:35 am

#152328

Hi,
Background :
Im in the middle of archiving old data. I have a table with 6million rows. I issues a delete statement which should have deleted about half the data. It has been running for 17 hours and is now causing huge problems. The site has become unusable. I cant determine how much data is left to delete. I want to kill the spid and delete smaller batches of data but am afraid that the rollback will take too long.
Question :
Is there a way to kill a spid without it going into a rollback state ?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

Nope, a DELETE like an UPDATE or INSERT is an 'all or nothing' transaction. If you kill the process, the delete has not finished (<>all deleted) so everything will rollback.

Your delete might be caught in a loop, it might be blocked, it might be deadlocked....you will need to review the script, the processes, etc to see what might be the problem. But stopping it will cause a rollback - Which might be a good thing. How do you know what your delete is doing? If it's taking an unusually long time, it might be doing the wrong thing.

-SQLBill

pnewhart SSC Eights! Points: 983 More actions · Answer 2

For future reference, you may want to consider doing what I do when deleting a considerable amount of archived data. I create a temporary table where I store the values of the primary key column of the table I want to delete then I run a vb program that loops through the temporary table and deletes only the records from the first table based on a match in the temporary table. It is cursor like since it loops through each record, but you may able to use the idea to come up with a way to run it in query analayzer. I usually make the temporary table column be the primary key and run the loop in an easily manageable set of 10K or 20K rows, depending on response time. I also remove the entries from the temp table after I delete them from the primary table. It sometimes takes a while, but the primary table is not usually locked for a long period since the transaction set is small.

don farrell Mr or Mrs. 500 Points: 575 More actions · Answer 3

thanks for the guys...ill keep that in mind when archiving the additional data