humer2000
asked on
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 ?
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 ?
you need to schedule a job which takes the backups of your log files
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),,,
* 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),,,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
Hi Yogeshup
tell me more about your solution
tell me more about your solution
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>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...)
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...)
ASKER
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 !
because in all task> when i click on backup, it only backs up the MDF file and keep the LDF unchanged !
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
* 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
points split aneeshattingal, angelIII, Yogeshup