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

Posted on

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.

SQL Ldf File Too Big

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.

shrink ldf file setting - SQL Ldf File Too Big

shrink SQL log file - SQL Ldf File Too Big

After shrink the ldf (log file) you will see significant file size reduction, on my case from 311GB into 1MB.
I write a post on how to receive alert email when our server’s disk nearly full this is very useful to me and I hope to you too. http://yunarwinardi.com/free-disk-space-alerts-to-your-email-easy-steps/ 

Facebook Comments