Link to home
Start Free TrialLog in
Avatar of CAITMAN
CAITMANFlag for United States of America

asked on

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)

Avatar of Darius Ghassem
Darius Ghassem
Flag of United States of America image

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
Avatar of CAITMAN

ASKER

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 :)

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?
Avatar of CAITMAN

ASKER

Im actually in the process of pinging them right now also to see.
>>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.
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.
ASKER CERTIFIED SOLUTION
Avatar of Rick4him
Rick4him
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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!'