Each database in SQL Server consists of one or more data files, and one or more transaction logs.

The data files store the data that make up the database i.e. internal database structures, data pages, index pages etc.  What then is the purpose of the transaction log?  Well, it's basically to keep a record of all modifications made to the database.  Let's take the following example:

Why does it work like that?  Why isn't the change written to the data file immediately?  Simply because it is inefficient to do so.  The data file may need to expand to accomodate the changes, page splits may need to be made, new extents may need to be allocated etc etc.  Writing to the transaction log is far faster, as log records are recorded serially.  Which is also why the general recommendation is to use a physically separate disk to store the transaction log, as disk reads and writes are largely serial in nature.  Disk reads and writes for data files are largely random.

What then is recorded in the transaction log?  Something like this (a very simplified example):

The change is recorded in a series of log records in the transaction log.  Each record is given a number, called the Log Sequence Number (LSN).

Each log record is stored in a virtual log file in the transaction log.  A transaction log can have any number of virtual log files, and is determined by the database engine.  The size of each virtual log file is also not fixed.

In our example then, the active portion of the log is the section that contains our transactions.  This is the portion that is required to do a full recovery of the database.  When more transactions are created, the active portion of the log will grow.

What happens when a checkpoint occurs?  The changes are written to the data file, and a checkpoint record is created.

Now, the changes made by transactions 1, 2 and 3 have been written to the data file.  The active portion of the log now spans from LSN 50 to LSN 52, because transaction 3 has not been committed.  As for LSN 45 to 49, they can be reused if the database is running the simple recovery model, as those records are no longer required.

When SQL Server marks virtual log file 1 and 2 as reusable, it is known as truncating the transaction log.  Note that the physical size of the log is still the same.

If the database is running the full or bulk-logged recovery models, LSN 45 to 49 can only be deleted and the space reused when the transaction log is backed up.

What happens now when more transactions are created?  In the simple recovery model, the free space at the beginning of the log is resued.

In the full or bulk-logged recovery models, the transaction log will need to be extended.

If the transaction log is a fixed size log, you will receive the following error in SQL Server 2000:

Server: Msg 9002, Level 17, State 6, Line 1 The log file for database 'AdventureWorks' is full. Back up the transaction log for the database to free up some log space.

In SQL Server 2005, the following error is displayed:

Msg 9002, Level 17, State 4, Line 1 The transaction log for database 'AdventureWorks' 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 not to say that you will never run into error 9002 if your database uses the simple recovery model.  If you have a long running transaction that has not been committed, your transaction log can still fill up as SQL Server will not be able to delete any log records that were created after the transaction started.  The active portion of the log begins from when the long running transaction started, and no part of the active portion can be deleted or reused.

So basically in order to keep your transaction log to a manageable size:

·ensure that your transactions are committed as soon as the changes are confirmed, or rolled-back if there is an error.

·back up your transaction log periodically if your database is running the full or bulk-logged recovery models

To find out what is the oldest active transaction in a database, especially the time it started, use the DBCC OPENTRAN command e.g.

DBCC OPENTRAN

Transaction information for database 'AdventureWorks'.   Oldest active transaction:    SPID (server process ID) : 52    UID (user ID) : 1    Name          : user_transaction    LSN           : (754:531:1)    Start time    : Jul 14 2008  5:43:55:390PM

To find out how much log space has been used in each database, use the DBCC SQLPERF command:

DBCC SQLPERF(LOGSPACE)

Sample output from DBCC SQLPERF(LOGSPACE)

To find out how many virtual logs are there in your transaction log, use the DBCC LOGINFO command.  The details that are displayed are for the database that you are currently connected to.  This was the output I got for my AdventureWorks database:

This means that there are 4 virtual log files (1 row for each virtual log file) in my transaction log, all contained in a single physical file (FileId = 2).  The 1st, 2nd and 3rd virtual log files are 458752 bytes in size (FileSize = 458752), while the last virtual log file is 712704 bytes in size.  The 1st to 3rd virtual log files are either unused or reusable (Status = 0), while the 4th virtual log file is in use (Status = 2).  The virtual log files are in the same sequence as the physical layout of the file (FSeqNo is ascending), though this may not always be the case.

Document history

7/18/2008	Added DBCC LOGINFO details.
7/14/2008	Added DBCC OPENTRAN example.
7/8/2008	Added DBCC SQLPERF example.
7/4/2008	Initial release.