I have a query that takes 60 million rows, copies them in a new table, does some updates on them, then inserts them into another new table. It's all wrapped in a transaction. I thought this would be good at first, but realize it's a dumb mistake.
While it ran for 7 hours on my local instance machine, on the production DB it took over 2 days to run! Tired of waiting and realizing that my solution was probably not the best way to do this, I closed my query window and did a kill on the process. Now, it's doing a rollback.
Meanwhile, I've run this: KILL 60 WITH STATUSONLY and EXEC sp_who2 'active'. The status says "SPID 60: transaction rollback in progress. Estimated rollback completion: 62%. Estimated time remaining: 31849 seconds". The seconds and diskio is going up, so I know it's trying to do the rollback. It's extremely slow and going up a percentage complete every several hours.
My issue is that because of my actions, the database got huge and is taking up disk space. Furthermore, I don't care about these two new tables I tried to copy into. I'd like to truncate/drop them and start over with a much better method of solving this issue. However, I can't truncate/drop them in the middle of the rollback? I could care less what happens to these new tables. They are also in their own file group and .ndf files. I just want to delete these as quickly as possible so I can resume my better method. I'm on SQL 2014 and need urgent help as it's Saturday and I'd rather not bother my boss! :)
