1. Computing

Discuss in my forum

Transaction Log Shrinking and Truncation in SQL Server

By

The transaction log plays a critical role in a SQL Server database: it maintains an ongoing record of database activity crucial for the restoration of recent data in the event of a disaster. However, this benefit comes at a cost: the transaction log can consume a substantial amount of space in an active database. SQL Server provides two actions designed to counterbalance these large space requirements: transaction log truncation and log file shrinking.

Log Truncation

Transaction log truncation removes entries from the transaction log file. Normally, SQL Server handles truncation automatically and no administration intervention is necessary. The frequency of truncation depends upon the recovery model used in your database. SQL Server truncates the transaction log every time you back it up under the full or bulk-logged recovery model. If you’re using the simple recovery model (which provides no transaction log recovery), SQL Server truncates the log at every transaction checkpoint.

You may force SQL Server to truncate the transaction log through a roundabout process. You need to run a backup, but instruct SQL Server that the only action you would like to take is truncation of the transaction log. You can do this with the following Transact-SQL command:
BACKUP LOG <database_name> WITH TRUNCATE_ONLY;

Log Shrinking

Log truncation does remove transactions from the log file, but it doesn’t actually reduce the amount of space reserved for the file. SQL Server expects that your transaction log will eventually grow to its pre-truncation size, so it doesn’t release the disk space allocated to the log. This can be problematic if your log grows to an artificially large size at one point in time and never reaches that size again.

In that case, you’ll need to manually shrink the transaction log file to reclaim the disk space for other uses. You may shrink your log file using the following Transact-SQL command:
DBCC SHRINKFILE(<filename>,<desired_shrink_size>)
Where desired_shrink_size is the amount of space, in megabytes, that you would like to reclaim. For obvious reasons, you can reclaim the most disk space immediately following a transaction log truncation operation.
  1. About.com
  2. Computing
  3. Databases
  4. SQL Server
  5. Transaction Log Shrinking and Truncation in SQL Server

©2014 About.com. All rights reserved.