Home

Truncating and shrinking the transaction log

Prev Page Next Page
Introduction
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
Failover
Log shipping in SQL Server 2005
Setting up log shipping using Management Studio
Checking the set up
Log shipping status report
Failover
Log shipping in SQL Backup
Using the CopyTool utility
Failover
3rd party backup applications
VDI
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
Encryption
Integrated database verification
Database file relocation
Improved backup retention
RESTORE HELP
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.
Pending
vdi error codes
Restore speed details
Help, my transaction log file is huge!
Mirror or log ship




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.

trxlog05_a

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:

trxlog07_a

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.

shrink_07

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:

trxlog05_a

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.

trxlog07_a

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:

shrink_01

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

shrink_02

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:

shrink_03

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):

shrink_04

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.

shrink_05

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:

shrink_06

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