Delete statement not responding to Kill session command

I am at a loss as to what to do about this query to get it to rollback and also just understanding what is going on, can anyone suggest what I can look at?

DELETE FROM [Some400MRowTable]

is expensive. Every row you delete gets logged. And when you killed the session that huge transaction has to rollback, which is even more expensive. So normally you just wait, and eventually it rolls back. Your alternative is to restore from a backup.

Note that this is one of the reasons Accelerated database recovery was added in Azure SQL Database and SQL Server 2019, which provides "Instantaneous transaction rollback" where rollback cost is not a function of the number of changes made by a transaction.


Rollbacks are single threaded, so it will take much longer, although 4 days seems long, we have no idea how long the original delete would have taken. From Jes Schultz Borland (link):

If the transaction had to take a lock on a row or table to perform the operation, it will have to take that lock again – and other processes might be using that object now. Also, consider that a rollback is mostly single-threaded. If the transaction ran using four cores initially, and the rollback is now only using one, it will take more time.

Imagine this: you’ve decided to climb a tower with 10,000 stairs. You get to stair 9,999 and decide you don’t want to complete the climb. You can’t wish yourself to the bottom step – you have to go back down. But now, you’re tired – and have to do this single-threaded. Imagine having to hop down the stairs backwards, on one foot.

As per BradC's answer, if you restart SQL Server, it will continue the rollback as it's reading the transaction log. Depending on your backup/recovery plans, restoring from backup could be your best option.


So the delete ran for only "a few hours" before you killed it, and now the "rollback" has been running for 4 days?

That's well past the time I would normally expect, so here's what I would recommend:

  1. Get management approval
  2. Restart the SQL instance.
  3. Cross your fingers.
  4. If you're lucky, the hung rollback will clear right away, the database will recover, and everyone will be happy.
  5. If you're not lucky, then the rollback will continue while the database is being recovered, and the database will never come back online. Check the SQL errorlog for status to see if this is occurring.
  6. In that case, stop the SQL instance again, then delete the database files from disk while the instance is stopped.
  7. Start SQL again, the instance will fail to find the database files.
  8. Drop the (ghost) database
  9. Restore the database from backup.

If the restart fixes it, you're good. If it doesn't, you're no worse off then simply doing a restore from backup.

Good luck.