如何删除SQL 2005中保留事务日志的记录

问题描述：

我正在运行以下存储过程以删除大量记录。我知道DELETE语句写入事务日志并删除很多行会使日志增长。如何删除SQL 2005中保留事务日志的记录

我已经研究了创建表格和插入记录以保留然后截断源的其他选项，但此方法对我无效。

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

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

“我已经研究了创建表和插入记录以保留然后截断源的其他选项，但此方法对我无效。”怎么来的？对此的答案影响可能的解决方案。 – 2009-09-09 19:32:50

如果我截断原始表，会不会有某种超时？这不会在试图写入日志表的应用程序中造成问题吗？这是我的担忧。 – Picflight 2009-09-09 19:35:47

什么是数据库恢复模式设置为？（Full，Bulk_Logged，Simple） – 2009-09-09 20:05:44

答

这里是我会怎么做：在批次开始

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

充分利用Tiemstamp比较优化搜索
独立的GETDATE（）产生一个一致的运行（否则它可以随着新纪录的“年龄”被删除而在无限循环中阻止）。
使用TOP，而不是SET ROWCOUNT（deprecated：Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server.）
检查@@ ROWCOUNT打破循环，而不是冗余选择

我刚刚测试了900万行，每次删除1000个。它工作得很好。 – 2009-11-10 21:31:56

对于不熟悉术语SARGable的人员 - 时间戳字段应该编入索引。另外，这种方法可能只适用于简单恢复模式，在每次删除后事务日志都清空。 – 2010-01-14 09:43:33

答

假设你有重建的分区方案错误日志表的选项一种选择是在日期分区表并交换分区。做一个谷歌搜索'改变表开关分区'进一步挖掘。

如果它符合你的架构，这将会成功。 – 2009-09-09 20:08:19

答

如何更频繁地运行它，并且每次删除更少的行？运行此每30分钟：

CREATE PROCEDURE [dbo].[ClearLog] 
(
    @Age int = 30 
) 
AS 
BEGIN 
    SET NOCOUNT ON; 
    SET ROWCOUNT 10000 --I assume you are on an old version of SQL Server and can't use TOP 
    DELETE dbo.Error_Log Where Timestamp>GETDATE()[email protected] 
    WAITFOR DELAY '00:00:01' --why??? 
    SET ROWCOUNT 0 
END

它处理的日期将不会截断时间的方式，你只会删除30分钟值得每个数据的时间。

另一个好的诀窍 - 当删除发生时你能隔出来，所以最小化它们的效果吗？ – 2009-09-09 20:09:00

@Philip凯利，这是“每30分钟”的想法，但仍然限制到10000，这将分摊负载与其他“真正的”用户交易 – 2009-09-09 20:11:34

啊。那会让程序“一直在运行”，不是吗？我建议将定时移出到SQL代理作业，每30分钟调用一次过程来删除行。 – 2009-09-10 03:29:52

答

我已经在过去使用的解决方案是临时设置为“大容量日志记录”恢复模式，然后再返回到“全”，在存储过程的结束：

DECLARE @dbName NVARCHAR(128); 
SELECT @dbName = DB_NAME(); 

EXEC('ALTER DATABASE ' + @dbName + ' SET RECOVERY BULK_LOGGED') 

WHILE EXISTS (...) 
BEGIN 
    -- Delete a batch of rows, then WAITFOR here 
END 

EXEC('ALTER DATABASE ' + @dbName + ' SET RECOVERY FULL')

这将显著减少大批量的事务日志消耗。我不喜欢它为整个数据库设置恢复模式（不仅仅是为了这个会话），但它是我能找到的最佳解决方案。

答

如果您的数据库处于FULL恢复模式，那么将您的删除语句的影响降至最低的唯一方法是“将它们空出来” - 仅在“事务间隔”期间删除那么多。例如，如果您每小时进行一次t-log备份，则每小时只能删除20,000行。这可能不会一下子全部消除，但是在24小时之后或一周之后，情况会不会持平？

如果您的数据库处于SIMPLE或BULK_LOGGED模式，则应将其删除为块。但是，由于您已经这么做了，我不得不猜测您的数据库处于完全恢复模式。（即，或者调用该过程的连接可能是交易的一部分）。

如何删除SQL 2005中保留事务日志的记录

相关推荐