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:
In SQL Server 2005, the following error is displayed:
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.
To find out how much log space has been used in each database, use the DBCC SQLPERF command:
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.