Using xp_delete_file in maintenance plans

There is no official documentation on the usage of the Stored Procedure xp_delete_file. In my research I have found that it can be used outside of the Maintenance plans. I also ran into some issues with it not deleting the backup files that I wanted it to delete in sub-directories.

It looks like in certain versions of MSSQL 2005 that the option to remove files from sub-directories is just not there.

The only resolution is to upgrade or grab the complete accumulative service pack according to Microsoft.

On one of the servers that I wanted to implement this on is going away soon so I didn't want to apply any updates but I still needed to implement a Maintenance plan that would remove old backups. The solution was to create a "Maintenance Cleanup Task" for every back folder containing the backup files that I wanted to have deleted. Luckily it was only 9 folders that I had to deal with.

For using xp_delete_file out side of the Maintenance plan here is some information I have gathered.

Usage:
SQL Server Version 9.0.1399
EXECUTE master.dob.xp_delete_file 0,N'E:SQLbackupdir',N'bak',N'06-03-2011 08:00:00'

On different versions I noticed you could use the parameter at the end to tell it to go into sub-directories but only the first level.

EXECUTE master.dbo.xp_delete_file 0,N'E:SQLbackupdir',N'bak',N'06-03-2011 08:00:00',1

So here it is broken down.

,N'E:\SQLbackupdir' - path of the directory that has the backup files
,N'bak' - extention of the backup file
,N'06-03-2011 08:00:00' - it will remove everything older than this date
,1 - some version have this parameter its for sub-directories