1

I have a problem with a Rollback of a ALTER TABLE ALTER COLUMN of a table that has more than 2 billion lines. It is taking more than one day and after trying to KILL the SPID nothing changes...I know that it could take long to rollback but this is too much...

Also I run sp_lock and it show as below. Any ideas to help here? Many thanks!!!

sp_lock result image

jarlh
  • 42,561
  • 8
  • 45
  • 63
Doug Valverde
  • 11
  • 2
  • Killing the spid is still going to wait for the rollback. If you kill the entire database process or reboot the server, when you restart it, it will still need to do the rollback. If you are happy to restore your last backup that is probably your fastest fix, otherwise you just have to wait. – Ben Mar 04 '19 at 15:42
  • To improve the speed, make sure the sever isn't doing anything else which will use memory or disk IO, and make sure the server hasn't got a low memory quota. – Ben Mar 04 '19 at 15:43
  • I.e. disable virus scanners, mail servers, indexing service and so on. Use Task Manager PF Delta and IO columns to find high disk IO. Use "Commit Charge" to find high memory usage. – Ben Mar 04 '19 at 15:44
  • 1
    `select percent_complete from sys.dm_exec_requests where session_id = YourKilledSPID` to get some estimation for the task – Denis Rubashkin Mar 04 '19 at 15:52

0 Answers0