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?
 
Rick4himConnect With a Mentor Commented:
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
 
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.