如何删除 SQL 2005 中的记录以检查事务日志

问题描述

我正在运行以下存储过程来删除大量记录.我知道 DELETE 语句写入事务日志，删除多行会使日志增长.

I am running the following stored procedure to delete large number of records. I understand that the DELETE statement writes to the transaction log and deleting many rows will make the log grow.

我已经研究了创建表和插入记录以保留然后截断源的其他选项，这种方法对我不起作用.

I have looked into other options of creating tables and inserting records to keep and then Truncating the source, this method will not work for me.

如何使下面的存储过程更高效，同时确保事务日志不会出现不必要的增长?

CREATE PROCEDURE [dbo].[ClearLog] 
(
  @Age int = 30
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

  -- DELETE ERRORLOG
  WHILE EXISTS ( SELECT [LogId]  FROM [dbo].[Error_Log] WHERE DATEDIFF( dd, [TimeStamp], GETDATE() ) > @Age )
   BEGIN
    SET ROWCOUNT 10000
    DELETE [dbo].[Error_Log] WHERE DATEDIFF( dd, [TimeStamp], GETDATE() ) > @Age

    WAITFOR DELAY '00:00:01'
    SET ROWCOUNT 0
   END
END

推荐答案

我会这样做:

CREATE PROCEDURE [dbo].[ClearLog] (  
@Age int = 30)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @d DATETIME
        , @batch INT;
    SET @batch = 10000;
    SET @d = DATEADD( dd, -@Age, GETDATE() )
    WHILE (1=1)
    BEGIN
        DELETE TOP (@batch) [dbo].[Error_Log]  
        WHERE 1670295504 < @d;
        IF (0 = @@ROWCOUNT)
            BREAK
    END
END

• 使时间**较 SARGable
• 在批处理开始时分离 GETDATE() 以产生一致的运行(否则它会在无限循环中阻塞，因为新记录随着旧记录被删除而老化").
• 使用 TOP 而不是 SET ROWCOUNT(已弃用:使用 SET ROWCOUNT 不会影响下一版本 SQL Server 中的 DELETE、INSERT 和 UPDATE 语句.)
• 检查@@ROWCOUNT 以打破循环而不是多余的 SELECT