如何管理SQL Server日志大小 [英] How Can I Manage SQL Server Log Size

问题描述

我正在尝试管理SQL Server 2008日志文件的大小.我有一个每天加载一次的报告数据库.最简单的恢复模型是最合适的，因为除了早晨的工作量之外没有其他事务，我可以重新创建这些记录.我的目标是使事务日志具有固定的大小，该大小应足够大以使其在加载期间不必分配新的空间.

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.

我的问题是日志持续增长.我知道应该将日志标记为可重复使用，但是为什么日志会不断增长? log_reuse_wait_desc显示无".

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

我可以将日志文件缩小到1 MB，因此我知道其中没有事务.我可以设置一个工作来执行此操作，但是我希望将日志保留为500MB，足以应付日常负载.如果执行此操作，日志将增加.

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.

推荐答案

即使数据库处于简单恢复"模式，日志文件也用于事务目的.如果LOG的大小确实增长到超过500 MB，则说明有人正在运行需要该空间的查询或存储过程.

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.

因此，如果日志大小从1MB开始并增加到700MB，则说明正在做一些需要该空间的事情.如果将大小锁定为500MB，最终将收到日志文件空间不足"错误.

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.

但是，如果您确实希望将其修复为500MB，则可以执行以下操作: (我正在使用SQL Server 2005)

But if you really want to fix it at 500MB, you can do the following: (I'm using SQL Server 2005)

1. 启动Microsoft SQL Server Management Studio
2. 找到您的数据库，然后右键单击它.选择属性.
3. 单击文件"部分
4. 找到日志文件"行.
5. 将初始大小"更改为:500
6. 找到自动增长"部分，然后单击椭圆(...)
7. 取消选中启用自动增长".点击确定.
8. 单击确定"进行更改.

注意:您还可以在自动增长部分"中设置最大日志文件大小.

Note: You can also set a maximum log file size in the "autogrowth section".

或者，您可以使用以下脚本进行更改.用适当的值替换DATABASENAME.如果需要，还可以更改日志文件名.

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.

USE [master]
GO
ALTER DATABASE [DatabaseName] MODIFY FILE ( NAME = N'DATABASENAME_Log', SIZE = 512000KB , FILEGROWTH = 0)
GO

这篇关于如何管理SQL Server日志大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！