sql-server - 如何在 SQL 2005 中删除记录并检查事务日志

Question

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

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

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

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

Answer 1

这是我的做法:

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 [Timestamp] < @d;
        IF (0 = @@ROWCOUNT)
            BREAK
    END
END

• 进行 Tiemstamp 比较 SARGable
• 在批处理开始时分隔 GETDATE() 以产生一致的运行(否则它可能会在无限循环中阻塞，因为新记录“老化”，而旧记录被删除)。
• 使用 TOP 代替 SET ROWCOUNT( deprecated :使用 SET ROWCOUNT 不会影响下一版本 SQL Server 中的 DELETE、INSERT 和 UPDATE 语句。)
• 检查@@ROWCOUNT来打破循环而不是多余的SELECT