如何清除SQL Server事务日志？

素染倾城色 2024-07-12 04:47:00

缩小日志文件实际上应该保留用于遇到意外增长且您不希望再次发生的情况。如果日志文件将再次增长到相同的大小，那么暂时缩小它并没有多大作用。现在，根据数据库的恢复目标，您应该采取以下操作。

首先，进行完整备份

切勿对数据库进行任何更改，除非确保出现问题时可以恢复数据库。

如果您关心时间点恢复

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

大概您的数据库处于 FULL< /code> 恢复模式。如果没有，请确保：

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% 。假设达到 200 MB，并且您希望任何后续自动增长事件为 50 MB，那么您可以通过这种方式调整日志文件大小（其中 '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

如果您不关心时间点恢复

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

ALTER DATABASE testdb SET RECOVERY SIMPLE;

将数据库置于SIMPLE 恢复模式将确保 SQL Server 重新使用日志文件的部分内容（实质上是逐步淘汰不活动的事务），而不是不断增长以保留全部记录。 > 事务（就像 FULL 恢复一样，直到您备份日志）。 CHECKPOINT 事件将有助于控制日志并确保日志不需要增长，除非您在 CHECKPOINT 之间生成大量 t-log 活动。

接下来，您应该绝对确保此日志增长确实是由于异常事件（例如，年度春季大扫除或重建最大索引）造成的，而不是由于正常的日常使用造成的。如果你将日志文件缩小到小得离谱，而 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（上面的示例）定位要调整的文件并独立调整它。
将日志文件缩小至 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 上，相信我，你真的会注意到这条曲线）。

进一步阅读

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

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.

First, take a full backup

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

If you care about point-in-time recovery

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

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

ALTER DATABASE testdb SET RECOVERY FULL;

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

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

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.

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.

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

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 (where 'yourdb_log' is the logical name of your database's log file, which you can find in sys.database_files):

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

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

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

If you don't care about point-in-time recovery

If this is a test database, and you don't care about point-in-time recovery, then you should make sure that your database is in SIMPLE recovery mode.

ALTER DATABASE testdb SET RECOVERY SIMPLE;

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.

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;
CHECKPOINT; -- run twice to ensure file wrap-around
GO
DBCC SHRINKFILE(N'yourdb_log', 200); -- unit is MB
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.

Some things you don't want to do

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

Be proactive

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.

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

我们建议您永远不要使用 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)

Most answers here so far are assuming you do not actually need the Transaction Log file, however if your database is using the FULL recovery model, and you want to keep your backups in case you need to restore the database, then do not truncate or delete the log file the way many of these answers suggest.

Eliminating the log file (through truncating it, discarding it, erasing it, etc) will break your backup chain, and will prevent you from restoring to any point in time since your last full, differential, or transaction log backup, until the next full or differential backup is made.

From the Microsoft article onBACKUP

We recommend that you never use NO_LOG or TRUNCATE_ONLY to manually
truncate the transaction log, because this breaks the log chain. Until
the next full or differential database backup, the database is not
protected from media failure. Use manual log truncation in only very
special circumstances, and create backups of the data immediately.

To avoid that, backup your log file to disk before shrinking it. The syntax would look something like this:

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

DBCC SHRINKFILE (N'MyDatabaseName_Log', 200)

如何清除SQL Server事务日志？

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（21）

首先，进行完整备份

如果您关心时间点恢复

如果您不关心时间点恢复

有些您不想做的事情

积极主动

进一步阅读

First, take a full backup

If you care about point-in-time recovery

If you don't care about point-in-time recovery

Some things you don't want to do

Be proactive

Further reading

关于作者

相关话题

热门标签

推荐作者

1CH1MKgiKxn9p

ゞ记忆︶ㄣ

JackDx

信远

yaoduoduo1995

霞映澄塘

友情链接

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。