SQL Server Transaction Log Backup File

Question

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.

Is there syntax/option problems in the backup statement?

Or I should have to store in separate files?

What should I do now?

Answer 1

retaindays does not do what you are expecting it to do.

It prevents backup files from being overwritten, but it does not mean that old backups are automatically deleted.

See here

If you want a solution that will do both backup and cleanup, try Ola Hallengren's solution

PS: This question is better suited to https://dba.stackexchange.com/