CAITMAN
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)
USE vCenterDB
DBCC SHRINKFILE('vCenterDB_log'
BACKUP LOG vCenterDB WITH TRUNCATE_ONLY
DBCC SHRINKFILE('vCenterDB_log'
USE wslogdb70
DBCC SHRINKFILE('wslogdb70_log.
BACKUP LOG wslogdb70 WITH TRUNCATE_ONLY
DBCC SHRINKFILE('wslogdb70_log.
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?
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.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!'
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!'
Article for anyone else trying to solve this issue:
https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/A_9772-How-to-Truncate-Log-on-SQL-Server-2008-Manually.html
https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/A_9772-How-to-Truncate-Log-on-SQL-Server-2008-Manually.html
http://technet.microsoft.com/en-us/library/ms189085.aspx