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:
- Backup the database
- Stop the database service
- 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.