SQL Server Performance Forum – Threads Archive

Create a store procedure to shrink old log files

Folks, I’d like to create a daily store procedure to automatically shrink my DBs Transaction logs files older than X days.
The log files I want to shrink are create daily as theirs DBs so they have diferent names but with the same letters on the begining.
I’m looking for something like: "shrink log file LM_xxxxxxx if it is older than 2 days"
Is any one can help?? regards,
Leo[email protected]

You cannot do that as the Transaction logs doesn’t store multiple files with the date, all the contents will be on same file. Why you are wasting SQL resources by shrinking the transaction log, why not check the enabled jobs and any bulk load operation on database contributing to this issue and set the desired size to Tlog. You can also manage the Tlog using SIMPLE recovery model and during the bulk load operations can perform the Tlog backup with TRUNCATEONLY to keepup the size. But bear in mind doing so if there is any problem with the database consistency in the case of crash you may not be able to recover the database and you must restore from last good known backup only. For this reason it is better to use FULL recovery model and perform the regular intervals of backup transaction log to take care of size. HTH Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

satya,
I understand what you say, but the application creates a new DB (data and log file) every day. It creates the log files with the name LM_<date>. This is the reason I want to schedule a procedure to automatically shrink those log files if they are 1 day old.
The application is creating log files bigger than the data files and when I shrink the 1 day old log files the size decrease from 25GB to 0,5MB. So I want a workaround until the solve the problem.
Can you help me?? best regards,
leo

Does application writes anything into 2 or more days old db? What is recovery model for these dbs?

Why the log is created with 25gb size?
Why not test the application activity in a day and set that size?
Can you place the transaction log on a seperate disk that can take care of the sizes without further issues, based on the feedback of your process I can say you can schedule a job to shrink the log files provided the application is taking care of its activities and you are setting recommended RECOVERY models to the databas whereever required. KBAhttp://support.microsoft.com/?kbid=873235 about tr.log. As the Transaction log files are created with date you can get the filename using extended SPs and use the same value in the code of that job to shrink the log. I;m sure there was a link available to shrink the Tlog after certain number of days. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>