SQL Server xp_delete_file not deleting files

Question

I'm trying to write some SQL that will delete files of type '.7z' that are older than 7 days.

Here's what I've got that's not working:

DECLARE @DateString CHAR(8)
SET @DateString = CONVERT(CHAR(8), DATEADD(d, -7, GETDATE()), 1)
EXECUTE master.dbo.xp_delete_file 0, 
                  N'e:\Database Backups',N'7z', @DateString, 1

I've also tried changing the '1' a the end to a '0'.

This returns 'success', but the files aren't getting deleted.

I'm using SQL Server 2005, Standard, w/SP2

Answer 1

Had a similar problem, found various answers. Here's what I found.

You can't delete 7z files with xp_delete_file. This is an undocumented extended stored procedure that's a holdover from SQL 2000. It checks the first line of the file to be deleted to verify that it is either a SQL backup file or a SQL report file. It doesn't check based on the file extension. From what I gather its intended use is in maintenance plans to cleanup old backups and plan reports.

Here's a sample based on Tomalak's link to delete backup files older than 7 days. What trips people up is the 'sys' schema, the trailing slash in the folder path, and no dot in the file extension to look for. The user that SQL Server runs as also needs to have delete permissions on the folder.

DECLARE @DeleteDate datetime
SET @DeleteDate = DateAdd(day, -7, GetDate())

EXECUTE master.sys.xp_delete_file
0, -- FileTypeSelected (0 = FileBackup, 1 = FileReport)
N'D:\SQLbackups\', -- folder path (trailing slash)
N'bak', -- file extension which needs to be deleted (no dot)
@DeleteDate, -- date prior which to delete
1 -- subfolder flag (1 = include files in first subfolder level, 0 = not)

Note that xp_delete_file is broken in SP2 and won't work on report files; there's a hotfix for it at [http://support.microsoft.com/kb/938085]. I have not tested it with SP3.

Since it's undocumented, xp_delete_file may go away or change in future versions of SQL Server. Many sites recommend a shell script to do the deletions instead.