SQL Server – Delete Statement Not Responding to Kill Session Command

deletekillrollbacksql server

Four days ago a user ran the command below on a table with 400,000,000 rows. It's still running and the log file is increasing in size.

delete from [table-name]

This table does have a foreign key constraint which is not enabled for checking and I know that no rows exist in the other table.

The database is running with "Is Read Committed Snapshot On" enabled and in Simple recovery mode.

After this had run for a few hours I issued a kill session command, because we were running out of disk space for the log file. I added another log file to allow the system to continue to function.

The log file is continuing to grow and when I run kill session with statusonly it returns this message:

SPID 123: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.

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?

Best Answer

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.

Related Question