Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

Thanks,

share|improve this question

4 Answers 4

up vote 1 down vote accepted

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

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer
    
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.

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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