7

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?

  • 3
    To avoid this scenario next time, tell the users how to do deletes in batches. – vonPryz Apr 6 at 14:19
  • Haha! My line manager did this and I've told him off. He should totally have known better! – David Woakes Apr 6 at 15:31
  • 1
    Could it be that some other open transaction is blocking or delaying the rollback? I'm not sure if it is possible, just a guess. – Ronaldo Apr 6 at 16:26
  • 1
    Great video about this from @brent-ozar, see youtube.com/watch?v=czhRBHaMWAs – David Woakes Apr 7 at 10:55
  • I also might suggest, from a RCA perspective, asking if it's necessary that your active table retain an astronomical 400,000,000 entries. I see a "best practices" process of procedural archiving mitigated the issue. This is far better to bring to an "uh oh" management meeting - since this will not be the last time someone "cuts themselves" and nearly bleeds to death. That being said, there are different needs for different clients and this may not always apply (I'm willing to bet for 99% it does). – Elysiumplain Apr 7 at 17:53
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.

| improve this answer | |
  • Thanks, I suspected that sitting tight was the answer. I think that read committed snapshot means it's copying the db. – David Woakes Apr 6 at 15:33
  • 5
    It took 14 days. – David Woakes Apr 17 at 7:22
6

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.

| improve this answer | |
  • Right. The restart gives it the chance to rollback without interference from other activity, so might be worth doing the restart and giving it some time before giving up and doing the restore. – BradC Apr 6 at 19:07
  • 1
    "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" - is this statement correct? SQL Server keeps write locks until the end of the transaction. – Nikita Apr 7 at 22:14
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.

| improve this answer | |
  • That's my worst nightmare and I'm fighting the urge to restart in case it's worse. Will check the logs to see if there is anything. – David Woakes Apr 6 at 15:34
  • @DavidWoakes Yep. The key is that you should already get management sign-off on a database restore before trying the restart. Then its either "as expected, I had to restore the db", or "good news! I didn't have to do a restore!". You definitely don't want the conversation to be: "my last ditch restart didn't work, and now the application is offline entirely". A trickier question is which backup to restore to: the last one before the delete started (4 days old)? Or risk more downtime by restoring a more recent one only to hit the same issue when it tries to come online. – BradC Apr 6 at 16:00
  • That may come down to how big the db is, and how long a full restore takes (and how often your full backups are taken). – BradC Apr 6 at 16:01
  • 1
    It took 14 days, so being patient can be worth while. – David Woakes Apr 17 at 6:41
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.

| improve this answer | |
  • Ha ha. My manager started it and he should have known better. We are now 6 days in and from sp_who2 I can see it’s busy doing something so sitting tight to see what’s happening. – David Woakes Apr 8 at 20:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.