如何清除 SQL Server 事务日志？

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

首先，进行完整备份

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

如果您关心时间点恢复

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

大概您的数据库处于 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\ 应该位于代表不同底层存储设备的不同机器上。将它们备份到同一台机器（或使用相同底层磁盘的另一台机器，或同一物理主机上的不同 VM）并不能真正帮助您，因为如果机器发生故障，您就丢失了数据库和它的备份。根据您的网络基础设施，在本地备份然后将它们转移到幕后的不同位置可能更有意义；在任何一种情况下，您都希望尽快将它们从主数据库机器上移除。

现在，一旦您运行了常规日志备份，将日志文件缩小到比现在被炸毁的任何东西更合理的东西应该是合理的。这确实不意味着一遍又一遍地运行 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 重复使用部分日志文件（实质上是逐步淘汰非活动事务）而不是增长以保留 所有 事务的记录（如 { 2} 恢复直到您备份日志）。 CHECKPOINT 事件将有助于控制日志并确保它不需要增长，除非您在 CHECKPOINT 之间生成大量 t-log 活动。

接下来，您应该绝对确保此日志增长确实是由于异常事件（例如，年度春季大扫除或重建最大索引），而不是由于正常的日常使用。如果您将日志文件缩小到小得离谱的大小，而 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 中不再可用。其次，如果您处于完整恢复模式，这将破坏您的日志链并需要新的完整备份。

分离数据库，删除日志文件，然后重新附加。我无法强调这有多危险。您的数据库可能无法备份，可能会出现可疑情况，您可能必须恢复到备份（如果有的话）等等。

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

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

创建第二个日志文件。这将为已填满磁盘的驱动器提供暂时的缓解，但这就像试图用创可贴修复被刺破的肺一样。您应该直接处理有问题的日志文件，而不是仅仅添加另一个潜在问题。除了将某些事务日志活动重定向到不同的驱动器之外，第二个日志文件实际上对您没有任何作用（与第二个数据文件不同），因为一次只能使用其中一个文件。 Paul Randal 还解释了为什么多个日志文件会在以后给您带来麻烦。

主动

与其将日志文件缩小到很小的数量并让它自己以很小的速率不断地自动增长，不如将它设置为相当大的大小（一个可以容纳最大并发事务集的总和的大小）并设置一个合理的自动增长设置为后备，因此它不必多次增长以满足单个事务，因此在正常业务操作期间它相对很少需要增长。

这里最糟糕的设置是 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。

-- DON'T FORGET TO BACKUP THE DB :D (Check [here][1]) 


USE AdventureWorks2008R2;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2008R2_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY FULL;
GO

来自：DBCC SHRINKFILE (Transact-SQL)

您可能需要先备份。

免责声明：请仔细阅读下面的评论，我假设您已经阅读了接受的答案。正如我将近 5 年前所说：

如果有人对这不是适当或最佳解决方案的情况有任何意见要添加，请在下面发表评论

右键单击数据库名称。

选择任务→收缩→数据库

然后点击确定！

我通常打开包含数据库文件的 Windows 资源管理器目录，因此我可以立即看到效果。

我真的很惊讶这能奏效！通常我以前使用过 DBCC，但我只是尝试过它并没有缩小任何东西，所以我尝试了 GUI（2005 年）并且效果很好 - 在 10 秒内释放了 17 GB

在完全恢复模式下，这可能不起作用，因此您必须先备份日志，或者更改为简单恢复，然后收缩文件。 [为此感谢@onupdatecascade]

--

PS：我很欣赏一些人对此危险的评论，但在我的环境中，我自己没有任何问题，特别是因为我总是先做完整备份。因此，在继续之前，请考虑您的环境是什么，以及这如何影响您的备份策略和工作安全性。我所做的只是向人们指出微软提供的一项功能！

下面是一个收缩事务日志的脚本，但我绝对建议在收缩之前备份事务日志。

如果您只是缩小文件，您将丢失大量数据，这些数据可能会在发生灾难时成为救生员。事务日志包含许多有用的数据，可以使用第三方事务日志阅读器读取（可以手动读取，但需要付出极大的努力）。

在进行时间点恢复时，事务日志也是必须的，所以不要只是扔掉它，而是要确保事先备份它。

以下是人们使用存储在事务日志中的数据来完成恢复的几篇文章：

如何在 SQL Server 2008 中查看事务日志

在 SQL Server 2008 中读取日志文件 (*.LDF)

USE DATABASE_NAME;
GO

ALTER DATABASE DATABASE_NAME
SET RECOVERY SIMPLE;
GO
--First parameter is log file name and second is size in MB
DBCC SHRINKFILE (DATABASE_NAME_Log, 1);

ALTER DATABASE DATABASE_NAME
SET RECOVERY FULL;
GO

执行上述命令时，您可能会收到如下所示的错误

“无法缩小日志文件（日志文件名），因为位于文件末尾的逻辑日志文件正在使用中”

这意味着 TLOG 正在使用中。在这种情况下，请尝试连续多次执行此操作，或找到减少数据库活动的方法。

这是一种简单且非常不雅且有潜在危险的方式。

备份数据库 分离数据库重命名日志文件 附加数据库 将重新创建新日志文件 删除重命名日志文件。

我猜你没有做日志备份。 （这会截断日志）。我的建议是将恢复模式从 full 更改为 simple。这将防止日志膨胀。

如果您不使用事务日志进行恢复（即您只进行完整备份），您可以将恢复模式设置为“简单”，事务日志将很快缩小并且不再填满。

如果您使用的是 SQL 7 或 2000，您可以在数据库选项选项卡中启用“truncate log on checkpoint”。这具有相同的效果。

显然不建议在生产环境中这样做，因为您将无法恢复到某个时间点。

不推荐使用 John 推荐的这种技术，因为无法保证数据库在没有日志文件的情况下会附加。将数据库从完整更改为简单，强制检查点并等待几分钟。 SQL Server 将清除日志，然后您可以使用 DBCC SHRINKFILE 将其缩小。

到目前为止，这里的大多数答案都假设您实际上不需要事务日志文件，但是如果您的数据库使用 FULL 恢复模式，并且您希望保留备份以防需要恢复数据库，那么 不要像许多这些答案所建议的那样截断或删除日志文件。

BACKUP LOG MyDatabaseName 
TO DISK='C:\DatabaseBackups\MyDatabaseName_backup_2013_01_31_095212_8797154.trn'

DBCC SHRINKFILE (N'MyDatabaseName_Log', 200)