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

Thank you

Answer 1

Try changing the first parameter from 0 to 1.

Here is a small summary on xp_delete_file I just found. Sounds a bit like you'd be out of luck with this procedure.

Answer 2

Tomalak: that didn't work.

Answer 3

AFAIK xp_delete_file only delete files recognized by SQL Server 2005 (backup files, transaction logs, ...). Perhaps you can try something like this:

xp_cmdshell 'del <filename>'

Answer 4

smink: I've tried changing the file extension to .bak without luck. Does it look into the file to see if I'm messing with it instead of just looking at the extension?

Answer 5

This sp will only will only delete native sql server backup files or native maintenance report files (for security purposes)

As Smink suggested you can use

xp_cmdshell 'del <filename>'

With the proper permissions on the folder.

Answer 6

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.

Answer 7

I get an error when i run this ...my .bak files are on D:\sqlbackup so no change there what else am i missing. I would however have to change the date to -2 since we have no space avail.

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

EXECUTE master.sys.xp_delete_file 0, -- FileTypeSelected (0 = FileBackup, 1 = FileReport) N'D:\SQLbackup\', -- 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)