• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 433
  • Last Modified:

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 ?
0
humer2000
Asked:
humer2000
  • 5
  • 2
  • 2
  • +1
3 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
you need to schedule a job which takes the backups of your log files
0
 
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),,,
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
change the database recovery mode to simple : NOT Recomended on production servers
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
YogeshupCommented:
Hi

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.
0
 
humer2000Author Commented:
Hi Yogeshup
tell me more about your solution
0
 
Guy Hengel [angelIII / a3]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.
0
 
YogeshupCommented:
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.
0
 
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...)
0
 
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 !
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the All Tasks> backup database  opens a form which has as main option:
* BACKUP
  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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
points split aneeshattingal, angelIII, Yogeshup
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now