A Guide to Transaction Log Shrinking and Truncation in SQL Server

Free up space in SQL Server with transaction log shrinking

Professor and college student at laptop
Hero Images / Getty Images

The transaction log plays an important 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 the large space requirement: transaction log truncation and log file shrinking.

Log Truncation in SQL Server

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 want to take is a truncation of the transaction log. You can do this with the following Transact-SQL command:

BACKUP LOG WITH TRUNCATE_ONLY;

Log Shrinking in SQL Server

Log truncation removes transactions from the log file, but it doesn’t 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 becomes 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 need to manually shrink the transaction log file to reclaim the disk space for other uses.

You may shrink the log file using the following Transact-SQL command:

DBCC SHRINKFILE(,)

Where desired_shrink_size is the amount of space in megabytes that you want to reclaim. For obvious reasons, you can reclaim the most disk space immediately following a transaction log truncation operation.

Format
mla apa chicago
Your Citation
Chapple, Mike. "A Guide to Transaction Log Shrinking and Truncation in SQL Server." ThoughtCo, Sep. 29, 2017, thoughtco.com/transaction-log-shrinking-and-truncation-1019839. Chapple, Mike. (2017, September 29). A Guide to Transaction Log Shrinking and Truncation in SQL Server. Retrieved from https://www.thoughtco.com/transaction-log-shrinking-and-truncation-1019839 Chapple, Mike. "A Guide to Transaction Log Shrinking and Truncation in SQL Server." ThoughtCo. https://www.thoughtco.com/transaction-log-shrinking-and-truncation-1019839 (accessed December 11, 2017).