automate SQL DB log truncations?

We have a number of SQL databases, both 2005 and 2008, and the transactions logs continue to grow to the point we have to manually truncate them.  I'm looking for the best way to automate this process?  Here is an example of the script we run to manually truncate them:


USE vCenterDB
DBCC SHRINKFILE('vCenterDB_log', 1)
BACKUP LOG vCenterDB WITH TRUNCATE_ONLY
DBCC SHRINKFILE('vCenterDB_log', 1)

USE wslogdb70
DBCC SHRINKFILE('wslogdb70_log.ldf', 1)
BACKUP LOG wslogdb70 WITH TRUNCATE_ONLY
DBCC SHRINKFILE('wslogdb70_log.ldf', 1)

CAITMANAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Darius GhassemCommented:
Are you backing the DB up? In the backup procedures  you can backup the logs then truncate them at the same time

http://technet.microsoft.com/en-us/library/ms189085.aspx
0
CAITMANAuthor Commented:
We use an application for backup that uses Block Level backup, so no.  I should have stated that from the beginning, I knew that would be the first response :)

0
Darius GhassemCommented:
Well after the block level backup you can create a transaction log backup only that truncates the logs. The backup software you currently have doesn't have this option?
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

CAITMANAuthor Commented:
Im actually in the process of pinging them right now also to see.
0
Anthony PerkinsCommented:
>>I'm looking for the best way to automate this process?  <<
This is a very bad idea.  Further "TRUNCATE_ONLY" is thankfully no longer supported in SQL Server 2008.

If you are not prepared or unprepared to adequately support and maintain a database in Full Recovery Model, than consider changing the Recovery Model to Simple.  What you are attempting to do now is no better and does not fragment the Transaction Log.
0
Anthony PerkinsCommented:
So that you fully grasp what is going on, your database is in Full Recovery Model, which supports point-in-time restores.  By Truncating the Log you:
A.  Fragmenting the Transaction Log and killing performance.
B.  Negating the one functionality you had with Full Recovery Model.

So either start doing:
1. Frequent Transaction Log backups or
2. Change the Recovery Model to Simple.

Pick your poison.
0
Rick4himCommented:
For "truncate_only" option only works in 2000/2005 but not on 2008.

To automate, build a cursor over master..sysdatabases, schedule in a job
-or-
create a maint task

For 2008 you will need to use backup log DatabaseName to Disk = 'NUL:' to truncate the log

For 2008, much simpler to change Database Recovery Option to "Simple" since you are discarding the log anyway.  Then you do not need to truncate the log.

Full recovery option is only of value if you are saving off logs (and all of them from the last backup).  The SQL transaction log is still used for option "simple" but only to complete a transaction, so it can still grow.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jogosCommented:
You mentioning a 'Block Level backup' makes me worry.  Did you ever try to restore one sql database? For that you need to have the content of the database-files and the commited/uncommited data to a consistant state, a database backup knows to handle that, a disk-backup not.

So I refer to the sql server backup posted higher.  In any way if you don't run a sql server transaction log backup your recovery model on any database must be 'SIMPLE'.

remember 'Taking backups is for show, being able to restore is for job/business security!'
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.