2

I executed a query using SQLCMD on 2019-01-30, and the query caused ldf file to bloat exponentially and it became 2TB in size, then my MSSQL started rollback of transaction at 2019-02-01. I used KILL SPID WITH STATUSONLY to monitor the rollback process: progress of rollback was about 2% a day for first two weeks, afterwards the rollback process got stuck at 22% from 2019-02-14, and it's still there today.

I'd appreciate if I can get expert opinion on if there is any way to fix this issue - how to stop or speed up rollback?

Please find below code details about this issue:


SQL

BEGIN TRAN
  DECLARE @m int
  SELECT @m = @@ERROR
  DECLARE @tbname_old varchar(50) = 'OTS_ARCHIVE'
  DECLARE @tbname_new varchar(50) = 'OTS_ARCHIVE2'
  DECLARE @column_old varchar(30) = 'GuID_ID'
  DECLARE @column_new varchar(30) = 'GuID_ID_old'
  DECLARE @sql varchar(50) = '[' + @tbname_new + '].[' + @column_old + ']'
  DECLARE @sqlid varchar(100) = 'CAST(CAST(NEWID() AS BINARY(10)) + CAST(GETDATE() AS BINARY(6)) AS UNIQUEIDENTIFIER)'
  DECLARE @date as datetime
  DECLARE @i int
  DECLARE @f int
  set @date = '2017-01-01'
  set @i = 0
  set @f = 27
  WHILE @i < @f
  BEGIN
  EXEC ('INSERT INTO ' + @tbname_new +
    ' select
    GuID_ID
    ,Box_ID
    ,Start_Time
    ,End_Time
    ,Duration_Time
    ,ots_count
    ,Group_ID
    ,' + @sqlid + ' from ' + @tbname_old
    )
  END
IF @m = 0
  COMMIT TRAN
ELSE
  ROLLBACK TRAN
  SELECT 
    ERROR_NUMBER() AS ErrorNumber,
    ERROR_SEVERITY() AS ErrorSeverity,
    ERROR_STATE() AS ErrorState,
    ERROR_PROCEDURE() AS ErrorProcedure,
    ERROR_LINE() AS ErrorLine,
    ERROR_MESSAGE() AS ErrorMessage

Activity monitor

enter image description here

2
  • @SabinBio I ran the query and don't get any wait type shows ASYNC_IO_COMPLETION. Thanks.
    – swchen
    Mar 19, 2019 at 8:10
  • 1
    What waits do you have for that session ? sys.dm_exec_session_wait_stats ; also a good helper is sp_WhoIsActive
    – Sabin B
    Mar 19, 2019 at 8:41

1 Answer 1

1

After about 60 days, the rollback process was finally completed. It seems the only way and the only solution you should do is wait, wait, and keep waiting.

2
  • Can you provide more detail about what the transaction did? What kind of disk subsystem do you have? How much memory? It's amazing that it took 60 days for rollback to complete!
    – Hannah Vernon
    Apr 3, 2019 at 13:40
  • How many Virtual Log Files does the database have?
    – Hannah Vernon
    Apr 3, 2019 at 13:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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