The transaction log

Prev Page Next Page
Recovery models
Main backup types
Backing up the database files by copying
The transaction log
Transaction log restore sequence
Log sequence numbers
Truncating and shrinking the transaction log
Backing up the tail
Inside the transaction log
So, what's in a backup file?
Test: A full backup does not contain deleted data
Verifying backup files
Verifying backup files on a budget
Cumulative backups
Recovering individual tables
Backup and restore history details
Backup reads and writes
Speeding up backups
Backup speed details
Speeding up restores
Restore state affects speed too
Backup and restore rights
Log shipping
Log shipping in SQL Server 2000
Setting up log shipping using Enterprise Manager
Checking the set up
Log shipping in SQL Server 2005
Setting up log shipping using Management Studio
Checking the set up
Log shipping status report
Log shipping in SQL Backup
Using the CopyTool utility
3rd party backup applications
VDI versions
VDI errors
SQL Backup - beyond compression
Restoring a chain of transaction log backups
Restoring to the latest possible state
Backing up multiple databases
Backup retention
Making a copy of the backup file
Backup file naming conventions
Restoring the latest backup set
Network resilience
Integrated database verification
Database file relocation
Improved backup retention
High-availability group support
Common SQL Backup issues
Installation checklist
Setting up rights
Configuring service rights
Backup data
Hanging issues
Common backup and restore errors
Error 3201 - when performing a backup to a network share
Full database backup file is larger than database size
Error 3205 - Too many backup devices specified for backup or restore
Error 4305 - an earlier transaction log backup is required
Bringing a database that is in recovery or read-only mode online
Using bulk-logged recovery model but transaction log backup is still large
Error 14274 - unable to delete SQL Server Agent job
Error messages when restoring from different versions of SQL Server.
vdi error codes
Restore speed details
Help, my transaction log file is huge!
Mirror or log ship

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.


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:


   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.    
Copyright 2008 - 2021 Yohz Ventures Sdn Bhd. All rights reserved.
All product and company names are trademarks or registered trademarks of their respective owners.