What 'resets' the transaction log

Question

My question might sound rather strange but I'm not really a SQL expert and I can't find the answer..

I'm trying to create a backup plan which creates a full backup twice a day. A differential back-up every hour and a log backup every 5 minutes.

It works fine but I've noticed the log backup (.trn) grows and grows in size each time.

If I use backup log with init it overwrites and I'm getting the too recent error: I thought that after another full or differential back-up the next log backup would only include the log back-ups 'since the last full / diff backup'?

BACKUP DATABASE 
    lagerdb 
TO DISK = N'C:\SQLBACKUP\lagerdb-rechts.bak' 
WITH INIT, NOUNLOAD, NAME = N'lagerdb full', NOSKIP, STATS = 20, NOFORMAT

BACKUP DATABASE 
    lagerdb TO DISK = N'c:\SQLBACKUP\lagerdbd-rechts.bak' 
WITH INIT, NOUNLOAD, NAME = N'lagerdb differential', NOSKIP, STATS = 20, NOFORMAT, DIFFERENTIAL 

BACKUP LOG 
    lagerdb 
TO DISK = N'C:\SQLBACKUP\lagerdb-rechts.trn' 
WITH NAME = N'lagerdb logs'

I did something with the suggestion below to backup the log to seperate files with a datetime stamp:

 -- Declares a string
 declare @sql nvarchar(255)
 declare @ParmDefinition NVARCHAR(500)

 -- declares the path name. Seperated by disk drive and directory.
 declare @bkName varchar(255)

 -- Declares the filename. 
 declare @bkdbName varchar(50)
 declare @bkdbDateTime varchar(20)

 --Get Timestring
 set @sql = N'select @parm1OUT = CONVERT(varchar(20),CONVERT(DATE,GETDATE(),104),104)     + ''_'' + Substring(CONVERT(varchar(8),getdate(),114),1,2) + Substring(CONVERT(varchar(8),getdate(),114),4,2)'
 SET @ParmDefinition=N'@parm1OUT varchar(20) OUTPUT'

 exec sp_executesql 
 @sql, 
 @ParmDefinition,
 @parm1OUT=@bkdbDateTime OUTPUT

 set @bkdbName = 'LagerDB_' + @bkdbDateTime

 -- Enter the Drive letter below. Please remove C: as current value.
 set @bkName = 'C:'

 -- Enter the folder name below (with lead and end '\' back slashes.
 -- Ensure that you change the folder from Temp below.
 set @bkName = @bkName + '\SQLBackup\'
 set @bkName = @bkName + @bkdbName 

 -- Sets the backup running by executing the @sql string.
 set @sql = 'BACKUP DATABASE lagerdb TO DISK = '''+@bkName+'.bak'''

 print @sql
 exec sp_executesql @sql

(EG: LagerDB_06.12.2011_1418.bak)

But the answer I was really looking for is that I thought it would reset after a full back-up. Maybe I should schedule a backup with init after a full or differential backup?

Answer 1

You are backing up the log to the same file repeatedly. Subsequent backups are being appended to that file, which is what you can see in your screen capture. Typically, we tend to backup to dated files:

BACKUP LOG 
    lagerdb 
TO DISK = N'C:\SQLBACKUP\lagerdb-rechts_201112061130.trn' 
WITH NAME = N'lagerdb logs'

Save yourself the trouble of hand cranking the necessary scripts and use Ola Hallengren's maintenance solution.