VERIFIED SOLUTION i
X

How to reduce/shrink LDF file size in SQL Server using SQL Management Studio

UPDATED: March 24, 2017


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

To reduce the .ldf file, you must do three things:

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

SQL Management Studio offers a simple GUI to perform both those tasks.

If you are not sure how big your database's .ldf file is, SQL Management studio will let you see this information.

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.

Environment Details

Product Feature: Database

Operating System: Any

Database: MSSQL


 

Downloads

  • No Downloads