Let's say I have a daily maintainance task that:

  1. Backups all databases and then
  2. removes backups that are older than 3 days.

Now let's say the first backup at day 1, starting at 10:00, results in the following files

db1.bak 2012-01-01 10:04
db2.bak 2012-01-01 10:06

Now let's say at day 4 the first step of the maintainance task (backup the DBs) happens to finish at 10:05. Will SQL Server

  • delete db1.bak and keep db2.bak (would be logical, but might be surprising for the user) or
  • keep both or
  • remove both?

The maintenance plan will remove backup files using the time that the cleanup task is started as it generates the SQL at that time. So in the three options you described the first case is what would happen.

I created a example plan that removes files over 3 days old and ran the job. From the log output below you can see that the job ran at 12:25 on 5/7/2013 and it removed files older than 12:25 on 5/4/2013. The T-SQL below shows an approximate command for how the date might be generated.

select GETDATE()
select DATEADD(DAY, -3, GETDATE() )

2013-05-07 12:51:52.073

(1 row(s) affected)

2013-05-04 12:51:52.073

(1 row(s) affected)

Maintenance Plan: MaintenancePlan Duration: 00:00:02 Status: Succeeded. Details:

Backup Database on Local server connection Databases: DBA Type: Full Append existing Task start: 2013-05-07T12:25:03. Task end: 2013-05-07T12:25:05. Success Command:BACKUP DATABASE [DBA] TO DISK = N''N:\Apps\MSSQL10.INSTANCE\MSSQL\Backup\DBA_backup_2013_05_07_122503_6738713.bak'' WITH NOFORMAT, NOINIT, NAME = N''DBA_backup_2013_05_07_122503_6738713'', SKIP, REWIND, NOUNLOAD, STATS = 10


Maintenance Cleanup on Local server connection Cleanup Database Backup files Age: Older than 3 Days Task start: 2013-05-07T12:25:05. Task end: 2013-05-07T12:25:05. Success Command:EXECUTE master.dbo.xp_delete_file 0,N''N:\Apps\MSSQL10.INSTANCE\MSSQL\Backup'',N''BAK'',N''2013-05-04T12:25:05''


  • Thank you. Unfortunately, xp_delete_file is intentionally undocumented. So, what is the conclusion? Can we say with certainty that, of the three options presented in my question, option 1 correctly describes the behaviour of the SQL Server maintenance plan? – Heinzi May 8 '13 at 17:53
  • That code is what the Microsoft Maintenance Plan generates; I think they'll make an exception for themselves on using undocumented stored procedures. I've updated the answer to more specifically answer your question. – Jason Cumberland May 10 '13 at 3:24

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.