periodically shrink or replace LDF file

Hi experts

I have serveral SQL database and after one month of use, LDF files are up to 5 Geg of size
I use to detach database, delete LDF and re attch them
but now i am fed with this solution, i need to automatically do it
Any one has great ideas ?
Who is Participating?
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
change the database recovery mode to simple : NOT Recomended on production servers
Aneesh RetnakaranDatabase AdministratorCommented:
you need to schedule a job which takes the backups of your log files
Guy Hengel [angelIII / a3]Billing EngineerCommented:
You should either
* run a regular full and transaction log backup, which will prevent the log file from growing all the time, as space will be reused (freed by the backup)
* change the database recovery mode to simple, so space will be reused automatically, but this will prevent you from being able to take transaction log backups and hence to be able to restore to any point in time (after full backup),,,
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.


You can also automate the process by creating an SQL script for the entire process being followed currently and schedule it as a job to run regularly.
humer2000Author Commented:
Hi Yogeshup
tell me more about your solution
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
Honestly: automating to detach and attach a database is really nonsense, because it is not a good practice.
avoiding the log file to grow by implementing the proper routines would show of expertise.
YogeshupConnect With a Mentor Commented:
Honestly, I agree with angelIII for the right approach. But there have been times when I am in the following situation

1. Client does not really want to store the log files but the company policy does not allow them to set the recovery mode to SIMPLE.
2. Not enough space to store backups on the server. I have worked with 20 GB databases ( with a 15 GB log) on a 60 GB hard disk where there is only 4-5 GB of space available

Hence the recommendation.....

please let me know your views.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>1. Client does not really want to store the log files but the company policy does not allow them to set the recovery mode to SIMPLE.
you have to tell them that the 2 are basically contradictory...

anyhow, in that situation, simply run a transaction log backup very often, but with deleting the log files very fast (not via the maintenance plan, but using some external process...)
humer2000Author Commented:
ummm how to backup transaction log ?
because in all task> when i click on backup, it only backs up the MDF file and keep the LDF unchanged !
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the All Tasks> backup database  opens a form which has as main option:
  0 Database - Complete
  0 Database - Differential
  0 Transaction Log  <<<< this is the one to perform transaction log backup
  0 File and Filegroup

The checkbox "schedule" is what you want to set in order it runs regulary
Guy Hengel [angelIII / a3]Billing EngineerCommented:
points split aneeshattingal, angelIII, Yogeshup
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.