数据库的事务日志已满 [英] The transaction log for the database is full

问题描述

我有一个运行时间很长的流程，可以在整个期间内保持打开事务。

我无法控制它的执行方式。

由于事务在整个持续时间内保持打开状态，因此当事务日志填满时，SQL Server无法增加日志文件的大小。

因此该过程失败，并显示错误数据库'xxx'的事务日志已满。。

我试图通过增加数据库属性中事务日志文件的大小来防止这种情况，但是我遇到了同样的错误。

不确定下一步该怎么做。该过程要花费几个小时，因此反复试验并不容易。

有什么想法吗？

如果有人感兴趣，该过程就是将组织导入 Microsoft Dynamics CRM 4.0。

有足够的磁盘空间，我们以简单的日志记录模式记录了日志，并且之前已经备份了该日志

-=-=-=-=-更新-=-=-=-=-

谢谢大家的评论。以下是使我相信由于未完成交易而导致日志不会增长的原因：

我遇到以下错误...

 导入组织（名称= xxx，ID = 560d04e7-98ed-e211-9759-0050569d6d39）失败，但出现以下异常：
 System.Data.SqlClient .SqlException：数据库'xxx'的事务日志已满。要找出为什么无法重用日志中的空间的原因，请参见sys.databases 
  

So中的log_reuse_wait_desc列根据该建议，我转到 sys.databases中的 log_reuse_wait_desc列，它的值为 ACTIVE_TRANSACTION 。 / p>

根据Microsoft：
http://msdn.microsoft.com/zh-CN/library/ms345414（v = sql.105）.aspx

这意味着：

事务处于活动状态（所有恢复模型）。
•日志备份开始时可能存在长时间运行的事务。在这种情况下，释放空间可能需要另一个日志备份。有关更多信息，请参见本主题后面的长期运行的事务。

•事务被推迟（仅适用于SQL Server 2005 Enterprise Edition和更高版本）。延迟事务实际上是一个活动事务，由于某些不可用的资源，其回滚被阻止。有关延迟事务的原因以及如何将其移出延迟状态的信息，请参阅延迟事务。

我误解了吗？

-=-=-=-更新2-=-=-= -

启动该过程时，初始日志文件大小设置为30GB。这将需要几个小时才能完成。

-=-=-=-最终更新-=-=-=-

问题实际上是由日志文件占用了所有可用磁盘空间引起的。在上一次尝试中，我释放了120GB的内存，但它仍然使用了所有内存，但最终失败了。

我之前并没有意识到这种情况的发生，因为该进程在一夜之间运行，它在失败时回滚。这次我能够在回滚之前检查日志文件的大小。

感谢您的输入。

解决方案

这是一次性脚本还是定期进行的工作？

过去，对于临时需要大量资源的特殊项目日志文件的空间，我创建了第二个日志文件并使其很大。项目完成后，我们将删除多余的日志文件。

I have a long running process that holds open a transaction for the full duration.

I have no control over the way this is executed.

Because a transaction is held open for the full duration, when the transaction log fills, SQL Server cannot increase the size of the log file.

So the process fails with the error "The transaction log for database 'xxx' is full".

I have attempted to prevent this by increasing the size of the transaction log file in the database properties, but I get the same error.

Not sure what I should try next. The process runs for several hours so it's not easy to play trial and error.

Any ideas?

If anyone is interested, the process is an organisation import in Microsoft Dynamics CRM 4.0.

There is plenty of disk space, we have the log in simple logging mode and have backed up the log prior to kicking off the process.

-=-=-=-=- UPDATE -=-=-=-=-

Thanks all for the comments so far. The following is what led me to believe that the log would not grow due to the open transaction:

I am getting the following error...

Import Organization (Name=xxx, Id=560d04e7-98ed-e211-9759-0050569d6d39) failed with Exception:
System.Data.SqlClient.SqlException: The transaction log for database 'xxx' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

So following that advice I went to "log_reuse_wait_desc column in sys.databases" and it held the value "ACTIVE_TRANSACTION".

According to Microsoft: http://msdn.microsoft.com/en-us/library/ms345414(v=sql.105).aspx

That means the following:

A transaction is active (all recovery models). • A long-running transaction might exist at the start of the log backup. In this case, freeing the space might require another log backup. For more information, see "Long-Running Active Transactions," later in this topic.

• A transaction is deferred (SQL Server 2005 Enterprise Edition and later versions only). A deferred transaction is effectively an active transaction whose rollback is blocked because of some unavailable resource. For information about the causes of deferred transactions and how to move them out of the deferred state, see Deferred Transactions.

Have I misunderstood something?

-=-=-=- UPDATE 2 -=-=-=-

Just kicked off the process with initial log file size set to 30GB. This will take a couple of hours to complete.

-=-=-=- Final UPDATE -=-=-=-

The issue was actually caused by the log file consuming all available disk space. In the last attempt I freed up 120GB and it still used all of it and ultimately failed.

I didn't realise this was happening previously because when the process was running overnight, it was rolling back on failure. This time I was able to check the log file size before the rollback.

Thanks all for your input.

解决方案

Is this a one time script, or regularly occurring job?

In the past, for special projects that temporarily require lots of space for the log file, I created a second log file and made it huge. Once the project is complete we then removed the extra log file.

这篇关于数据库的事务日志已满的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！