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 Server（2000）日志文件变得太大，我遇到了一些问题。 Microsoft不建议对日志文件使用自动收缩，但由于它是一项功能，因此在某些情况下必须有用。有谁知道什么时候适合使用自动收缩属性？
4 个解决方案
#1
11
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分钟支持一次。）备份数据库本身是不够的，你也必须做日志。如果不备份事务日志，它将会增长，直到它占用驱动器上的所有空间。如果备份它，它会释放要重用的空间（在第一次备份后，您仍可能需要缩小以使日志记录更合理）。如果您不需要能够从事务中恢复（除非您的整个数据库包含从其他源加载并且可以轻松重新加载的表，否则您需要能够执行此事务），然后将您的日志设置为simlpe恢复模式。
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.
自动收缩不是一个好主意的一个原因是，您将经常增加事务日志，这会降低性能。如果你备份日志，你可以获得一个相对稳定的大小（在备份之间的时间段内事务日志通常使用的空间量），那么如果异常繁重，日志只需要偶尔增长交易。
#2
3
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的数据库上使用自动收缩，因为您永远不知道收缩操作何时启动，并且在收缩完成之前将阻止对数据库的访问。
#3
2
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 Fault and Just Say No To Auto-Shrink!!
阅读Paul Randal关于服务器故障的答案，并且说不要自动收缩!!
#4
0
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.
您应该定期截断日志作为备份策略的一部分。
#1
11
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分钟支持一次。）备份数据库本身是不够的，你也必须做日志。如果不备份事务日志，它将会增长，直到它占用驱动器上的所有空间。如果备份它，它会释放要重用的空间（在第一次备份后，您仍可能需要缩小以使日志记录更合理）。如果您不需要能够从事务中恢复（除非您的整个数据库包含从其他源加载并且可以轻松重新加载的表，否则您需要能够执行此事务），然后将您的日志设置为simlpe恢复模式。
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.
自动收缩不是一个好主意的一个原因是，您将经常增加事务日志，这会降低性能。如果你备份日志，你可以获得一个相对稳定的大小（在备份之间的时间段内事务日志通常使用的空间量），那么如果异常繁重，日志只需要偶尔增长交易。
#2
3
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的数据库上使用自动收缩，因为您永远不知道收缩操作何时启动，并且在收缩完成之前将阻止对数据库的访问。
#3
2
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 Fault and Just Say No To Auto-Shrink!!
阅读Paul Randal关于服务器故障的答案，并且说不要自动收缩!!
#4
0
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.
您应该定期截断日志作为备份策略的一部分。