Log TruncationTransaction 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 ShrinkingLog 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:
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.