SQL Server Performance Forum – Threads Archive
Automaticcally truncate the transaction logI want to scehdule the truncation of transaction log file. How can I do it. I take differential backups. The transaction log is getting full very soon. As of now I delete the logs manually but this process is not possible all the time. Is there any way to schedule the automatic truncating the loga?
you can schedule a job to truncate the log file. Create a job using EM that does
BACKUP LOG db_name WITH TRUNCATE_ONLY than if needed shrink the log it using DBCC SRHINKFILE, and schedule the job to run when needed. If recovery to a point in time
is not within your requirments, consider changing recovery model to SIMPLY. Bambola.
As you haven’t mentioned the verion of SQL used.
IF its SQL 2000 then keep Database recovery model to SIMPLE.
If its SQL 7 then use Trunc.log at chkpt. option enabled. Truncation does not reduce the size of a physical log file, it reduces the size of the logical log file. For information on shrinking the size of a physical log file, see Shrinking the Transaction Log. _________
If you are truncating the log, you may not need to shrink the log file as truncating the log will increase %age free space in the file and so the file may not go beyond a certain size. I would not recommend shrinking the transaction log manually as a job during the production time. This hogs up resources on the server and the users of the database may see some delay in response. In my case I used to have a Transaction Log Backup job scheduled every 15 minutes which would generate the backup of transaction log. Since backing up of log removes inthe active portion of the transaction log from the log file, there were rarely any issues of Transaction Log growing up and so shrinking of the same was not needed. Gaurav
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
SET NOCOUNT ON
DECLARE @db_name varchar(100)
DECLARE @db_filename varchar(100)
DECLARE @str1 varchar(255)
DECLARE @str2 varchar(255)
DECLARE @str3 varchar(255)
DECLARE @shrink_value varchar(10) set @shrink_value = ’25’
CREATE TABLE temp1
[name] varchar(50) null,
) — Select list to determine which databases will have their log file truncated shrunk.
— databases from the select list.
DECLARE list_dbs Cursor
FOR select name from master..sysdatabases
where name not in (‘master’,’tempdb’,’msdb’,’northwind’,’model’,’pubs’)
AND CONVERT(sysname, DatabasePropertyEx(name, ‘Recovery’)) <>’Simple’ OPEN list_dbs FETCH NEXT FROM list_dbs into @db_name WHILE @@FETCH_STATUS = 0
— build string for name of datafile – store this in a temporary table SET @str1= ‘Insert temp1 select name, groupid from ‘ + @db_name+ ‘..sysfiles’
–select @str1 exec (@str1) — return on the log portion from the sysfiles table – groupid = 0
select @db_filename = name from temp1
where groupid = 0 — build string for backup statement
set @str2 = ‘backup log ‘ + @db_name + ‘ with no_log’
–build string for dbcc statement
set @str3 = ‘use ‘ + @db_name + ‘ dbcc shrinkfile (‘ + @db_filename + ‘,’ + @shrink_value + ‘)’
exec (@str3) truncate table temp1 FETCH NEXT FROM list_dbs into @db_name END
drop table temp1 CLOSE list_dbs