过去两个月暂停交易回滚（MSSQL 2016） [英] Transaction rollback hanging from past two months(MSSQL 2016)

问题描述

我使用  SQLCMD   on  2019-01-30 ，
和查询导致  ldf  文件以指数方式膨胀，大小变为2TB （2,216,539,357
行），然后我的MSSQL开始回滚交易  2019-02-01 。
我用过  KILL
SPID WITH STATUSONLY  来监控回滚过程：回滚的进度大约是 2％
每天 前两周，之后回滚过程停留在22％的 2019-02-14 ，
，它今天仍在那里。

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 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

$

推荐答案

实际上我会让它去完成回滚。你试图插入多少数据？我看到你插入27次但不知道有多少行？你能发行吗？该表上的SELECT / UPDATE / DELETE？

Actually I would let it go and finish the rollback. How much data did you try to insert? I see you insert 27 times but have no idea how much rows? Can you issue SELECT/UPDATE/DELETEs on that table?

这篇关于过去两个月暂停交易回滚（MSSQL 2016）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！