Solved

periodically shrink or replace LDF file

Posted on 2006-11-08
14
423 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
Comment Utility
you need to schedule a job which takes the backups of your log files
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
change the database recovery mode to simple : NOT Recomended on production servers
0
 
LVL 1

Expert Comment

by:Yogeshup
Comment Utility
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
Comment Utility
Hi Yogeshup
tell me more about your solution
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 166 total points
Comment Utility
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
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>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
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
points split aneeshattingal, angelIII, Yogeshup
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now