问题描述
我正在运行以下存储过程来删除大量记录.我知道 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