SQL ldf File Too Big? This 2 Easy Steps Will Fix It


Have you ever come to situation when suddenly your business critical application stops working? If your application running SQL server check whether the disk is full or nearly full? If that case then the reason your business critical application stop working is run out of disk space.

The question is why the space is so fast full although a month ago you still have plenty of space on that server? Check your the SQL log file with extension ldf. Ldf file is a transaction log of a database. Without ldf you cannot restore a database. MDF is actually hold the main data, in a database mdf file and ldf file is separate but they must come together to make a database works.

LDF file size 120% from mdf is considered normal, but if your mdf 200 mb and your ldf id 200GB then you can follow following steps to reduce your ldf file hence freeing disk space and will make your business critical application back running. On Ms SQL 8.0 mdf and ldf files usually located here:

C:\Program Files\Microsoft SQl Server\MSSQL10MSSQLSERVER\MSSQL\DATA\

1. Change log transaction from full to simple
Right click Database that have big ldf file, click properties, Options, on Recovery model option make sure it Simple, not full. Click OK.
ldf file huge


2. Shrink ldf file size from SQL administrative
Right click Database that have big ldf file, task, shrink, Files. New window will open on the file type choose log, OK.
mssql ldf file too big


ldf file too large


After shrink the ldf (log file) you will see significant file size reduction, on my case from 311GB into 1MB.

SQL ldf File Too Big? This 2 Easy Steps Will Fix It Reviewed by Yunar Winardi on April 28, 2019 Rating: 5

No comments:

Powered by Blogger.