如何清除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;

即使您要进行定期的完整备份，日志文件也会不断增长，直到执行 log 备份为止-这是为了保护您，而不是不必要地吞噬了磁盘空间.根据恢复目标，您应该非常频繁地执行这些日志备份.例如，如果您有一条业务规则规定在发生灾难时可以承受不超过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_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)并不能真正帮助您，因为如果计算机崩溃，您将丢失数据库和其备份.根据您的网络基础架构，在本地进行备份，然后将其转移到幕后的其他位置可能更有意义.无论哪种情况，您都希望尽快将它们从主数据库计算机中移出.

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必须将文件归零(与启用即时文件初始化时的数据文件不同)，并且用户事务必须等待这种情况发生.您希望尽可能少地执行此Growth-shrink-grow-shrink例程，并且您当然不想让您的用户为此付费.

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重用日志文件的某些部分(基本上淘汰不活动的事务)，而不是为了保留所有 all 事务的记录(就像FULL恢复一样，直到备份日志为止). CHECKPOINT事件将有助于控制日志，并确保它不需要增长，除非您在CHECKPOINT s之间生成大量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的默认值(我一直在抱怨它，

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.

.

博客文章Brent Ozar四年前写的，指向多种资源，以回应《 SQL Server杂志》上应该不已经发表的文章.

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维护很重要和 Mike Walsh有一个很好的答案也涵盖了这些方面，包括为什么您可能无法立即缩小日志文件的原因.

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