Transaction rollback hanging from past two months(MSSQL 2016)
-
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(2,216,539,357 rows), then my MSSQL started rollback of transaction at2019-02-01. I usedKILL SPID WITH STATUSONLYto monitor the rollback process: progress of rollback was about 2% a day for first two weeks, afterwards the rollback process got stuck at 22% from2019-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 ErrorMessageActivity monitor
-
Hi,
I think you can't stop a rollback. If you restart your server, the sql server will continue the rollback.
Maybe you can try to detach and atach the database without the ldf file, but this can cause dataloss!
Be sure you have a good backup of your database.
Kind regards,
Andreas - andreas.kreuzberg - Wednesday, March 20, 2019 12:59 AM
Hi Andreas:
I already backup data and try to detach the database, but the detach process elapsed 24 hour and can't finish the detach process. I guess detach should wait until the rollback complete.If there is some 'forced detach' command/process please let me know.
Sincerely
-
Hi,
try to shut down your sql server. And remove the ldf & mdf file.
Start your sql server.
And now try to attach the mdf file without the ldf file.Good luck
Andreas
- andreas.kreuzberg - Wednesday, March 20, 2019 1:25 AM
Hi Andreas:
Thanks for your advise, but I can't stop the SQL server because there are other production databases working around the day.Sincerely
-
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply