如何清除SQL Server事务日志？

Question

如何清除SQL Server事务日志？

sql-servertransaction-log

671

我不是一个SQL专家，每当我需要做一些基本操作以外的事情时，都会想起这一点。我有一个测试数据库，它的大小并不大，但交易日志确实很大。如何清除交易日志？

- Kilhoffer

1

如何使用DBCC SHRINKFILE语句在SQL Server 2005中收缩事务日志文件 - Gulzar Nazim

5

管理工作室中应该有一个命令：“单击以收缩日志”，然后你就完成了。 - frenchie

21个回答

259

-- 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)

您可能需要先备份。

- Rui Lima

5

即使恢复类型之前是其他值，这种方法也会将恢复类型设置为“FULL”。 - Vildan

3

像魔法一样起作用！请注意，日志文件的名称实际上是它的逻辑名称（可以在db->properties->files中找到）。 - Bizhan

3

我会在这个脚本中加入“备份数据库”语句，以防止任何人忘记这部分。我这样说是因为几年前，在一个空间非常有限的磁盘上缩小了一个数据库。在缩小过程中，文件变得越来越大，然后出现了“空间不足”的错误。结果：我失去了数据库。幸运的是，它是一个具有容错性的日志数据库。 - Cesar

@Dragas 在答案中有官方文档的链接，你可能想要考虑查看一下官方文档。顺便说一句，一个好的数据库管理员不会在stackoverflow上寻找如何清除日志的方法，他们肯定知道，但我不是一个DBA :) - Rui Lima

2

起初没有成功，直到我意识到在 SQL-Server 2016 中，日志文件实际上是小写 "_log"。第三个命令是区分大小写的。一旦我将其更改为与我的数据库日志名称完全匹配，就可以正常工作了！ - Gabe Halsmer

让我困扰的是，这个应该对我有效，但对我的SQL Server版本却没有。根据文档，它应该可以。虽然它确实缩小了我的日志，但它并没有截断它，也没有将恢复模式更改为简单模式。在它起作用之前，我不得不在GUI中完成这两个步骤。与其他替代方案相比，管理SQL Server并不好玩。 - chaostheory

196

注意：在尝试此方法之前，请仔细阅读本回答下方的评论，并确保检查已接受的答案。正如我在将近五年前所说：

如果有人对这不是最佳解决方案的情况有任何评论，请在下面发表评论。

结果确实有人提出了问题 :-)

原始回答：

右键单击数据库名称。
选择任务 → 收缩 → 数据库
然后点击 确定！

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

我真的很惊讶这个方法起作用了！通常我以前使用DBCC，但我刚试了一下，它没有收缩任何东西，所以我尝试了GUI（2005），它非常好用——只用10秒就释放了17 GB。

在完整恢复模式下，这可能行不通，因此您必须首先备份日志，或切换到简单恢复模式，然后收缩文件。[感谢 @onupdatecascade 提供这个提示]

--

PS：我感谢一些人对此的危险性进行了评论，但在我的环境中，我自己使用这种方法并没有遇到任何问题，特别是因为我总是先进行完全备份。因此，在继续之前，请考虑您的环境以及如何影响您的备份策略和工作安全性。我只是在向人们介绍Microsoft提供的一个功能！

- Simon

51

在完整恢复模式下，这可能无法起作用，因此您需要首先备份日志，或切换到简单恢复模式，然后缩小文件。 - onupdatecascade

7

@onupdatecascade - 关于完全恢复技巧的建议很好。我有另一个日志非常大的数据库：切换到简单模式，缩小数据库，然后切换回完全模式。日志文件缩小到了500kb！ - Simon_Weaver

27

抱歉，但这个答案简直不能再错了。通过缩小数据库，您将增加事务日志文件的大小。在 SQL Server 数据库中移动数据时，每次都需要记录日志 - 这会使日志文件膨胀。要减小日志文件的大小，可以将数据库设置为简单恢复模式，或者（如果您关心/需要已记录的数据 - 在生产中几乎总是需要）备份日志。更多详细信息请参见以下简单、免费的视频： http://www.sqlservervideos.com/video/logging-essentials/ http://www.sqlservervideos.com/video/sql2528-log-files/ - Michael K. Campbell

30

哇，祝贺你因这篇回答获得了1300多的声望，但实际上这是非常糟糕的建议。 - Aaron Bertrand

8

这里有一个夸张的例子来说明为什么定期收缩是非常关键的：在备份之前，记录A被更改了100万次。日志中有什么？999,999条无关紧要的数据。如果日志从未被收缩，你将永远不知道数据库的真实运营成本。此外，你还会占用SAN上宝贵的资源。收缩是良好的维护方式，可以让你了解环境情况。告诉我谁认为你永远不应该收缩，我会告诉你他正在忽略自己的环境。 - Newclique

显示剩余8条评论

59

下面是缩小事务日志的脚本，但我强烈建议在缩小之前备份事务日志。

如果你只缩小文件，你将失去大量可能在灾难发生时拯救生命的数据。事务日志包含许多有用的数据，可以使用第三方事务日志阅读器进行读取（尽管需要极大的工作量）。

当谈到恢复到某个时间点时，事务日志也是必不可少的，因此不要丢弃它，而是确保你提前备份好它。

以下是一些人们在恢复中使用存储在事务日志中的数据的帖子:

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

当执行上述命令时，您可能会遇到以下类似错误：

“Cannot shrink log file (log file name) because the logical log file located at the end of the file is in use“

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

- Michael Dalton

38

这里有一种简单而非常不雅且潜在危险的方法。

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

我猜你没有进行日志备份。(这会截断日志)。我的建议是将恢复模式从完整更改为简单。这将防止日志膨胀。

- John Nolan

17

尊敬地提醒，删除/重命名/重新创建/替换日志是一个非常糟糕的想法。缩小尺寸则风险要小得多，而且操作相当简单。 - onupdatecascade

14

虽然不够优雅，但这种方法曾经帮我在数据库日志填满整个磁盘时多次摆脱困境，即使缩小命令也无法运行。 - Shaul Behr

5

在日志中存在未经检查的事务风险吗？ - Paul

1

这对于较小的数据库可能也可以，但如果你有一个3或4 TB的数据库，它可能不是最佳解决方案。 - Jaques

1

如果您开发系统已有很长时间并在开发期间加载/删除了成千上万条记录，那么这看起来是可以的。然后，当您要使用此数据库部署到生产环境时，已记录的测试/开发数据是多余的，因此如果丢失它也没有关系，对吗？ - JsonStatham

显示剩余2条评论

32

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

如果您正在使用 SQL 7 或 2000，则可以在数据库选项选项卡中启用“清除日志”功能。这具有同样的效果。

显然，这在生产环境中是不推荐的，因为您将无法还原到特定时间点。

- Jonathan

2

将恢复模式设置为简单模式并不能自动缩小事务日志。 - Aaron Bertrand

@Aaron并不是单纯的如此。我想OP会使用他们的测试数据库，因此“事务日志很快就会缩小”，但你说得对，这更像一个副作用：简单恢复模式可能会让你很快结束事务日志的收缩。 - Jonathan

“简单”……并不会永远不再填满 —— 并非如此。我在过去的48小时内看到它发生在一个恢复模式设置为“简单”的数据库上。日志文件的文件增长被设置为“受限制”，而我们一直在对其进行大量活动……我理解这是一个不寻常的情况。（在我们的情况下，由于有足够的磁盘空间，我们增加了日志文件大小，并将日志文件增长设置为“无限制”……顺便说一句——界面上的错误——更改后，显示为带有最大值为2,097,152 MB的“受限制”。） - Doug_Ivison

3

@Doug_Ivison 是的，事务日志中会有未提交的事务，但在简单模式下，一旦完成检查点，它们将被移除。这个答案只是为了快速解决“我的开发/测试环境有一个巨大的事务日志，我希望它能够消失，这样我就不需要经常担心它”，而不是用于生产环境。再次强调：不要在生产环境中这样做。 - Jonathan

2

这些都是真的，我知道这只是一个开发阶段的快速解决方法。我为什么要评论：在我亲身经历之前，我实际上认为简单恢复模式永远不会满...而我想这花了我更长时间去理解和解决，而我现在明白了异常大的事务可以导致这种情况。 - Doug_Ivison

也许在将数据库设置为完全恢复模式之后，然后收缩日志文件（使用 UI，根据此帖子），以查看减小日志大小的效果？ - Nate Anderson

11

为了防止SQL Server事务日志不必要的增长，必须正确维护它。这意味着要经常运行事务日志备份。如果不这样做，您就面临着事务日志变满并开始增长的风险。

除了回答这个问题的答案外，我建议阅读和理解有关事务日志常见误解的文章。这些阅读材料可以帮助理解事务日志，并决定使用哪些技术来"清除"它：

来自 10 most important SQL Server transaction log myths:

谬论：我的SQL Server太忙了，我不想进行SQL Server事务日志备份。

SQL Server中最耗费性能的操作之一是在线事务日志文件的自动增长事件。如果不经常进行事务日志备份，在线事务日志将变满并且必须进行增长。默认的增长大小为10%。数据库越繁忙，如果不创建事务日志备份，则在线事务日志增长得越快。创建SQL Server事务日志备份不会阻塞在线事务日志，但自动增长事件会阻塞在线事务日志中的所有活动。

来自 Transaction log myths：

谬论：定期缩小日志是一种良好的维护实践。

假的。日志增长非常昂贵，因为新块必须被清零。直到清零完成，在该数据库上的所有写入活动都将停止，如果您的磁盘写入速度很慢或自动增长大小很大，则暂停可能会很长且用户会注意到。这就是为什么要避免增长的原因之一。如果收缩日志，它会再次增长，并且您只是在无谓地进行收缩和再增长的操作，浪费磁盘操作。

- McRobert

10

大多数答案都假设您实际上不需要事务日志文件，但是如果您的数据库正在使用“FULL”恢复模型，并且您希望保留备份以防需要还原数据库，则请勿像这些答案建议的那样截断或删除日志文件。

通过消除日志文件（通过截断、丢弃、擦除等方式），将会破坏备份链，并且会阻止您从最后一次完整、差异或事务日志备份之后的任何时间点进行还原，直到下一个完整或差异备份被创建。

来自Microsoft文章《BACKUP》：

我们建议您永远不要使用NO_LOG或TRUNCATE_ONLY手动截断事务日志，因为这会破坏日志链。在下一个完整或差异数据库备份之前，数据库无法受到媒体故障的保护。仅在非常特殊的情况下使用手动日志截断，并立即创建数据备份。

为了避免这种情况发生，在缩小日志文件之前，请将其备份到磁盘。语法应该类似于这样：

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

DBCC SHRINKFILE (N'MyDatabaseName_Log', 200)

- Rachel

3

除了, 1)部分外，我同意您的答案。问题在于，如果将其缩小到1 MB，则导致正常日志大小的增长事件将会非常昂贵，并且如果将增长率保留为10％的默认值，则这些事件将会很多。 - Aaron Bertrand

10

约翰推荐的这种技术不被建议使用，因为没有保证数据库可以在没有日志文件的情况下附加。把数据库从完整模式改成简单模式，强制进行检查点并等待几分钟。SQL Server将清除日志，然后您可以使用DBCC SHRINKFILE来缩小它。

- mrdenny

但我已经做了数十次而没有问题。也许你可以解释一下为什么数据库可能无法重新附加。 - Johnno Nolan

我偶尔（并不经常）看到SQL Server无法将数据库重新附加到数据库，当日志文件被删除时。这会让你得到一个无用的MDF文件。有几种可能会导致这个问题。未完成回滚的事务是其中之一。 - mrdenny

4

我同意这种策略，但应该仅在日志因某些未预见和/或非凡事件而爆炸时才使用。如果您每周设置一个工作来执行此操作，则做法是非常错误的。 - Aaron Bertrand

是的，这种情况刚刚发生在我们身上。我们想要丢弃20G的日志文件，因为我们刚刚在移动数据库之前备份了数据。但是MSSQL不允许我们重新附加新的数据库而不使用庞大的日志文件。 - George M Reinstate Monica

6

首先检查数据库的恢复模式。默认情况下，SQL Server Express Edition会为简单恢复模式创建一个数据库（如果我没有搞错的话）。

使用Truncate_Only备份日志：Backup log DatabaseName With Truncate_Only：

DBCC ShrinkFile(yourLogical_LogFileName, 50)

SP_helpfile将给出逻辑日志文件名。

参考：

从SQL Server数据库中的完整事务日志恢复

如果您的数据库处于完整恢复模式，并且您没有进行TL备份，则将其更改为简单模式。

- Majid Ali

这是我在开发环境中清除日志文件的方式。生产环境及其相关备份策略等方面，我会交给数据库管理员处理 :-) - Joon

在当前版本的SQL Server中，“TRUNCATE_ONLY”选项已不再存在，并且在支持它的版本上也不建议使用（请参见Rachel的回答）。 - Aaron Bertrand

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Aaron Bertrand · Accepted Answer

缩小日志文件的操作应该只在遇到意外增长的情况下使用，而且你不希望再次发生这种情况。如果日志文件会再次增长到相同的大小，那么暂时缩小它并没有太大的意义。根据数据库的恢复目标，你应该采取以下行动。

首先，进行完整备份。

在没有确保能够在出现问题时恢复数据库的情况下，不要对数据库进行任何更改。

如果你关心按时间点恢复

（按时间点恢复指的是你希望能够恢复到除完整备份或差异备份之外的任何时间点。）

假设你的数据库处于完整恢复模式。如果不是，请确保它是。

ALTER DATABASE testdb SET RECOVERY FULL;

-- and take another full backup here
-- so log backups are possible

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

DECLARE @path nvarchar(4000) = CONCAT(
  N'\\backup_share\log\testdb_',  
  CONVERT(char(8), GETDATE(), 112), N'_',
  REPLACE(CONVERT(char(8), GETDATE(), 108),':',''),
  N'.trn');

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

WAITFOR DELAY '00:00:01';
GO 2 -- run twice to ensure file wrap-around

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

现在，一旦您定期运行日志备份，就可以将日志文件缩小到比现在膨胀的大小更合理的大小。这并不意味着反复运行SHRINKFILE直到日志文件变为1 MB - 即使您经常备份日志，它仍然需要容纳可能发生的并发事务的总和。日志文件的自动增长事件是昂贵的，因为SQL Server必须将文件清零（与启用即时文件初始化的数据文件不同），并且用户事务必须等待此过程完成。您希望尽可能少地执行此增长-缩小-增长-缩小的例程，并且肯定不希望让用户为此付费。

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

因此，您需要为日志文件确定一个实际的大小。如果没有更多关于您的系统的了解，我们无法告诉您具体大小。但是，如果您经常收缩日志文件并且它又重新增长，一个好的参考值可能是比最大值高10-50%。假设这个值为200MB，并且您希望任何后续的自动增长事件为50MB，那么您可以通过以下方式调整日志文件的大小（其中'yourdb_log'是您数据库日志文件的逻辑名称，您可以在sys.database_files中找到）：

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

请注意，如果日志文件当前大小超过200 MB，您可能需要首先运行以下操作：

USE yourdb;
GO
DBCC SHRINKFILE(N'yourdb_log', 200); -- unit is MB
GO

如果您不关心点时间恢复

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

ALTER DATABASE testdb SET RECOVERY SIMPLE;

将数据库设置为“简单”恢复模式将确保SQL Server重用日志文件的部分内容（基本上淘汰不活动的事务），而不是不断增长以记录“所有”事务（就像“完整”恢复模式在备份日志之前一直做的那样）。“检查点”事件将帮助控制日志，并确保它不需要增长，除非在“检查点”之间生成了大量的事务日志活动。

接下来，您应该确保此日志增长确实是由异常事件引起的（例如，年度大扫除或重建最大的索引），而不是由正常的日常使用引起的。如果将日志文件缩小到非常小的大小，而SQL Server只是不得不再次增长以适应您的正常活动，那么您获得了什么？您能够暂时释放的磁盘空间是否能够利用？如果您需要立即解决问题，那么可以运行以下命令：

USE yourdb;
GO
CHECKPOINT;
CHECKPOINT; -- run twice to ensure file wrap-around
GO
DBCC SHRINKFILE(N'yourdb_log', 200); -- unit is MB
GO

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

一些你不想做的事情

使用TRUNCATE_ONLY选项备份日志，然后使用SHRINKFILE。首先，这个TRUNCATE_ONLY选项已经被弃用，在当前版本的SQL Server中不再可用。其次，如果您处于FULL恢复模式，这将破坏您的日志链，并需要进行新的完整备份。
分离数据库，删除日志文件，然后重新附加。我无法强调这个方法有多么危险。您的数据库可能无法重新启动，可能会以可疑状态重新启动，您可能不得不恢复到备份（如果有的话），等等。
使用"收缩数据库"选项。DBCC SHRINKDATABASE和使用维护计划选项做同样的事情都是不好的主意，特别是如果您只是想解决一个日志问题。针对您想要调整的文件进行独立调整，使用DBCC SHRINKFILE或ALTER DATABASE ... MODIFY FILE（上面有示例）。
将日志文件收缩到1MB。这看起来很诱人，因为嘿，SQL Server在某些情况下允许我这样做，并且看看它释放了多少空间！除非您的数据库是只读的（如果是，您应该使用ALTER DATABASE标记它），否则这绝对只会导致许多不必要的增长事件，因为日志必须适应当前事务，而不管恢复模式如何。临时释放空间的意义是什么，SQL Server会慢慢而痛苦地重新占用它。
创建第二个日志文件。这将为填满磁盘的驱动器提供临时缓解，但这就像用创可贴修复一个破损的肺一样。您应该直接处理有问题的日志文件，而不仅仅是增加另一个潜在的问题。除了将一些事务日志活动重定向到不同的驱动器，第二个日志文件对您来说真的没有任何作用（不像第二个数据文件），因为一次只能使用一个文件。Paul Randal也解释了为什么多个日志文件以后可能会给您带来麻烦。

积极主动

不要将日志文件缩小到一定的小量，然后让它自动以较小的速度不断增长，而是将其设置为相当大的大小（足以容纳最大并发事务集的总和），并设置一个合理的自动增长设置作为备用，这样它就不必多次增长以满足单个事务，并且在正常业务操作期间，它很少需要增长。

在这里，最糟糕的设置是1MB增长或10%增长。有趣的是，这些是SQL Server的默认设置（我已经抱怨过并要求更改，但没有得到回应）- 数据文件为1MB，日志文件为10%。前者在当今时代太小了，而后者会导致每次事件越来越长（比如，你的日志文件为500MB，第一次增长为50MB，下一次增长为55MB，再下一次增长为60.5MB等等-在慢速I/O上，相信我，你会真正注意到这个曲线）。

进一步阅读

请不要停在这里；虽然关于缩小日志文件的大部分建议都是本质上不好甚至有潜在灾难性的，但也有一些人更关心数据完整性而不是释放磁盘空间。