数百万行的更新查询填满事务日志

发布于 2024-09-06 09:42:03 字数 788 浏览 5 评论 0原文

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

相关要点：

我需要将脚本交给运营团队，因此需要一种无需手动干预的 T-SQL 方法。
显然，事务日志每 15 分钟就会回收一次。（我考虑过在 catch 块中使用 WAITFOR DELAY '00:15:00' 编写一个带有 try-catch 的循环，如下所示）
(编辑) 我可以除了数据之外不要修改任何内容。
（编辑）这是一个简单的更新，将外键列更改为不同的现有键。

谢谢，

菲尔

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

原文

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.

Pertinent points:

I need to hand a script over to the operations team so need a T-SQL method with no manual intervention.
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)
(EDIT) I can't modify anything except the data.
(EDIT) It's a simple update changing a foreign key column to a different existing key.

Thanks,

Phil

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

梦明 2024-09-13 09:42:03

最后，我已经写的例子效果最好； catch 中捕获事务日志已满错误，并且 15 分钟足以回收日志。

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

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

回复收藏 0 原文