-1

I have a very large table that needed a schema change to one column - I needed to expand a datatype from varchar(50) to varchar(100). I expected this to happen quickly since the column datatype is already variable and obviously no data currently in that column would have to change. But after the command ran for several minutes I decided to back away and take another look. Now, after killing the SPID, rollback has been happening for over an hour (much longer than the command had been running). When I run 'kill' with statusonly it says:

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

sp_WhoIsActive shows the wait as IO_Completion.

Why would a rollback on ALTER TABLE foo ALTER COLUMN bar varchar(100) NULL get stuck like this? Column bar is varchar(50) now.

Update: rollback finished after about 3 hours. I would still like to know why this took so long to rollback.

Hannah Vernon
  • 70,928
  • 22
  • 177
  • 323
Kevin Olree
  • 103
  • 1
  • 5

1 Answers1

1

These links should be helpful in explaining what happens during a rollback:

  1. Rollback: What happens when you KILL a session?

  2. Rollback taking forever

One key thing to takeaway from these is that rollbacks are typically single-threaded, which generally contributes to why they can take longer than the query itself was originally running for before the rollback was issued.

J.D.
  • 40,776
  • 12
  • 62
  • 141