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.