6

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! :)

  • 10
    Nothing you can do except wait for the rollback or restore a backup. If you stop the server, the rollback will restart when you restart the server. – Max Vernon Dec 26 '15 at 8:21
  • Thanks. I was afraid of that. I imagine the rollback it's doing won't increase file or log sizes? I'm using NDF files and used WITH (TABLOCK) in the original inserts that are being rolled back. It went from 61% to 63% in 7 hours...slowly making progress! – Andy DesRosiers Dec 26 '15 at 8:32
  • 1
    Santa likes you ;) – RayofCommand Dec 26 '15 at 16:07
6

@MaxVernon already gave all the info you need. Just some addition from my side. The bigger the transaction the harder the (pre)calculation for the SQL Server. That's why your remaining time in seconds may increase while the process runs. After running a query the SQL Server can't grant more memory to the process. For big calculations your tempdb will most probably be used. And yes this will lead to a disk intensive call. That's why it's nearly always best to keep the transactions small.

Also please note that restarting SQL Server is in most cases a bad idea, since it has multiple negative side effects. It will for example rebuild the TEMP DB and also clear statistics... I

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.