2005 Maintenance Plan - Delete files older than

Question

Post reply

2005 Maintenance Plan - Delete files older than

AndyP-636632

SSC Enthusiast

Points: 115
More actions
June 3, 2008 at 8:15 am

#186329

Good afternoon,
does anybody know how SQL Server 2005 determines the age of the backup files on disk to delete.
I have a Maintenance Plan set to delete files older than 1 day and it runs at 18:00 daily.
I checked today and there is only one set of files when I was expecting 2 since if the job starts at 18:00 then the files will be dated (say) 19:00 (i.e. at the end of the previous backup), which is within a day, so the first set should still be there shouldn't they?
Regards
Andy P

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 1

That's a good question. I know xp_delete_file is called, with the filename and time base on tracing things.

In testing, I also saw a call like this:

exec sp_executesql N'RESTORE LABELONLY FROM DISK=@P1',N'@P1 nchar(67)',N'C:\Maint2005\BAK Backup Files\Test BAK PLan_backup_200602071034.bak'

That appeared to verify this was a backup file.

What's the time stamp on the current file? Could the other one have finished early? You might experiment with some times to determine this and then perhaps alter the time you're keeping things around for.

Also, are you SP2 + cumulative updates? there were issues in SP2 that caused problems with this task because the "Hours" option was added to the task.

AndyP-636632 SSC Enthusiast Points: 115 More actions · Answer 2

There is no hours option - the min value is Days (no connection to server to check this at present so possibly not SP2)!

I will keep an eye on this over the next couple of days to be absolutely sure, but, I am relatively confident that the files deleted were from a previous backup started at the same time as the backup that deleted them the following day - so I'm still not entirely sure which file date is used to determine the age of the files?

Thanks for your input.

Andy P

Manoj-485464 SSCrazy Points: 2717 More actions · Answer 3

You should be able to add cleanup task in the MP and keep your policy "Delete .BAK files older than 2 days"

Manoj

MCP, MCTS (GDBA/EDA)

AndyP-636632 SSC Enthusiast Points: 115 More actions · Answer 4

Thanks Manoj,

I do have a cleanup task in my maintenance plan.

It is that task that is removing the files.

The real question is, what date does the cleanup task use to determine the age of the files on disk it is going to delete?

Regards

Andy P

Manoj-485464 SSCrazy Points: 2717 More actions · Answer 5

AndyP (6/4/2008)
Thanks Manoj,
I do have a cleanup task in my maintenance plan.
It is that task that is removing the files.
The real question is, what date does the cleanup task use to determine the age of the files on disk it is going to delete?
Regards
Andy P

Again my dear friend it depends on what age you are going to give in the Cleanup task? As I explained you earlier as well When you start Job it will check the files older than timeline deleting first and then it will start the backup job.

Manoj

MCP, MCTS (GDBA/EDA)