Truncating Database Transaction Log
Posted under tips on November 02 , 2019 by Nidhisha Shetty
Databases are usually in Full Recovery mode. This means that every transaction is retained in the transaction logs allowing a point in time recovery.
The implication of this is that unless the transaction log is truncated, no space gets freed up on deleting rows from any tables. Delete operations only shift data from the table to the logs.
Following are the steps to reduce the size of transaction log in SQL Server Management Studio:
Right click the database, select Tasks → Shrink → Files as shown in the following figure:
Fig 1: Options to reduce transaction log file size
- On the Shrink File window, change the File type field option to Log. You can also choose either Release unused space, Reorganize pages before releasing unused space, or Empty file by migrating the data to other files in the same filegroup options:
- Click OK, this will reduce the file size of the transaction log.