I have used the following, but it works only 80% of the time. Sometimes it will not delete the required files. And I don't know why, this bug has been around for sometime and I hope Microsoft comes up with a fix, or most DBAs will be asking for Oracle as a replacement;
- Begin code block
DECLARE @currentdate datetime
DECLARE @olddate datetime
set @currentdate = CURRENT_TIMESTAMP
set @olddate = @currentdate - 1 -- Change number here depending how many days of backup file you want to retain on server.
-- Add below line for each database and/or path.
EXECUTE master.dbo.xp_delete_file 0,N'E:\Backup\SQL_INDEX_REBUILD',N'bak',@olddate,1
- End code block
As I stated before this will work but it's not reliable..... I would like to thank all of you and this forum which has been the only real workaround I have seen in 4 months. If anybody can offer any other insight to this issue, please do.