对数百万行的更新查询将填充事务日志 [英] Update query on millions of rows fills the transaction log

问题描述

作为下一个版本的一部分，我需要更新数百万行，但是这样做会填满事务日志，但会失败.我有一些想法，但我不是SQL专家，所以我敢肯定会有一些我不知道的陷阱.

I need to update millions of rows as part of my next release, but doing so fills the transaction log and fails. I have a few ideas but I'm not a SQL expert so I'm sure there will be gotchas that I'm not aware of.

相关点:

1. 我需要将脚本移交给运营团队，因此需要一种无需人工干预的T-SQL方法.
2. 显然，事务日志每15分钟被回收一次. (我曾考虑过要在try捕获块中使用WAITFOR DELAY '00:15:00'编写带有try-catch的循环，如下所示)
3. (编辑)，除了数据我什么也不能修改.
4. (编辑).这是一个简单的更新，将外键列更改为其他现有键.

1. I need to hand a script over to the operations team so need a T-SQL method with no manual intervention.
2. Apparently the transaction log gets recycled every 15 minutes. (I've thought about writing a loop with a try-catch with WAITFOR DELAY '00:15:00' in the catch block like below)
3. (EDIT) I can't modify anything except the data.
4. (EDIT) It's a simple update changing a foreign key column to a different existing key.

谢谢

菲尔

DECLARE
    @AffectedRows int

SET @AffectedRows = 0

WHILE @AffectedRows < @RowsToUpdate
BEGIN
    BEGIN TRY
        BEGIN TRAN
        -- Do some updates  
        SET @AffectedRows = @AffectedRows + @@RowCount
        COMMIT TRAN
    END TRY
    BEGIN CATCH
        PRINT ERROR_MESSAGE()
        WAITFOR DELAY '00:15:00'
    END CATCH
END

PRINT @AffectedRows

推荐答案

最后，我已经编写的示例效果最好.事务日志已满错误被捕获，并且15分钟的时间足以使日志被回收.

In the end the example I had already written worked best; a transaction log full error gets caught in the catch and 15 minutes is long enough for the log to be recycled.

DECLARE 
    @AffectedRows int 

SET @AffectedRows = 0 

WHILE @AffectedRows < @RowsToUpdate 
BEGIN 
    BEGIN TRY 
        BEGIN TRAN 
        -- Do some updates   
        SET @AffectedRows = @AffectedRows + @@RowCount 
        COMMIT TRAN 
    END TRY 
    BEGIN CATCH 
        PRINT ERROR_MESSAGE() 
        WAITFOR DELAY '00:15:00' 
    END CATCH 
END 

PRINT @AffectedRows

这篇关于对数百万行的更新查询将填充事务日志的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！