When SQL Server truncates the tranasction log, it only marks virtual log files that are no longer in use and already backed up (if running the full or bullk-logged recovery models) as reusable.

Say that a transaction log contains the following log records, and is using the simple recovery mode.

When a checkpoint now occurs, virtual log file 1 and 2 are no longer in use, as transactions 1 and 2 have been committed and the log records are not required to perform a rollback.  SQL Server then marks virtual log file 1 and 2 are reusable, as follows:

This is what is known as truncating the transaction log.  Basically, the active portion of the transaction log has been truncated.  The physical size of the transaction log is unchanged, unless the database has the Autoshrink property set, in which case the transaction log will be physically shrunk (where possible) at periodic intervals.

So that's for the simple recovery model - once a transaction has been committed or rolled back, SQL Server can reuse the space occupied by those transactions.

As for databases running the full or bulk-logged recovery models, you will need to back up the transaction log before the log can be truncated.  So if your transaction log contained the following:

Now when a checkpoint occurs, SQL Server simply writes all dirty pages to disk, but will not mark any of the virtual log files as reusable.  Even if you performed a full database backup, nothing in the transaction log will be marked as reusable.  The transaction log will only be truncated when you back up the transaction log e.g.

BACKUP LOG AdventureWorks TO DISK = 'g:\backups\AdventureWorks_log_20100918 1530.bak'

Only then will the transaction log be truncated i.e.

There may be other reasons why SQL Server cannot truncate the transaction log e.g. if you are performing transactional replication and the transactions have not been replicated to your subscriber databases, or when there are long running transactions.  If you are using SQL Server 2005 and newer, you can run the following query to find out the reason the transaction log cannot be truncated. SELECT name, log_reuse_wait_desc FROM sys.databases If the transaction log cannot be truncated because it needs to be backed up, the value LOG_BACKUP is displayed.  If it's because there are transactions that have made a lot of changes but have not been committed nor rolled back, the value ACTIVE_TRANSACTION is displayed.  Refer to SQL Server Books Online 'sys.databases' topic for the other possible values.

If you have a new database that's running a full or bulk-logged recovery model, but have never made a full database backup of that database, the transaction log will be truncated every time a checkpoint occurs.  This behaviour is similar to when the database is running the simple recovery model.  So if you have a database that's running the full recovery model and you find that the transaction log never grows even though you have not backed it up, you need to check if you have even made a full backup of that database yet.

Reducing the physical size of the transaction log

In order to physically reduce the size of the transaction log, which is known as shrinking the transaction log, you could use any one of the following options:

·issue a DBCC SHRINKDATABASE command

·issue a DBCC SHRINKFILE command

Note that the transaction log can only be shrunk up to the boundary of a virtual log file.  Here's an example of that happening.

I created a new database with an initial size of 1 MB for the transaction log, and to autogrow in 5 MB increments.  This database is running the full recovery model.  Running DBCC LOGINFO displays the following:

There are 4 virtual log files of varying sizes.  I then entered some data, which caused the transaction log to grow by 5 MB:

4 new virtual log files have been created in the new 5 MB portion of the transaction log.  Each of those new virtual log files are 1310720 bytes in size.  In total, 7 virtual log files are in use (status = 2).  I now back up the transaction log, thus truncating the transaction log:

Only 1 virtual log file is in use (row 7, where status = 2).  If I now try to shrink the transaction log to 2 MB using the following command:

DBCC SHRINKFILE ('AdventureWorks_log', 2)

SQL Server can only delete the virtual log file 8, as the active log record is in virtual log file 7.  This shrunk the transaction log from ~7 MB to ~4.7 MB. SQL Server also creates dummy entries in the transaction log, to move the latest active log record before the 2 MB point, so that it wraps around to virtual log file 2 (note the rows with status = 2):

If I now back up the transaction log again, the transaction log is again truncated, and the active portion is now in virtual log file 2.

If I now attempt to shrink the file again, SQL Server succeeds in shrinking it closer to 2 MB, because the active portion of the log is within the first 2 MB.  The file is shrunk up to the nearest virtual log file size that meets the entered size.  The output from DBCC LOGINFO is now as follows:

and the size of the transaction log file is 2359296 bytes (the sum of all the virtual log file sizes + 8192 bytes for the header).

So if you find that you cannot shrink the transaction log to a particular size, run DBCC LOGINFO and check the size of the virtual log files to know how large each of them are, and to what sizes you can actually shink the file to.

Document history

9/19/2010	Added notes on full/bulk-logged recovery models.
9/28/2009	Added notes on Autoshrink property.
8/21/2008	Reworded virtual log file example.
7/18/2008	Initial release.