如何清除SQL Server事务日志？

我不是SQL专家,每当我需要做一些超出基础知识的事情时,我就会想起这个事实.我有一个不大的测试数据库,但事务日志肯定是.如何清除事务日志？

1> Aaron Bertra..：

---

使日志文件更小应该真正保留用于遇到意外增长的情况,而这种情况不会再发生.如果日志文件将再次增长到相同的大小,那么暂时缩小它就不会实现.现在,根据数据库的恢复目标,这些是您应该采取的操作.

首先,进行完整备份

如果没有确保在出现问题时可以恢复数据库,切勿对数据库进行任何更改.

如果您关心时间点恢复

(通过即时恢复,我的意思是您关心能够还原到完整或差异备份以外的任何其他内容.)

据推测,您的数据库处于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重新使用部分日志文件(基本上逐步淘汰不活动的事务),而不是增长以保留所有事务的记录(比如FULL在备份日志之前进行恢复).CHECKPOINT事件将有助于控制日志并确保它不需要增长,除非您在CHECKPOINTs 之间生成大量的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中不再可用.其次,如果您处于FULL恢复模式,这将破坏您的日志链并需要新的完整备份.

分离数据库,删除日志文件,然后重新附加.我不能强调这有多危险.您的数据库可能无法恢复,可能会出现疑似,您可能需要恢复备份(如果有的话),等等.

使用"缩小数据库"选项.DBCC SHRINKDATABASE并且执行相同操作的维护计划选项是不好的想法,特别是如果您真的只需要解决日志问题.使用DBCC SHRINKFILE或ALTER DATABASE ... MODIFY FILE(上面的示例)定位要调整的文件并单独调整.

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

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

主动

而不是将您的日志文件缩小到一个小数量并让它自己以小的速率自动进行自动增长,将其设置为一个相当大的大小(一个可以容纳最大并发事务集合的总和)并设置合理的自动增长设置为后备,因此它不必增长多次以满足单个事务,因此在正常业务操作期间它必须增长相对较少.

这里最糟糕的设置是1 MB增长或10%增长.有趣的是,这些是SQL Server的默认设置(我已经抱怨并要求更改无效) - 数据文件为1 MB,日志文件为10%.前者在这个时代太小了,后者每次都会导致更长更长的事件(例如,你的日志文件是500 MB,第一个增长是50 MB,下一个增长是55 MB,下一个增长是60.5 MB等等 - 在缓慢的I/O上,相信我,你会真正注意到这条曲线).

进一步阅读

请不要停在这里; 虽然你在那里看到的有关缩小日志文件的大部分建议本来就很糟糕甚至可能是灾难性的,但有些人更关心数据完整性而不是释放磁盘空间.

我在2009年写的一篇博客文章,当时我看到一些"这里是如何收缩日志文件"的帖子出现了.

博客文章布伦特奥扎尔写了四年前,指向多方资源,响应应该在SQL Server杂志的文章没有被公布.

Paul Randal撰写的博客文章解释了为什么t-log维护很重要以及为什么你不应该缩小数据文件.

Mike Walsh也有一个很好的答案,涵盖了其中的一些方面,包括你可能无法立即缩小日志文件的原因.

---

我还想补充一点,清除日志是通过备份日志(完整或大容量日志恢复)或检查点(简单恢复)完成的.但是,如果您处于必须缩小日志文件的情况,那还不够.您需要使当前活动的VLF循环回到日志文件的开头.您可以通过背靠背发出两个日志备份或检查点,在SQL 2008及更高版本中强制执行此操作.第一个清除它,第二个将它循环回文件的开头.

---

除此之外,这是本页面给出的最完整和正确的答案.

---

时间点恢复不是使用完全恢复模型的唯一原因.主要原因是防止数据丢失.数据丢失的可能性是备份之间的长度.如果您只进行每日备份,则数据丢失的可能性为24小时.如果您每半小时添加一次日志备份,则数据丢失的可能性将变为30分钟.此外,还需要日志备份来执行任何类型的零碎还原(例如从损坏中恢复).

---

@Doug_Ivison因为在任何时候都可以清除日志记录.允许您备份不完整的日志有什么意义？在简单恢复中,日志仅用于允许事务回滚.一旦提交或回滚了一个事务,下一秒就可以从日志中消失.

---

@zinczinc好，谢谢您的反馈。我看到的将答案放在第一位，然后再进行解释的问题是，他们永远不会阅读重要的部分。实际上，我淹没读者的演讲比结尾的答案要重要得多，恕我直言，我提供的背景对于做出这些选择非常重要。但是，如果您想提交单行答案是因为您认为这对OP更好，请随时使用我的答案中的那一部分来做一个更好的答案，我们可以从中学习。

2> Rui Lima..：

---

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

您可能想先备份.

---

这可能是最好的答案.很好,简单,我的upvote :)

3> Simon_Weaver..：

---

免责声明:请仔细阅读以下评论,我假设您已经阅读了已接受的答案.正如我近5年前所说:

如果任何人有任何评论要添加的情况,这不是一个充分或最佳的解决方案,请在下面评论

---

右键单击数据库名称.

选择任务→收缩→数据库

然后点击OK!

我经常打开包含数据库文件的Windows资源管理器目录,这样我就可以立即看到效果了.

我真的很惊讶这个工作!通常我之前使用过DBCC,但我只是尝试了它并没有缩小任何东西,所以我尝试了GUI(2005)并且效果很好 - 在10秒内释放17 GB

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

-

PS:我很欣赏一些有关此危险的评论,但在我的环境中,我自己也没有遇到任何问题,特别是因为我总是首先进行完整备份.因此,请在继续之前考虑您的环境,以及这将如何影响您的备份策略和工作安全性.我所做的只是将人们指向微软提供的功能!

---

在完全恢复模式下,这可能不起作用,因此您必须先备份日志,或更改为简单恢复,然后收缩文件.

---

哇,为这个答案获得1300多名代表的荣誉,但这确实是一个糟糕的建议.

---

抱歉.但这个答案简直不会错.通过缩小数据库,您将增加事务日志文件.无论何时在SQL Server数据库中移动数据,都需要进行日志记录 - 膨胀日志文件.要减小日志大小,请将数据库设置为简单恢复或(如果您关心/需要记录数据 - 并且几乎总是在生产中),请备份日志.更多细节在这些简单,免费的视频中:http://www.sqlservervideos.com/video/logging-essentials/ http://www.sqlservervideos.com/video/sql2528-log-files/

---

这里有一个夸张的例子来说明正在发生的事情以及为什么收缩在定期的基础上是绝对关键的:在备份完成之前,记录A会改变100万次.日志中有什么？999,999条不相关的数据.如果日志永远不会缩减,您永远不会知道数据库的真正运营费用是多少.此外,您最有可能在SAN上占用宝贵的资源.缩小是良好的维护,让您与您的环境保持联系.告诉我一个认为你不应该畏缩的人,我会告诉你一个无视环境的人.

---

@onupdatecascade - 对完全恢复技巧的好评.有另一个带有巨大日志的数据库:切换到简单,然后收缩数据库并切换回完全.日志文件低至500kb!

---

除了Michael和Aaron所说的,如果您在完整恢复模型和简单恢复模型之间来回切换,在您至少学习基础知识之前,不应该允许您触摸SQL Server.

---

最糟糕的建议.这是积极危险的建议,应该删除.

---

您是否关闭了"完全恢复"模式并将其更改为"简单"模式？免责声明:如果您这样做,请首先备份.我从来没有遇到过问题,但却讨厌丢失数据

4> 小智..：

---

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

如果您只是缩小文件,您将丢失大量数据,这些数据可能会在发生灾难时保护您的生命.事务日志包含许多可以使用第三方事务日志读取器读取的有用数据(可以手动读取,但需要付出极大的努力).

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

以下是几个帖子,其中人们使用存储在事务日志中的数据来完成恢复:

如何在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正在使用中.在这种情况下,请尝试连续多次执行此操作,或者找到减少数据库活动的方法.

5> Johnno Nolan..：

---

这是一种简单而且非常不优雅且具有潜在危险的 方式.

备份DB

分离DB

重命名日志文件

附上数据库

将重新创建新的日志文件

删除重命名的日志文件.

我猜你没有做日志备份.(截断日志).我的建议是将恢复模式从完整变为简单.这样可以防止log膨胀.

---

尊敬地,删除/重命名/重新创建/替换日志是一个非常糟糕的主意.收缩必须降低风险,加上它很简单.

---

+1 - 不优雅与否,这种方法让我几次脱离热水,数据库日志已经填满整个磁盘,这样即使收缩命令也无法运行.

---

日志中是否存在未经检查的交易风险？

6> Jonathan..：

---

如果您不使用事务日志进行还原(即您只进行完全备份),则可以将恢复模式设置为"简单",并且事务日志将很快缩小并且永远不会再次填满.

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

这显然不是在生产环境中推荐的,因为您将无法恢复到某个时间点.

---

@Doug_Ivison是的,事务日志中将包含打开的事务,但是一旦检查点发生,它们将以简单模式被删除.这个答案只是作为一个快速的"我的开发/测试盒有一个大的事务日志,我希望它消失所以我不需要经常担心它",而不是打算进入生产环境.重新迭代:*不要在生产*中这样做.

7> Rachel..：

---

到目前为止,大多数答案假设您实际上并不需要事务日志文件,但是如果您的数据库使用FULL恢复模型,并且您希望保留备份以防需要还原数据库,则不要截断或删除日志文件的方式很多这些答案的建议.

消除日志文件(通过截断,丢弃,删除等)将破坏您的备份链,并阻止您恢复到自上次完整,差异或事务日志备份以来的任何时间点,直到下一次完整或差异备份.

来自微软的文章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)

---

我同意你的答案,除了`,1)`部分.问题在于,如果将其缩小到1 MB,导致*正常*日志大小的增长事件将非常昂贵,如果增长率保持为默认值10%,则会有*很多*.

8> 小智..：

---

需要正确维护SQL Server事务日志,以防止其不必要的增长.这意味着经常运行事务日志备份.如果不这样做,您就有可能使事务日志变满并开始增长.

除了这个问题的答案,我建议阅读和理解事务日志常见的神话.这些读数可能有助于理解事务日志并决定使用哪种技术来"清除"它:

来自10个最重要的SQL Server事务日志神话:

神话:我的SQL Server太忙了.我不想进行SQL Server事务日志备份

SQL Server中性能最密集的操作之一是联机事务日志文件的自动增长事件.通过不经常进行事务日志备份,联机事务日志将变满并且必须增长.默认增长大小为10%.如果未创建事务日志备份,则数据库越忙,联机事务日志将越快增长创建SQL Server事务日志备份不会阻止联机事务日志,但会自动增长事件.它可以阻止在线事务日志中的所有活动

来自事务日志的神话:

误区:定期日志缩小是一种很好的维护习惯

假.日志增长非常昂贵,因为新块必须清零.所有写入活动在该数据库上停止,直到清零为止,如果您的磁盘写入速度很慢或自动增长大小很大,那么该暂停可能很大,用户会注意到.这就是为什么要避免增长的原因之一.如果你缩小日志,它会再次增长,你只是在不必要的缩小和再生游戏上浪费磁盘操作

9> mrdenny..：

---

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

---

我同意这种策略,但它应该保留用于由于一些不可预见和/或特殊事件导致日志爆炸的情况.如果你每周都要设立一份工作,那你就是非常非常错误的.

---

非常非常真实的亚伦.

10> 小智..：

---

首先检查数据库恢复模型.默认情况下,SQL Server Express Edition为简单恢复模型创建数据库(如果我没有记错的话).

使用Truncate_Only备份日志DatabaseName:

DBCC ShrinkFile(yourLogical_LogFileName, 50)

SP_helpfile将为您提供逻辑日志文件名.

参考:

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

如果您的数据库处于完全恢复模型中,并且您没有进行TL备份,则将其更改为SIMPLE.

11> ripvlan..：

---

使用该DBCC ShrinkFile ({logicalLogName}, TRUNCATEONLY)命令.如果这是一个测试数据库并且您正在尝试保存/回收空间,这将有所帮助.

请记住,虽然TX日志确实具有一种最小/稳定的状态大小,但它们会长大.根据您的恢复模型,您可能无法缩小日志 - 如果在FULL中并且您没有发出TX日志备份,则日志无法缩小 - 它将永远增长.如果您不需要TX日志备份,请将恢复模型切换为" 简单".

请记住,在任何情况下都不要删除日志(LDF)文件!你几乎会有即时数据库损坏.熟!完成!丢失的数据!如果保留"未修复",则主MDF文件可能会永久损坏.

永远不会删除事务日志 - 您将丢失数据!部分数据位于TX日志中(无论恢复模式如何)...如果您分离并"重命名"有效删除部分数据库的TX日志文件.

对于已删除TX日志的用户,您可能需要运行一些checkdb命令并修复损坏,然后再丢失更多数据.

查看Paul Randal关于这个主题的博客文章,不好的建议.

通常也不要在MDF文件上使用shrinkfile,因为它会严重破坏您的数据.查看他的"错误建议"部分以获取更多信息("为什么不应缩小数据文件")

查看Paul的网站 - 他介绍了这些问题.上个月,他在" 神话一天"系列节目中介绍了许多这些问题.