SQL Server Xp_Delete_File Not Deleting Files

SQL Server xp_delete_file not deleting files

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.

xp_delete_file not removing old backups

Solved: Both users for agent and sql server service need read/write/delete permissions on the backup folder.

SQL Server xp_delete_file parameters

xp_delete_file take five parameters:

1. File Type = 0 for backup files or 1 for report files.
2. Folder Path = The folder to delete files. The path must end with a backslash "".
3. File Extension = This could be 'BAK' or 'TRN' or whatever you normally use.
4. Date = The cutoff date for what files need to be deleted.
5. Subfolder = 0 to ignore subfolders, 1 to delete files in
   subfolders.

Source How to Use xp_delete_file to Purge Old Backup Files by Patrick Keisler

SQL Maintenance Cleanup task not deleting any files, SQL installed on a DC

It turned out that this "login missing" is not a Windows login, but rather SQL "login" which was not present for the service account. So I needed to create a "DOMAIN\mssql_srv" login in SSMS, give it "public" access rights and voila, files started to get deleted properly. The reason is explained in comment:

If it's T-SQL step and job owner is member of sysadmin server role, the step is executed under service account.

How to delete files on the directory via MS SQL Server

xp_cmdshell requires that a literal string be passed as parameter. You cannot construct a value on the fly.

Try this:

DECLARE @cmd NVARCHAR(MAX) = 
'xp_cmdshell ''del "C:\root\sfd_devtracker\' + @deletefile + '"''';
EXEC (@cmd)

Consider that xp_cmdshell must be enabled, for instance in this way.

MYDOMAIN\MSSQLUser. xp_delete_file() returned error 2, 'The system cannot find the file specified.' [SQLSTATE 42000] (Error 22049). The step failed

I have found the solution. In the script below I removed last value 1 and it worked finely.

DECLARE @DELETE_DATE NVARCHAR(50),
        @DELETE_DATE_TIME DATETIME;

SET @DELETE_DATE_TIME = DATEADD(DAY, -7, GETDATE())
SET @DELETE_DATE = (SELECT ( REPLACE( CONVERT( NVARCHAR, @DELETE_DATE_TIME, 
111), '/', '-') + 'T' + CONVERT( NVARCHAR, @DELETE_DATE_TIME, 108)))

EXECUTE xp_delete_file 0, N'location', N'BAK', @DELETE_DATE

---

Related Topics