?
Solved

periodically shrink or replace LDF file

Posted on 2006-11-08
14
Medium Priority
?
430 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 672 total points
ID: 17896903
change the database recovery mode to simple : NOT Recomended on production servers
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public 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 664 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 664 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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

762 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