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:

  1. Right click the database, select Tasks → Shrink → Files as shown in the following figure:

    Fig 1

    Fig 1: Options to reduce transaction log file size

  2. 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:

    Fig 2

  3. Click OK, this will reduce the file size of the transaction log.


Tagged:SQL ServerSSMSDatabase

Let's talk

Address

7th floor, Unit No. 701, C Wing, Kailas Business Park, S. Veer Savarkar Marg, Park Site, Vikhroli (W), Mumbai-400079.


Phone

+91 22 62362043


For General Queries

contact@quantumphinance.com


For Technical Support

contact@quantumphinance.com


For Sale and Demos

sales@quantumphinance.com

Thank you for your feedback.