I have a SQL Maintance Plan that is set to backup my transaction log. I have it set to expire the transaction log after 2 days. The transaction log get created fine, but the old ones never get deleted.

Here is the SQL that maintance plan generates

EXECUTE master.dbo.xp_create_subdir N'J:\MSSQL\Backup\DBName' GO BACKUP LOG [DBName] TO DISK = N'J:\MSSQL\Backup\DBName\DBName_backup_yyyyddMMsss.trn' WITH RETAINDAYS = 2, NOFORMAT, NOINIT, NAME = N'DBName_backup_yyyyddMMsss', SKIP, REWIND, NOUNLOAD, STATS = 10

Looking at the history of the job, it's been running fine. It's just that the files aren't being deleted.

Any help is greatly appreciated.



As pointed out by others, the RETAINDAYS option doesn't delete the files, but only prevents overwriting. It's probably more useful if backups were done directly to tape.

If you want to stick with using maintenance plans you need to re-run the wizard and select the Maintenance Cleanup Task.

Below are a few screenies to show you where you need to go and what you will need to fill in.

alt text

alt text


Just off of a hunch, because I've been bitten by this before and run the box out of disk space, check what you have in the Backup file extension textbox in your backup task. Make sure you have an extension without the dot. SQL seemingly automatically appends the . so you don't want it in the extension value.


In the maintenance plan, specify the extension of the backup files that you'd like deleted. As far as I know it's not supposed to be necessary but I've seen adding that make it work.

  • sorry I should have specified. The Backup file extension is set to "trn" – mikemurf22 Sep 29 '09 at 14:30

You have to add the object which deletes the files to the plan. Just because the files are expired doesn't mean they will be deleted. Expiring is only useful when writing to tape.

  • That makes sense. How do I specify that I want to delete an expired file? – mikemurf22 Sep 30 '09 at 13:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.