如何管理 SQL Server 日志大小
我正在尝试管理 SQL Server 2008 日志文件的大小。 我有一个每天加载一次的报告数据库。 简单恢复模型是最适合的，因为除了早上的负载之外没有任何事务，并且我可以重新创建这些记录。 我的目标是使事务日志具有固定大小，足够大，以便在加载期间不必分配新空间。
我的问题是日志不断增长。 我知道日志应该被标记为可重复使用，但为什么日志不断增长？ log_reuse_wait_desc 显示“无”。
我可以将日志文件缩小到 1 MB，因此我知道其中没有事务。 我可以设置一个作业来执行此操作，但我更愿意将日志保留在 500MB，这足以处理日常负载。 如果我这样做，日志就会增长。
如何使日志大小保持一致？
请注意：缩小日志文件并不能解决此问题。 我知道该怎么做。 我正在尝试找到一种方法来重新使用事务日志中的空间。
I'm trying to manage the size of a SQL Server 2008 log file. I have a reporting database that is loaded once a day. The Simple recovery model is the best fit as there are no transactions other than the morning load, and I can re-create those records. My goals are to have the transaction log at a fixed size, large enough that it doesn't have to allocate new space during the load.
My problem is that the log keeps growing. I know that the log is supposed to be marked for re-use, but why does the log keep growing? The log_reuse_wait_desc shows "Nothing".
I can shrink the log file to 1 MB, so I know there are no transactions in it. I can set up a job to do this, but I would prefer to leave the log at 500MB, which is enough to handle the daily loads. If I do this, the log grows.
How can I keep the log at a consistent size?
Please note: Shrinking the log file does not address this issue. I know how to do that. I'm trying to find a way for space in the transaction log to be re-used.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
即使数据库处于简单恢复模式，日志文件也用于事务目的。 如果 LOG 大小确实增长到超过 500 MB，则说明有人正在运行需要该空间的查询或存储过程。
例如，也许您正在针对报告表创建索引。 这将在事务内完成，以便在发生错误时可以回滚更改。 但是，一旦完成，已使用的空间将被释放以供其他事务使用。
因此，如果日志大小从 1MB 开始并逐渐增加到 700MB，则说明正在执行的操作需要该空间。 如果将大小锁定为 500MB，您最终将收到“日志文件空间不足”错误。
但如果您确实想将其修复为 500MB，您可以执行以下操作：
（我使用的是 SQL Server 2005）
注意：您还可以在“自动增长部分”中设置最大日志文件大小。
或者，您可以使用以下脚本进行更改。 将 DATABASENAME 替换为适当的值。 如果需要，还可以更改日志文件名。
The log file is used for transactional purposes, even if the database is in SIMPLE RECOVERY MODE. If the LOG size is truly growing beyond 500 MB, then someone is running a query or stored procedure that is requiring that space.
For example, perhaps you are creating indexes against your reporting tables. That will be done inside a transaction so that the changes can be rolled back in case of an error. However, the used space will be released afterward for other transactions once complete.
So, if the log size is starting at 1MB and increasing to say, 700MB, then something is being done that requires that space. If you lock the size to 500MB, you will eventually receive a "log file out of space" error.
But if you really want to fix it at 500MB, you can do the following:
(I'm using SQL Server 2005)
Note: You can also set a maximum log file size in the "autogrowth section".
Alternatively, you can use the following script to make the change. Replace DATABASENAME with the appropriate value. Also change the Log File Name if required.
创建备份数据库并收缩日志的维护作业
create a maintenance job that backups the DB and shrinks the log
DBCC Shrinkfile 命令允许您指定目标大小：
将文件 DataFile1 缩小到 7MB
文档位于：
The DBCC Shrinkfile command allows you to specify a target size:
Shrinks the file DataFile1 to 7MB
Document at: MSDN
在我看来，您可以使用 DBCC SHRINKFILE 来执行此操作。
Off the top of my head you can use DBCC SHRINKFILE to do this.