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.

EXECUTE master.dbo.xp_delete_file 0,N'G:\<Backup file path>\<your database name>',N'bak',@olddate,1
EXECUTE master.dbo.xp_delete_file 0,N'G:\<Backup file path>\<your database name>',N'bak',@olddate,1



-- Backup file path example : G:\Program Files\Microsoft SQL Server\MSSQL\MSSQL.1\Backup\<your database name>

TheHose (4/11/2011)

---

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;

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.

TheHose (4/12/2011)

---

Version is SQL Server 2008

Microsoft SQL Server Management Studio 10.0.2531.0
Microsoft Analysis Services Client Tools 10.0.1600.22
Microsoft Data Access Components (MDAC) 3.86.3959
Microsoft MSXML 2.6 3.0 4.0 6.0
Microsoft Internet Explorer 8.0.6001.18702
Microsoft .NET Framework 2.0.50727.3615
Operating System 5.2.3790


Oh it's still an issue!!!!!