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
Best Answer
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.