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
wait_type: PAGEIOLATCH_EX
Elapsed Time (in Sec): 19750.420000
open_transaction_count: 2
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.
