Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

periodically shrink or replace LDF file

Posted on 2006-11-08
14
Medium Priority
?
431 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

618 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