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\
- 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.
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.
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/