Advice on automating a job.

Hi I'm just looking for a little bit of advice to point me in the right direction before I start to put this together.

The task I have is too automate the clear down of the transaction logs. Presently I am manually going in and using this method:

BACKUP LOG {database name} WITH TRUNCATE_ONLY
DBCC SHRINKFILE ( {log file name},2 )

However now we have decided this would be best to be automated and I'm faily new to SQL server so I'm not sure the best way to go about this. So I was hoping someone could point me in the right direction that I go about doing it so I don't go off on a complete tangent. Ow and Can anyone think of any problem automating this job could cause.

My eternal graitude, EdWhy are you backing up with TRUNCATE ONLY? This does not give you any recoverability for transactions occuring after the last backup. If you really don't need point-in-time recoverability then you could just put the database in simple recovery mode.

blindman|||Ahh I see

I was trying to come up with a way of reducing the size transaction Log without, stopping the database and restarting and I came across this advice, I used the first statment because otherwise the the transactionlog won't shrink much. I didn't really consider the point that (I said I was new too this) this basically mean that the it is no longer actuly producing a functional backup.

Ouch

Well in that case I need to come up with a solution, Do you have any Idea what the best time to clear down the transaction logs would be? erm could I run this immediately before the back up. but then if the backup is faulty we would lose a whole days work and if I run it just after the backup then the next days would be useless, unless I run it immediately and make sure their is no transaction inbetween the backup and the transaction shrinking!

Do you know how to restore the transaction log to it orginal functioning glory!

Thanks Again Ed