More about database maintenance

Managing the Transaction Log

Overview

Note: please refer to SQL Server Books Online for more information regarding this topic.

All changes made to a database are recorded in the transaction log.

This information is used:

The log will grow indefinitely or until no more space is available if it isn’t truncated. The error message from SQL Server version 6.5 when there is no more space in a database is:

1105

Can not allocate space for object '%.*s' in database '%.*s' because the '%.*s' segment is full. If you ran out of space in Syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment.

If the object name is syslogs, then the transaction log is full. If you have not separated the log, this simply means that the database is full because all page allocations go to the same device(s).

Do not use sp_extendsegment to extend the segment. This should only be used if you have created user defined segments, and for user-created tables and indexes.

The error message for version 7 and later is (error 9002):

The log file for database '%.*ls' is full. Back up the transaction log for the database to free up some log space.

Preventing a full transaction log.

Regardless of which method you choose to truncate the log, SQL Server will not truncate past the earliest open transaction or past transactions in the log that still hasn't been replicated to the Distribution database (when using replication). You can check if you have open transaction with the DBCC OPENTRAN command.

The “simple recovery mode” (SQL Server 2000) or "Trunc. log on chkpt." database option (SQL Server 7.0 or earlier)

When this option is set, you cannot perform transaction log backups.

SQL Server will truncate the log regularly. This means that you will probably not encounter a full (or large) transaction log, but the obvious downside is that you cannot backup the log (since the log records aren’t kept around).

Perform continuous backup of the transaction log

This is the preferred method of truncating the transaction log. Since you perform incremental backup, you risk losing less data in a crash situation.

Batch operations

It is not unusual for the log to fill up or grow large during batch operations. If possible, perform DUMP TRANSACTION/BACKUP LOG within your batch and/or use non-logged operations (like fast BCP, BULK INSERT etc).

To recover from a full transaction log

If the log is 100% full you cannot truncate it by backing it up, since the backup has to be recorded in the transaction log. For version 6.5, use:

DUMP TRANSACTION <dbname> WITH NO_LOG

And for version 7 or later:

BACKUP LOG <dbname> WITH NO_LOG

Since you cannot continue to perform transaction log backup after this command has been executed, you should perform a database backup.

How to handle very large transaction log files (SQL Server 7.0 and higher)

The most common reasons for very large log files are:

After handling the problem, you might want to shrink the physical file size. Note that when you perform a log backup, the physical file size isn’t shrunk. The only thing that happens is that the old transaction log records are removed. The file is shrunk using the DBCC SHRINKFILE command. The process is fairly straightforward in SQL Server 2000 and a little bit more involved in SQL Server 7.0. There are two excellent Microsoft Knowledgebase articles available on the subject (one for SQL Server 7.0: 256650 and one for SQL Server 2000: 272318. The URL’s at the time of writing for the two are, respectively:

http://support.microsoft.com/default.aspx?scid=kb;en-us;256650

http://support.microsoft.com/default.aspx?scid=kb;en-us;272318