sql-server - 如何清除 SQL Server 事务日志？

Question

我不是 SQL 专家，每次我需要做一些超出基础的事情时，我都会想起这个事实。我有一个规模不大的测试数据库，但事务日志肯定是。如何清除事务日志？

Answer 1

缩小日志文件确实应该保留用于遇到意外增长而您不希望再次发生的情况。如果日志文件将再次增长到相同的大小，则暂时缩小它并不能完成很多工作。现在，根据数据库的恢复目标，这些是您应该采取的操作。

首先，进行完整备份

永远不要对您的数据库进行任何更改，除非确保您可以在出现问题时将其恢复。

如果您关心时间点恢复

(通过时间点恢复，我的意思是您关心能够恢复到完整备份或差异备份以外的任何内容。)

大概你的数据库在 FULL恢复模式。如果不是，请确保它是:

ALTER DATABASE testdb SET RECOVERY FULL;

即使您正在定期进行完整备份，日志文件也会不断增长，直到您执行日志备份 - 这是为了您的保护，而不是不必要地消耗您的磁盘空间。根据您的恢复目标，您应该经常执行这些日志备份。例如，如果您有一个业务规则，规定在发生灾难时您可以承受的数据丢失时间不超过 15 分钟，那么您应该有一个每 15 分钟备份一次日志的作业。这是一个脚本，它将根据当前时间生成带时间戳的文件名(但您也可以使用维护计划等来执行此操作，只是不要在维护计划中选择任何收缩选项，它们很糟糕)。

DECLARE @path NVARCHAR(255) = N'\\backup_share\log\testdb_' 
  + CONVERT(CHAR(8), GETDATE(), 112) + '_'
  + REPLACE(CONVERT(CHAR(8), GETDATE(), 108),':','')
  + '.trn';

BACKUP LOG foo TO DISK = @path WITH INIT, COMPRESSION;

请注意 \\backup_share\应该在代表不同底层存储设备的不同机器上。将这些备份到同一台机器(或使用相同底层磁盘的不同机器，或同一物理主机上的不同虚拟机)并没有真正帮助你，因为如果机器爆炸，你已经丢失了你的数据库及其备份。根据您的网络基础设施，在本地备份然后将它们转移到幕后的不同位置可能更有意义；无论哪种情况，您都希望尽快将它们从主数据库机器上删除。

现在，一旦您运行了常规的日志备份，就应该合理地将日志文件缩小到比它现在被炸毁的更合理的地方。这并不意味着运行 SHRINKFILE一遍又一遍，直到日志文件为 1 MB - 即使您经常备份日志，它仍然需要容纳可能发生的任何并发事务的总和。日志文件自动增长事件代价高昂，因为 SQL Server 必须将文件清零(与启用即时文件初始化时的数据文件不同)，并且用户事务必须在发生这种情况时等待。您希望尽可能少地执行这种增长-收缩-增长-收缩的例程，并且您当然不想让您的用户为此付出代价。

请注意，在可能进行收缩之前，您可能需要备份日志两次(感谢 Robert)。

因此，您需要为日志文件提供一个实用的大小。这里没有人可以在不了解更多有关您的系统的情况下告诉您这是什么，但是如果您经常缩小日志文件并且它再次增长，那么好的水印可能比最大的水印高 10-50% .假设达到 200 MB，并且您希望任何后续自动增长事件为 50 MB，那么您可以通过以下方式调整日志文件大小:

USE [master];
GO
ALTER DATABASE Test1 
  MODIFY FILE
  (NAME = yourdb_log, SIZE = 200MB, FILEGROWTH = 50MB);
GO

请注意，如果日志文件当前 > 200 MB，您可能需要先运行:

USE yourdb;
GO
DBCC SHRINKFILE(yourdb_log, 200);
GO

如果您不关心时间点恢复

如果这是一个测试数据库，并且您不关心时间点恢复，那么您应该确保您的数据库在 SIMPLE 中。恢复模式。

ALTER DATABASE testdb SET RECOVERY SIMPLE;

将数据库放入 SIMPLE恢复模式将确保 SQL Server 重用部分日志文件(基本上逐步淘汰不事件的事务)，而不是增长以保留所有事务的记录(如 FULL 恢复直到您备份日志)。 CHECKPOINT事件将有助于控制日志并确保它不需要增长，除非您在 CHECKPOINT 之间生成大量 t-log 事件。 s。

接下来，您应该绝对确保这种日志增长确实是由于异常事件(例如，年度 Spring 大扫除或重建您最大的索引)，而不是由于正常的日常使用。如果您将日志文件缩小到小得离谱，而 SQL Server 只需要再次增大它以适应您的正常事件，您得到了什么？您是否能够使用您只是暂时释放的磁盘空间？如果您需要立即修复，则可以运行以下命令:

USE yourdb;
GO
CHECKPOINT;
GO
CHECKPOINT; -- run twice to ensure file wrap-around
GO
DBCC SHRINKFILE(yourdb_log, 200); -- unit is set in MBs
GO

否则，设置适当的大小和增长率。根据时间点恢复案例中的示例，您可以使用相同的代码和逻辑来确定合适的文件大小并设置合理的自动增长参数。

有些事情你不想做

使用 TRUNCATE_ONLY 备份日志选项，然后 SHRINKFILE .一方面，这个 TRUNCATE_ONLY选项已被弃用，并且在当前版本的 SQL Server 中不再可用。二、如果你在FULL恢复模式，这将破坏您的日志链并需要一个新的、完整的备份。

分离数据库，删除日志文件，重新附加 .我无法强调这有多危险。您的数据库可能无法备份，可能会被怀疑，您可能必须恢复备份(如果有)等等。

使用“收缩数据库”选项 . DBCC SHRINKDATABASE和维护计划选项做同样的事情是坏主意，特别是如果你真的只需要解决日志问题。使用DBCC SHRINKFILE定位要调整的文件并独立调整它或 ALTER DATABASE ... MODIFY FILE (上面的例子)。

将日志文件缩小到 1 MB .这看起来很诱人，因为，嘿，SQL Server 会让我在某些情况下这样做，看看它释放的所有空间!除非您的数据库是只读的(确实是这样，您应该使用 ALTER DATABASE 将其标记为只读)，否则这绝对会导致许多不必要的增长事件，因为无论恢复模式如何，日志都必须容纳当前事务。暂时释放该空间有什么意义，只是为了让 SQL Server 可以缓慢而痛苦地收回它？

创建第二个日志文件 .这将为已填满磁盘的驱动器提供暂时的缓解，但这就像试图用创可贴修复被刺破的肺。您应该直接处理有问题的日志文件，而不是仅仅添加另一个潜在问题。除了将某些事务日志事件重定向到不同的驱动器之外，第二个日志文件实际上对您没有任何作用(与第二个数据文件不同)，因为一次只能使用其中一个文件。 Paul Randal also explains why multiple log files can bite you later .

主动

与其将日志文件缩小到很小的数量，并让它自己以很小的速率不断自动增长，不如将其设置为一个相当大的大小(将容纳最大的并发事务集的总和)并设置一个合理的自动增长设置为后备，这样它就不必多次增长来满足单个事务，因此在正常业务运营期间它必须增长的情况相对较少。

此处最糟糕的设置是 1 MB 增长或 10% 增长。有趣的是，这些是 SQL Server 的默认值(我曾提示过和 asked for changes to no avail) - 1 MB 用于数据文件，10% 用于日志文件。前者在当今时代太小了，而后者每次都会导致越来越长的事件(例如，您的日志文件是 500 MB，第一次增长是 50 MB，下一次增长是 55 MB，下一次增长是 60.5 MB等 - 以及在慢速 I/O 上，相信我，您会真正注意到这条曲线)。

进一步阅读

请不要停在这里；虽然您在那里看到的许多关于缩小日志文件的建议本质上都是糟糕的，甚至可能是灾难性的，但有些人更关心数据完整性而不是释放磁盘空间。

A blog post I wrote in 2009, when I saw a few "here's how to shrink the log file" posts spring up .

A blog post Brent Ozar wrote four years ago, pointing to multiple resources, in response to a SQL Server Magazine article that should not have been published .

A blog post by Paul Randal explaining why t-log maintenance is important和 why you shouldn't shrink your data files, either .

Mike Walsh has a great answer covering some of these aspects too, including reasons why you might not be able to shrink your log file immediately .