Advertisement
Advertisement


The transaction log for the database is full


Question

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.

2014/10/16
1
110
10/16/2014 6:21:48 PM

Accepted Answer

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.

2013/07/16
19
7/16/2013 11:35:01 AM


I had this error once and it ended up being the server's hard drive that run out of disk space.

2014/06/05

Do you have Enable Autogrowth and Unrestricted File Growth both enabled for the log file? You can edit these via SSMS in "Database Properties > Files"

2013/07/16

This is an old school approach, but if you're performing an iterative update or insert operation in SQL, something that runs for a long time, it's a good idea to periodically (programmatically) call "checkpoint". Calling "checkpoint" causes SQL to write to disk all of those memory-only changes (dirty pages, they're called) and items stored in the transaction log. This has the effect of cleaning out your transaction log periodically, thus preventing problems like the one described.

2013/07/16

The following will truncate the log.

USE [yourdbname] 
GO

-- TRUNCATE TRANSACTION LOG --
DBCC SHRINKFILE(yourdbname_log, 1)
BACKUP LOG yourdbname WITH TRUNCATE_ONLY
DBCC SHRINKFILE(yourdbname_log, 1)
GO

-- CHECK DATABASE HEALTH --
ALTER FUNCTION [dbo].[checker]() RETURNS int AS BEGIN  RETURN 0 END
GO
2014/04/05

If your database recovery model is full and you didn't have a log backup maintenance plan, you will get this error because the transaction log becomes full due to LOG_BACKUP.

This will prevent any action on this database (e.g. shrink), and the SQL Server Database Engine will raise a 9002 error.

To overcome this behavior I advise you to check this The transaction log for database ‘SharePoint_Config’ is full due to LOG_BACKUP that shows detailed steps to solve the issue.

2016/07/25