什么时候应该对 SQL Server 中的日志文件使用自动收缩？

When should one use auto shrink on log files in SQL Server?

提问by bjorsig

I have had a few problems with log files growing too big on my SQL Servers (2000). Microsoft doesn't recommend using auto shrink for log files, but since it is a feature it must be useful in some scenarios. Does anyone know when is proper to use the auto shrink property?

我的 SQL Servers (2000) 上的日志文件变得太大时遇到了一些问题。Microsoft 不建议对日志文件使用自动收缩，但由于它是一项功能，因此在某些情况下必须很有用。有谁知道什么时候适合使用自动收缩属性？

回答by HLGEM

Your problem is not that you need to autoshrink periodically but that you need to backup the log files periodically. (We back ours up every 15 minutes.) Backing up the database itself is not sufficient, you must do the log as well. If you do not back up the transaction log, it will grow until it takes up all the space on the drive. If you back it up, it frees the space to be reused (you will still probably need to shrink after the first backup to get the log down to a more reasonable size). If you don't need to be able torecover from transactions (which you should need to be able to do unless your entire database consists of tables that are loaded from another source and can easily be re-loaded.), then set your log to simlpe recovery mode.

您的问题不是您需要定期自动收缩，而是您需要定期备份日志文件。（我们每 15 分钟备份一次。）备份数据库本身是不够的，您还必须做日志。如果不备份事务日志，它会一直增长，直到占用驱动器上的所有空间。如果您备份它，它会释放空间以供重用（您可能仍需要在第一次备份后缩小以将日志缩小到更合理的大小）。如果您不需要能够从事务中恢复（您应该能够这样做，除非您的整个数据库包含从另一个源加载并且可以轻松重新加载的表。），然后将您的日志设置为简单的恢复模式。

One reason why autoshrinking isn't so good an idea is that you will be growing the transaction log frequently which slows down performance. IF you back up the log, one you get to a relatively stable size (the amount of space normally used by the transaction log in the time period between backups), then the log will only need to grow occasionally if there are an unusually heavy amount fo transactions.

自动收缩不是一个好主意的一个原因是您会频繁地增加事务日志，这会降低性能。如果你备份日志，你得到一个相对稳定的大小（事务日志在备份之间的时间段内通常使用的空间量），那么日志只需要在异常大量的情况下偶尔增长交易。

回答by Jim McLeod

My take on this is that auto-shrink is useful when you have many fairly small databases that frequently get larger due to added data, and then have a lot of empty space afterwards. You also need to not mind that the files will be fragmented on the disk when they frequently grow and shrink. I'd never use auto-shrink on a critical database or one larger than 2 GB, as you never know when the shrink operation will kick in, and access to the database will be blocked until the shrink has completed.

我对此的看法是，当您有许多相当小的数据库，这些数据库由于添加数据而经常变大时，自动收缩很有用，然后有很多空白空间。您还需要注意文件在频繁增长和收缩时会在磁盘上产生碎片。我永远不会在关键数据库或大于 2 GB 的数据库上使用自动收缩，因为您永远不知道收缩操作何时开始，并且在收缩完成之前对数据库的访问将被阻止。

回答by Alex Angas

You should never have autoshrink turned on. It causes performance degradation in several ways. The file-system and indexes become fragmented and it is very resource intensive. It is also not necessary if you manage your backups correctly.

你永远不应该打开自动收缩。它以多种方式导致性能下降。文件系统和索引变得碎片化并且非常占用资源。如果您正确管理备份，也没有必要。

Read this answer from Paul Randal on Server Faultand Just Say No To Auto-Shrink!!

阅读 Paul Randal 关于服务器故障的答案，然后对自动收缩说不！

回答by Charles Graham

I used to use it when we had a demo version of a huge database that took up a lot of space on the laptop, so we used it to keep the size down.

当我们有一个巨大的数据库的演示版本时，我曾经使用它占用了笔记本电脑上的大量空间，因此我们使用它来减小大小。

The key is to use it only when the data is basically throw away.

关键是数据基本扔掉的时候才用。

You should truncate the logs periodically as a part of your backup strategy.

作为备份策略的一部分，您应该定期截断日志。