sql - 从 SQL Server 中的大量表中删除时如何减少事务日志的使用？

Question

我有这个功能，可以从超过 1000 万行的大表中删除不再需要的历史数据

DELETE FROM BigTable
WHERE DATEDIFF(month,dtmtimestamp, getdate()) > 2)

然后我尝试了这种方法来减少事务日志使用的空间量:

WHILE (SELECT COUNT(*) FROM BigTable WHERE DATEDIFF(month,dtmtimestamp, getdate()) > 2) > 0 BEGIN
    DELETE TOP 10000 FROM BigTable
    FROM BigTable
    WHERE DateDiff(month,dtmtimestamp, getdate()) > 2
CONTINUE END 

这是正确的方法吗？还是我会用这种方式用完更多的事务日志？

有什么好的方法吗？

干杯，
妮可

最终答案:

DECLARE @Remainder INT
DECLARE @ChunkSize INT
SET @Remainder = (SELECT COUNT(id) FROM BigTable WHERE dtmtimestamp < DateAdd(month, -2, getdate()))
SET @ChunkSize = CEILING(@Remainder/100) /* Divide the total into 100 parts, whole integers only */
WHILE @Remainder BEGIN
    BEGIN TRANSACTION deletehistorical
    DELETE TOP (@ChunkSize)
    FROM BigTable
    WHERE dtmtimestamp < DateAdd(month, -2, getdate());
    SET @Remainder = @@ROWCOUNT;
    COMMIT TRANSACTION deletehistorical
END

CHECKPOINT 命令只告诉引擎从日志中删除已完成的事务(在简单恢复模式下)，并且由于此查询实际上仍在继续每个循环，事务仍在创建中。因此，为了分解事务，我添加了 BEGIN 和 COMMIT 以强制数据库每次都进行这些更改。

Answer 1

与其计算仍要删除的行数，不如使用 EXISTS(这样一找到行就返回):

WHILE EXISTS(SELECT * FROM BigTable WHERE DATEDIFF(month,dtmtimestamp, getdate()) > 2) BEGIN

或者偷偷摸摸的:

select top 1 * from sysobjects /* Force @@ROWCOUNT > 0 */
WHILE @@ROWCOUNT BEGIN
    DELETE TOP 10000 FROM BigTable
    FROM BigTable
    WHERE dtmtimestamp< DateAdd(month, -2,getdate())
CONTINUE END

表的唯一搜索是用于执行实际删除的搜索。

我还移动了您的日期逻辑，以防 dtmtimestamp 列有有用的索引。

编辑当然，正如 Martin 指出的那样，这些都没有解决事务日志的使用问题。

限制删除的策略是阻止可怕的日志使用的合理策略，但还需要同时进行大量日志备份或截断，以允许旧的事务日志空间被重用。否则，它仍然会增长日志。

如果您知道日志备份正在发生，比方说，每十五分钟发生一次，您可能希望每“n”次迭代暂停您的循环，并使用 WAITFOR DELAY，以便您知道之前的事务日志使用情况已被备份/清除。无论发生什么情况，只要您是删除而不是截断，每个已删除行的日志记录仍会占用日志或日志备份中的空间。

如果您能够将通常使用该系统的任何东西脱机，并且您想要保留的行的数量与要删除的行的数量相比相形见绌，您可能想要将要保留的行复制到另一个表中，删除所有外键，截断表，将保留的行复制回来，并重建外键。 YMMV.