How to Shrink LDF File Size using SQL Management Studio

If the transaction log file (.ldf) in Microsoft SQL is too big, it can cause performance issues, eat up valuable disk space and consume resources when you back up. Therefore it’s imperative to periodically maintain the database to keep the .ldf file under control.

To reduce the .ldf file, you must do threethings:

  1. Backup the database
  2. Stop the database service
  3. Shrink the .ldf file

Fortunately, SQL Management Studio offers a simple GUI to perform both those tasks. If you don’t have SQL Management Studio, you can get it here.

If you are not sure how big your database’s .ldf file is, SQL Management studio will let you peek as well.

To preview and reduce your SQL database .ldf file, follow these directions:

Open SQL management Studio and find your database.

Right click on it and select properties. From the left hand pane, select Files.

The image above shows a 5GB database with a 49 GB .ldf transaction log!

Click OK to exit the window.

Right click on your database and select Tasks > Backup

Make sure the backup type is Full and select a destination. Click OK to proceed.

Once the backup has completed, stop the SQL server service, then right click on the database and select Tasks> Shrink > Shrink Files.

Select Log as the file type and select Release unused space. Click OK, to proceed. This will truncate the log file.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s