We have a transaction that has been running for more than 5 hours. We are running out of disk space. The session has been killed but it is still waiting for IO_COMPLETION. Actually the wait_type just changed to PAGEIOLATCH_EX. How can I terminate the suspended SQL Server transaction? I am not worried about losing data since all of it can be repopulated.

session_id: 54
STATUS: suspended
blocked by: 0
Elapsed Time (in Sec): 19750.420000
open_transaction_count: 2
  • 3
    Let it finish .. else if you do a knee jerk thing of pulling out power supply or restarting sql server service, it will take time to rollback. Since you are not worried about loosing data, how about you restore the database from last backup and then repopulate the data ? Additionally, you can use KILL 54 WITH STATUSONLY to find out how long it is going to take to rollback. – Kin Shah Jun 3 '15 at 16:26
  • I ran KILL 54 WITH STATUSONLY and received this: SPID 54: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds. I don't believe the 0 seconds part. – Tarzan Jun 3 '15 at 16:31
  • 2
    Rollback is a single threaded operation, so for example if your original transaction was running with 8 threads parallel until killed, the rollback can take 8 times longer. – James Z Jun 3 '15 at 16:33
  • @Tarzan KILL .. WITH STATUSONLY is not accurate and I see your point. Can you try Alter database .. set OFFLINE or single_User WITH ROLLBACK IMMEDIATE ? – Kin Shah Jun 3 '15 at 16:38
  • 2
    I ended up letting it run overnight. It finally finished. Woohoo! Thank you for commenting. – Tarzan Jun 4 '15 at 15:23

Next time this happens, run sp_WhoIsActive (download / documentation) and see who is running what and look over the logic. Check to see if the TSQL can be optimized to run quicker or perhaps chop it up into smaller transactions.

I've had cases where the transaction log of a bad query by a report writers, data loaders, etc. would grow the transaction log larger than the data file size, and it's usually a bad performing, poorly written query that's not optimized or chunked into smaller transactions to give back free space once the transaction completes - this was on a SIMPLE recovery model database too by the way - FULL recovery model databases would need to have transaction log backups completed to enable reuse of transaction log space of committed transactions.

The root problem is likely the query so determining who is doing what and reaching out to them and reporting the issue with your findings would put the pressure on them to fix their logic to not hose up the server disk space for that disk partition - hopefully it's not your logic but if it is, look at optimizing the logic to query tune for performance.

| improve this answer | |

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.