1

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,

Nick Kavadias
  • 10,796
  • 7
  • 37
  • 47
mikemurf22
  • 113
  • 4

4 Answers4

1

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.

squillman
  • 37,883
  • 12
  • 92
  • 146
1

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

Community
  • 1
Nick Kavadias
  • 10,796
  • 7
  • 37
  • 47
0

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.

Dave Forgac
  • 3,546
  • 7
  • 37
  • 48
0

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.

mrdenny
  • 27,174
  • 4
  • 41
  • 69