Solved

periodically shrink or replace LDF file

Posted on 2006-11-08
14
426 Views
Last Modified: 2012-08-13
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
Comment
Question by:humer2000
  • 5
  • 2
  • 2
  • +1
14 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17896828
you need to schedule a job which takes the backups of your log files
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17896894
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
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 168 total points
ID: 17896903
change the database recovery mode to simple : NOT Recomended on production servers
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 1

Expert Comment

by:Yogeshup
ID: 17897021
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
 
LVL 1

Author Comment

by:humer2000
ID: 17898506
Hi Yogeshup
tell me more about your solution
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 166 total points
ID: 17901019
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
 
LVL 1

Assisted Solution

by:Yogeshup
Yogeshup earned 166 total points
ID: 17904218
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17904317
>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
 
LVL 1

Author Comment

by:humer2000
ID: 17904488
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17904587
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18261416
points split aneeshattingal, angelIII, Yogeshup
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question