0

I am using SQL Server 2016. I have setup SQL Agent for Full backup (Weekly) and Transaction Log backup (Daily).

Backup transaction log into a single file using:

BACKUP LOG [XXX] TO  DISK = N'E:\SQLDB\Backup\XXX_Trans_Log.bak' WITH RETAINDAYS = 28, NOFORMAT, NOINIT,  NAME = N'XXX-Transaction Log Backup', NOSKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

What I expected is only 28 days transaction log backups will be kept. But now I just found All transaction log backups are kept. So the file grows to very large in size.

After finding this, I change the plan to backup log in a separate file each day by:

DECLARE @Ver VARCHAR(2)
DECLARE @File VARCHAR(100)

SET @Ver = (SELECT RIGHT('0' + CAST((DATEPART(DAY,GETDATE())) AS VARCHAR(2)),2))
SET @File = 'E:\SQLDB\Backup\XXX_Trans_Log_' + @Ver + '.bak'


BACKUP LOG [XXX] TO DISK = @File WITH RETAINDAYS = 28, NOFORMAT, INIT, NAME = N'XXX-Transaction Log Backup', NOSKIP, NOREWIND, NOUNLOAD, STATS = 10

I am testing it on testing environment. Basically it works but I found the log size is quite large, as nobody is using the environment. Here are the figures:

DB file size = 500MB (38% free)

Log file Size = 865MB (38% free)

DB Backup (Weekly) = 311MB

Trans Log Backup (Daily)= 277MB

Any reason why the trans log backup so large?

redsimon
  • 71
  • 3

1 Answers1

0

I cannot say why your daily transaction log backups are large - it is usually down to user activity on the database and SQL Agent maintenance jobs such as ones that use DBCC CHECKDB and re-indexing.

WITH RETAINDAYS will not delete your previous backups, it will prevent overwrites of the backup file when using the INIT option. You will still need to run a job to clear out the old backup files.

Here's one that Ola Hallengren's scripts add :-

    DECLARE @CleanupDate datetime
    SET @CleanupDate = DATEADD(dd,-28,GETDATE())
    EXECUTE dbo.sp_purge_jobhistory @oldest_date = @CleanupDate
Ruby Blue
  • 57
  • 5