如何删除 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 1686855073 < @d;
IF (0 = @@ROWCOUNT)
BREAK
END
END
- 使时间戳比较 SARGable
- 在批处理开始时分离 GETDATE() 以产生一致的运行(否则它会在无限循环中阻塞,因为新记录随着旧记录被删除而老化").
- 使用 TOP 而不是 SET ROWCOUNT(已弃用:
使用 SET ROWCOUNT 不会影响下一版本 SQL Server 中的 DELETE、INSERT 和 UPDATE 语句.
) - 检查@@ROWCOUNT 以打破循环而不是多余的 SELECT
相关文章