SQL - Shrinking the transaction log (.ldf)

Expand / Collapse

SQL - Shrinking the transaction log (.ldf)

Article ID: 51146  - Last Review: July 26, 2011


Where the database files are located on CCM/SQL server, you will have two file types, .mdf and .ldf. The .mdf (Master Database file) will contain all the information of the SQL database. The .ldf file (Logical Data File) contains all transaction logs. In the SQL Database Properties, the default recovery model is set to Simple but in some cases it might have been manually changed to Full. The effect of it being set to Full could lead to rapid growth of the transaction log file, potentially taking a lot of hard drive space.

In the event of having such a large .ldf file, there is a way to reduce its size. Please use the following steps to shrink it ;

  1. In SQL Studio management Express, expand Databases
  2. Right click on CCMData then select Properties
  3. Select Options from the left
  4. Under Recovery Model, select Simple then click ok

    5. Right click on CCMData again
    6. Under Tasks, select Shrink, then DatabaseThis will shrink the size of the database

The Shrink task is NOT included in our Maintenance service as the recovery model is set to Simple by default and should not grow to a relatively large file. What is included in our Maintenance service is the defragmentation of the SQL Database.

There are other potential issues related to large .ldf files. It could cause the SQLWriter service to be slow writing information to the database but more importantly it can impact the performance of the entire system due to the considerable amount of space it could be taking on the hard drive.

NOTE : This can be performed on any of the prairieFyre Software database files; CCMData, CCMStatisticalData and IQR3.

Keywords:  SQL ldf transaction log shrink recovery model simple full maintenance

Rate this Article:

Add Your Comments

Name: *
Email Address:
Web Address:
Verification Code:

Last Modified:Tuesday, July 26, 2011
Last Modified By: kmiddlemiss
Rated 5 stars based on 1 vote
Article has been viewed 18,212 times.