SQL Server 2008 - 缩小事务日志 - 有什么方法可以自动化？

SQL Server 2008 - Shrinking the Transaction Log - Any way to automate?

提问by Albert

I went in and checked my Transaction log the other day and it was something crazy like 15GB. I ran the following code:

前几天我进去检查了我的交易日志，它像 15GB 一样疯狂。我运行了以下代码：

USE mydb
GO
BACKUP LOG mydb WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE(mydb_log,8)
GO

Which worked fine, shrank it down to 8MB...but the DB in question is a Log Shipping Publisher, and the log is already back up to some 500MB and growing quick.

效果很好，将其缩小到 8MB……但有问题的数据库是 Log Shipping Publisher，并且日志已经备份到大约 500MB 并且增长很快。

Is there any way to automate this log shrinking, outside of creating a custom "Execute T-SQL Statement Task" Maintenance Plan Task, and hooking it on to my log backup task? If that's the best way then fine...but I was just thinking that SQL Server would have a better way of dealing with this. I thought it was supposed to shrink automatically whenever you took a log backup, but that's not happening (perhaps because of my log shipping, I don't know).

除了创建自定义的“执行 T-SQL 语句任务”维护计划任务并将其挂接到我的日志备份任务之外，是否有任何方法可以自动执行此日志收缩？如果那是最好的方法，那很好……但我只是在想 SQL Server 会有更好的方法来处理这个问题。我认为它应该在您进行日志备份时自动缩小，但这并没有发生（也许是因为我的日志传送，我不知道）。

Here's my current backup plan:

这是我目前的备份计划：

• Full backups every night
• Transaction log backups once a day, late morning (maybe hook the Log shrinking onto this...doesn't need to be shrank every day though)

• 每晚完整备份
• 每天早上备份一次事务日志（也许将日志缩小到这个上......虽然不需要每天都缩小）

Or maybe I just run it once a week, after I run a full backup task? What do you all think?

或者也许我只是在运行完整备份任务后每周运行一次？大家怎么看？

回答by Remus Rusanu

If you file grows every night at 500 MB there is only one correct action: pre-grow the file to 500MB and leave it there. Shrinking the log file is damaging. Having the log file auto-grow is also damaging.

如果您的文件每晚都以 500 MB的速度增长，那么只有一个正确的操作：将文件预先增大到500 MB并保留在那里。缩小日志文件是有害的。让日志文件自动增长也是有害的。

• you hit the file growth zero fill initialization during normal operations, reducing performance
• your log grows in small increments creating many virtual log files, resulting in poorer operational performance
• your log gets fragmented during shrinkage. While not as bad as a data file fragmentation, log file fragmentation still impact performance
• one day the daily growth of 500MB will run out of disk space and you'd wish the file was pre-grown

• 您在正常操作期间达到了文件增长零填充初始化，从而降低了性能
• 您的日志以小增量增长，从而创建许多虚拟日志文件，从而导致操作性能变差
• 您的日志在收缩期间会变得碎片化。虽然不像数据文件碎片那么糟糕，但日志文件碎片仍然会影响性能
• 有一天，每天 500MB 的增长将耗尽磁盘空间，而您希望该文件是预先增长的

You don't have to take my word for it, you can read on some of the MVP blogs what they have to say about the practice of log and file shrinkage on a regular basis:

你不必相信我的话，你可以在一些 MVP 博客上阅读他们对定期日志和文件收缩实践的看法：

• Auto-shrink – turn it OFF!
• Oh, the horror! Please stop telling people they should shrink their log files!
• Why you want to be restrictive with shrink of database files
• Don't Touch that Shrink Button!
• Do not truncate your ldf files!

• 自动收缩 - 关闭它！
• 哦，恐怖！请停止告诉人们他们应该缩小他们的日志文件！
• 为什么要限制数据库文件的收缩
• 不要碰那个收缩按钮！
• 不要截断你的 ldf 文件！

There are more, I just got tired of linking them.

还有更多，我只是厌倦了将它们联系起来。

Every time you shrink a log file, a fairy loses her wings.

每次缩小日志文件时，仙女都会失去她的翅膀。

回答by Cade Roux

I'd think more frequent transaction log backups.

我想更频繁的事务日志备份。

回答by Joe L.

I think what you suggest in your question is the right approach. That is, "hook the Log shrinking onto" your nightly backup/maintenance task process. The main thing is that you are regularly doing transaction log backups, which will allow the database to be shrunk when you do the shrink task. The key thing to keep in mind is that this is a two-step process: 1) backup your transaction log, which automatically "truncates" your log file; 2) run a shrink against your log file. "truncate" doesn't necessarily (or ever?) mean that the file will shrink...shrinking it is a separate step you must do.

我认为您在问题中建议的是正确的方法。也就是说，“将日志缩小到”您的夜间备份/维护任务流程。主要是您定期进行事务日志备份，这将允许您在执行收缩任务时收缩数据库。要记住的关键是，这是一个两步过程：1) 备份您的事务日志，它会自动“截断”您的日志文件；2）对您的日志文件运行收缩。“截断”并不一定（或永远？）意味着文件会缩小...缩小它是您必须执行的单独步骤。

回答by Mehmet Sahin

for SQL Server 2005

对于 SQL Server 2005

DBCC SHRINKFILE ( Database_log_file_name , NOTRUNCATE)

DBCC SHRINKFILE ( Database_log_file_name , NOTRUNCATE)

This statement don't break log shipping. But, you may need to run more than one. For each run, the log shipping backup, copy, and restored to run after again run this statement.

此语句不会破坏日志传送。但是，您可能需要运行多个。对于每次运行，日志传送备份、复制和恢复运行后再次运行此语句。

Shrink and truncate are different.

收缩和截断是不同的。

My experiences:

我的经历：

AA db, 6.8GB transaction log
first run: 6.8 GB
log shipping backup, copy, restore after second run: 1.9 GB
log shipping backup, copy, restore after third run: 1.7 GB
log shipping backup, copy, restore after fourth run: 1 GB

AA db，6.8GB 事务日志
第一次运行：6.8 GB
日志传送备份、复制、第二次运行后恢复：1.9 GB
日志传送备份、复制、第三次运行后恢复：1.7 GB
日志传送备份、复制、第四次运行后恢复：1国标

BB db, 50GB transaction log
first run: 39 GB
log shipping backup, copy, restore after second run: 1 GB

BB db，50GB 事务日志
首次运行：39 GB
日志传送备份、复制、第二次运行后恢复：1 GB

回答by Milena Petrovic

Creating a transaction log backup doesn't mean that the online transaction log file size will be reduced. The file size remains the same. When a transaction is backuped up, in the online transaction log it's marked for overwriting. It;s not automatically removed, and no spaces is freed, therefore, the size remains the same.

创建事务日志备份并不意味着联机事务日志文件的大小会减少。文件大小保持不变。备份事务后，在联机事务日志中将其标记为覆盖。它不会自动删除，也不会释放任何空间，因此大小保持不变。

Once you set the LDF file size, maintain its size by setting the right transaction log backup frequency.

设置 LDF 文件大小后，请通过设置正确的事务日志备份频率来保持其大小。

Paul Randal provides details here:

Paul Randal 在这里提供了详细信息：

Understanding Logging and Recovery in SQL Server

了解 SQL Server 中的日志记录和恢复

Understanding SQL Server Backups

了解 SQL Server 备份

回答by Mohamed El-Qassas MVP

Based on Microsoft recommendation Before you intend to Shrink log file you should first try to perform the following capabilities:

基于 Microsoft 的建议，在您打算收缩日志文件之前，您应该首先尝试执行以下功能：

• Freeing disk space so that the log can automatically grow.
• Moving the log file to a disk drive with sufficient space.
• Increasing the size of a log file.
• Adding a log file on a different disk.

• Turn on auto growth by using the ALTER DATABASE statement to set a non-zero growth increment for the FILEGROWTH option.

  ALTER DATABASE EmployeeDB MODIFY FILE ( NAME = SharePoint_Config_log, SIZE = 2MB, MAXSIZE = 200MB, FILEGROWTH = 10MB );

• 释放磁盘空间，以便日志可以自动增长。
• 将日志文件移动到具有足够空间的磁盘驱动器。
• 增加日志文件的大小。
• 在不同的磁盘上添加日志文件。

• 通过使用 ALTER DATABASE 语句为 FILEGROWTH 选项设置非零增长增量来打开自动增长。

  ALTER DATABASE EmployeeDB MODIFY FILE (NAME = SharePoint_Config_log, SIZE = 2MB, MAXSIZE = 200MB, FILEGROWTH = 10MB );

Also, you should be aware of shrink operation via maintenance plan will effect on *.mdf file and *.ldf file. so you need to create a maintenance plan with SQL job task and write the following command to can only shrink *.ldf file to your appropriate target size.

此外，您应该知道通过维护计划进行的收缩操作会影响 *.mdf 文件和 *.ldf 文件。所以你需要用 SQL 作业任务创建一个维护计划，并编写以下命令，只能将 *.ldf 文件缩小到合适的目标大小。

use sharepoint_config
go
alter database sharepoint_config set recovery simple
go
dbcc shrinkfile('SharePoint_Config_log',100)
go
alter database sharepoint_config set recovery FUll
go

Note: 100 is called the target_size for the file in megabytes, expressed as an integer. If not specified, DBCC SHRINKFILE reduces the size to the default file size. The default size is the size specified when the file was created.

注意：100 称为文件的 target_size，以兆字节为单位，表示为整数。如果未指定，DBCC SHRINKFILE 会将大小减小到默认文件大小。默认大小是创建文件时指定的大小。

In my humble opinion, It's not recommended to perform the shrink operation periodically! Only in some circumstances that you need to reduce the physical size.

以我的拙见，不建议定期执行收缩操作！仅在某些情况下您需要减小物理尺寸。

You can also check this useful guide to Shrink a transaction log file Maintenance Plan in SQL Server

您还可以查看这个有用的指南来缩小 SQL Server 中的事务日志文件维护计划