如何清除 SQL Server 事务日志? [英] How do you clear the SQL Server transaction log?

问题描述

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

I'm not a SQL expert, and I'm reminded of the fact every time I need to do something beyond the basics. I have a test database that is not large in size, but the transaction log definitely is. How do I clear out the transaction log?

推荐答案

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

Making a log file smaller should really be reserved for scenarios where it encountered unexpected growth which you do not expect to happen again. If the log file will grow to the same size again, not very much is accomplished by shrinking it temporarily. Now, depending on the recovery goals of your database, these are the actions you should take.

在没有确保可以在出现问题时恢复数据库的情况下，切勿对数据库进行任何更改.

Never make any changes to your database without ensuring you can restore it should something go wrong.

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

(And by point-in-time recovery, I mean you care about being able to restore to anything other than a full or differential backup.)

大概您的数据库处于 FULL 恢复模式.如果不是，请确保它是:

Presumably your database is in FULL recovery mode. If not, then make sure it is:

ALTER DATABASE testdb SET RECOVERY FULL;

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

Even if you are taking regular full backups, the log file will grow and grow until you perform a log backup - this is for your protection, not to needlessly eat away at your disk space. You should be performing these log backups quite frequently, according to your recovery objectives. For example, if you have a business rule that states you can afford to lose no more than 15 minutes of data in the event of a disaster, you should have a job that backs up the log every 15 minutes. Here is a script that will generate timestamped file names based on the current time (but you can also do this with maintenance plans etc., just don't choose any of the shrink options in maintenance plans, they're awful).

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

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

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

Note that \backup_share should be on a different machine that represents a different underlying storage device. Backing these up to the same machine (or to a different machine that uses the same underlying disks, or a different VM that's on the same physical host) does not really help you, since if the machine blows up, you've lost your database and its backups. Depending on your network infrastructure it may make more sense to backup locally and then transfer them to a different location behind the scenes; in either case, you want to get them off the primary database machine as quickly as possible.

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

Now, once you have regular log backups running, it should be reasonable to shrink the log file to something more reasonable than whatever it's blown up to now. This does not mean running SHRINKFILE over and over again until the log file is 1 MB - even if you are backing up the log frequently, it still needs to accommodate the sum of any concurrent transactions that can occur. Log file autogrow events are expensive, since SQL Server has to zero out the files (unlike data files when instant file initialization is enabled), and user transactions have to wait while this happens. You want to do this grow-shrink-grow-shrink routine as little as possible, and you certainly don't want to make your users pay for it.

请注意，您可能需要将日志备份两次才能进行收缩(感谢 Robert).

Note that you may need to back up the log twice before a shrink is possible (thanks Robert).

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

So, you need to come up with a practical size for your log file. Nobody here can tell you what that is without knowing a lot more about your system, but if you've been frequently shrinking the log file and it has been growing again, a good watermark is probably 10-50% higher than the largest it's been. Let's say that comes to 200 MB, and you want any subsequent autogrowth events to be 50 MB, then you can adjust the log file size this way:

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

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

Note that if the log file is currently > 200 MB, you may need to run this first:

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

如果您不关心时间点恢复

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

ALTER DATABASE testdb SET RECOVERY SIMPLE;

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

Putting the database in SIMPLE recovery mode will make sure that SQL Server re-uses portions of the log file (essentially phasing out inactive transactions) instead of growing to keep a record of all transactions (like FULL recovery does until you back up the log). CHECKPOINT events will help control the log and make sure that it doesn't need to grow unless you generate a lot of t-log activity between CHECKPOINTs.

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

Next, you should make absolute sure that this log growth was truly due to an abnormal event (say, an annual spring cleaning or rebuilding your biggest indexes), and not due to normal, everyday usage. If you shrink the log file to a ridiculously small size, and SQL Server just has to grow it again to accommodate your normal activity, what did you gain? Were you able to make use of that disk space you freed up only temporarily? If you need an immediate fix, then you can run the following:

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

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

Otherwise, set an appropriate size and growth rate. As per the example in the point-in-time recovery case, you can use the same code and logic to determine what file size is appropriate and set reasonable autogrowth parameters.

• 使用TRUNCATE_ONLY 选项和SHRINKFILE 备份日志.一方面，此 TRUNCATE_ONLY 选项已被弃用，并且在当前版本的 SQL Server 中不再可用.其次，如果您处于 FULL 恢复模式，这将破坏您的日志链并需要一个新的完整备份.

• Back up the log with TRUNCATE_ONLY option and then SHRINKFILE. For one, this TRUNCATE_ONLY option has been deprecated and is no longer available in current versions of SQL Server. Second, if you are in FULL recovery model, this will destroy your log chain and require a new, full backup.

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

Detach the database, delete the log file, and re-attach. I can't emphasize how dangerous this can be. Your database may not come back up, it may come up as suspect, you may have to revert to a backup (if you have one), etc. etc.

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

Use the "shrink database" option. DBCC SHRINKDATABASE and the maintenance plan option to do the same are bad ideas, especially if you really only need to resolve a log problem issue. Target the file you want to adjust and adjust it independently, using DBCC SHRINKFILE or ALTER DATABASE ... MODIFY FILE (examples above).

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

Shrink the log file to 1 MB. This looks tempting because, hey, SQL Server will let me do it in certain scenarios, and look at all the space it frees! Unless your database is read only (and it is, you should mark it as such using ALTER DATABASE), this will absolutely just lead to many unnecessary growth events, as the log has to accommodate current transactions regardless of the recovery model. What is the point of freeing up that space temporarily, just so SQL Server can take it back slowly and painfully?

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

Create a second log file. This will provide temporarily relief for the drive that has filled your disk, but this is like trying to fix a punctured lung with a band-aid. You should deal with the problematic log file directly instead of just adding another potential problem. Other than redirecting some transaction log activity to a different drive, a second log file really does nothing for you (unlike a second data file), since only one of the files can ever be used at a time. Paul Randal also explains why multiple log files can bite you later.

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

Instead of shrinking your log file to some small amount and letting it constantly autogrow at a small rate on its own, set it to some reasonably large size (one that will accommodate the sum of your largest set of concurrent transactions) and set a reasonable autogrow setting as a fallback, so that it doesn't have to grow multiple times to satisfy single transactions and so that it will be relatively rare for it to ever have to grow during normal business operations.

此处最糟糕的设置是 1 MB 增长或 10% 增长.有趣的是，这些是 SQL Server 的默认设置(我曾抱怨过，要求更改无效) - 1 MB 用于数据文件，10% 用于日志文件.前者在当今时代太小了，而后者每次都会导致越来越长的事件(例如，您的日志文件是 500 MB，第一次增长是 50 MB，下一次增长是 55 MB，下一次增长是 60.5 MB等等 - 在慢速 I/O 上，相信我，你会真正注意到这条曲线).

The worst possible settings here are 1 MB growth or 10% growth. Funny enough, these are the defaults for SQL Server (which I've complained about and asked for changes to no avail) - 1 MB for data files, and 10% for log files. The former is much too small in this day and age, and the latter leads to longer and longer events every time (say, your log file is 500 MB, first growth is 50 MB, next growth is 55 MB, next growth is 60.5 MB, etc. etc. - and on slow I/O, believe me, you will really notice this curve).

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

Please don't stop here; while much of the advice you see out there about shrinking log files is inherently bad and even potentially disastrous, there are some people who care more about data integrity than freeing up disk space.

我在 2009 年写的一篇博文，当时我看到一些这是如何缩小日志文件"的帖子出现.

博客文章 Brent Ozar四年前写的，指向多个资源，回应 SQL Server Magazine 一篇不不应该发表的文章.

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.

Paul Randal 的一篇博文解释为什么 t-log 维护很重要 和 为什么您不应该缩小您的数据文件.

迈克沃尔什有一个很好的答案也涵盖了其中一些方面，包括您可能无法立即缩小日志文件的原因.

这篇关于如何清除 SQL Server 事务日志?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！