8

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?

Paul White
  • 94,921
  • 30
  • 437
  • 687
David Woakes
  • 183
  • 1
  • 4

4 Answers4

9

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.

David Browne - Microsoft
  • 49,000
  • 3
  • 53
  • 102
7

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.

Josh Darnell
  • 30,133
  • 5
  • 70
  • 124
Amar L
  • 111
  • 4
4

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.

BradC
  • 10,073
  • 9
  • 51
  • 89
0

Have you thought about using TRUNCATE TABLE instead of DELETE? If you are trying remove all rows in the table with that many rows TRUNCATE TABLE might be a better choice. It runs faster that DELETE does. However, I also understand that if you use it, you might not be able to rollback if needed. Others who are smarter than I am can correct me if I am wrong about the rollback when using TRUNCATE TABLE.

user42059
  • 1
  • 2