Sunday, September 23, 2007
Search Engine Q&A #1: Running out of transaction log space

One of the great things about the blog engine we use is that it shows all the search engine queries that led to someone clicking through to the site. I've been looking through the logs to see what kind of problems people are having that end up here. In this occasional series, I'm going to pick a search engine query and blog about it - hopefully helping out people who have the problem in future.

First up is running out of transaction log space. This happens when the transaction log fills up to the point where it has to grow but either autogrow is not turned on or the volume on which the transaction log is placed has no more space for the file to grow. So what causes the transaction log to fill up in the first place? It could be a number of different things. The two most common ones I've seen are:

  • The database is in full recovery mode with normal processing, a full database backup has been taken but no log backups have been taken. This commonly happens when an application goes into production and someone decides to take a backup, without realizing the consequences. Log truncation is not automatic in such a case - a log backup is required to allow the log to truncate.
  • The database is in any recovery mode but there's a very long-running, uncommitted transaction that prevents log truncation. (Even in full recovery mode with regular log backups, all the log records for the long-running transaction are required in case it rolls-back - and SQL Server can't selectively truncate log records from the log for some transactions but not others.)

Let's have a look at both of these in SQL Server 2005 and example solutions.

The log backup case...

First thing to do is create a database with a very small transaction log that we can easily fill up.

USE master;
GO
IF DATABASEPROPERTY (N'tinylogtest', 'Version') > 0 DROP DATABASE tinylogtest;
GO

CREATE DATABASE tinylogtest ON (
   NAME = fgt_mdf,
   FILENAME = 'c:\tinylogtest\tinylogtest.mdf',
   SIZE = 2MB)
LOG ON (
   NAME = fgt_log,
   FILENAME = 'c:\tinylogtest\tinylogtest.ldf',
   SIZE = 512KB,
   FILEGROWTH = 0);
GO

Notice that I've specifically turned auto-growth of the log file off by setting FILEGROWTH = 0. Now let's set the database into full recovery mode and take a full database backup.

ALTER DATABASE tinylogtest SET RECOVERY FULL;
GO
BACKUP DATABASE tinylogtest to DISK = 'C:\tinylogtest\dummybackup.bck';
GO

And then create a simple table and fill up the transaction log.

SET NOCOUNT ON;
GO
CREATE TABLE tinylogtest..testtable (c1 INT, c2 CHAR (3000));
GO
WHILE (1=1)
BEGIN
   INSERT INTO tinylogtest..testtable VALUES (1, 'a')
END;

GO

And we get the following error:

Msg 9002, Level 17, State 2, Line 4
The transaction log for database 'tinylogtest' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

This is much better than you get on SQL Server 2000:

Msg 9002, Level 17, State 2, Line 4
The log file for database 'tinylogtest' is full.

This has no helpful information at all. So, looking at the 2005 message, it's telling us to look in the sys.databases table for more info. Let's do that:

SELECT log_reuse_wait_desc FROM sys.databases WHERE [name] = 'tinylogtest';
GO

And the result is:

LOG_BACKUP

That's pretty clear - take a log backup! Here are the various values this can take, from the Books Online entry for sys.databases:

  • NOTHING
  • CHECKPOINT
  • LOG_BACKUP
  • ACTIVE_BACKUP_OR_RESTORE
  • ACTIVE_TRANSACTION
  • DATABASE_MIRRORING
  • REPLICATION
  • DATABASE_SNAPSHOT_CREATION
  • LOG_SCAN
  • OTHER_TRANSIENT

Their meanings are explained in the Books Online entry Factors That Can Delay Log Truncation. Now if we take a log backup, the log can be truncated.

BACKUP LOG tinylogtest to DISK = 'C:\tinylogtest\dummybackup.bck';
GO

And checking the state in sys.databases again returns:

NOTHING

Simple - but it's amazing how many times I see this problem on newsgroups and forums.

The long-running transaction case...

For this test, assume I've run the T-SQL above to create the database, put it into full recovery mode, take the first full backup, and then create the table. Now in a second connection, I'll create a long-running transaction:

BEGIN TRANSACTION PAULSTRAN;
GO

INSERT INTO tinylogtest..testtable VALUES (1, 'a');
GO

Note that the transaction hasn't been committed or rolled back. In the original connection, I'll execute the WHILE loop to fill up the log again. When we check sys.databases we get back:

LOG_BACKUP

So we take a log backup and check the state again. This time we get:

ACTIVE_TRANSACTION

We need to use the DBCC OPENTRAN command to find out what the long running transaction is:

DBCC OPENTRAN ('tinylogtest');
GO

And we get back:

Transaction information for database 'tinylogtest'.

Oldest active transaction:
    SPID (server process ID): 54
    UID (user ID) : -1
    Name            : PAULSTRAN
    LSN               : (18:98:1)
    Start time      : Sep 23 2007  5:49:53:077PM
    SID               : 0x010500000000000515000000ae4da6eadcd59cf661d6bb58ed030000
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

From this info we can track down the transaction and commit it or roll it back. In our example, once we roll back the PAULSTRAN transaction, there's enough log space to insert one more record into the table, but then we run out of log space again. What's going on? Because the PAULSTRAN transaction we very close to the start of the transaction log, all of the log it was effectively holding hostage was backed up but couldn't be cleared. In this case we need to perform another log backup to allow the log to be cleared. This makes sense and is documented behavior.

Summary

Of course, there are other causes of the transaction log filling up and other things you can do apart from taking log backups or identifying long-running transactions - such as growing the log file or moving the log file to a volume with more free space. Here are some good resources you can use for further reading:

Hope this helps!

Example Scripts | Search Engine Q and A | Transaction Log
Sunday, September 23, 2007 4:35:31 PM (Pacific Standard Time, UTC-08:00)  #    Comments [1]  | 
Thursday, January 24, 2008 7:54:12 AM (Pacific Standard Time, UTC-08:00)
Thanks for looking out for we 'seek now that it is broken' searchers.
It may well be just me, but as a developer with seldom need to do anything other than a full backup then a full restore, the log_reuse_wait_desc of LOG_BACKUP actually made it harder for me. I considered 'full' to be an all inclusive backup ie. including a log backup. So when a backup failed, and the reason was backup, I was so happy to be a coder. I will return to learn all your secrets :-)
Errol
Name
E-mail
Home page

Comment (Some html is allowed: a@href@title, strike) where the @ means "attribute." For example, you can use <a href="" title=""> or <blockquote cite="Scott">.  

Enter the code shown (prevents robots):
Live Comment Preview

Theme design by Jelle Druyts

Pick a theme: