SQL Server xp_delete_file parameters

Who can explain list of xp_Delete_file parameters in sql server. I don't find msdn document for this stored procedure.

I got script of xp_Delete_file from maintenance plan but didn't understand first parameter.

4 Answers

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

Following on from the comment above, I have been testing this on SQL Server 2012 SP4 and I can confirm the syntax EXEC master.dbo.xp_delete_file 0, 'C:\Some Path\Backup file.bak' works and deletes the specific named file.

I found this to be more obvious, since I like showing parameters:

DECLARE @DeleteDate DATETIME = DATEADD(wk,-2,GETDATE()); DECLARE @ReturnVal int EXEC @ReturnVal = master.dbo.xp_delete_file @FileType = 0, @FolderPath = N'U:\SQLBackups', @FileExtension = N'bak', @Date = @DeleteDate, @Subfolder = 1 print @ReturnVal 

Note the following extra information (tested on SQL Server 2019 in a Microsoft Windows environment... I can't speak for other OSs):

• the @Date parameter can include a time component;
• it is accurate to at least 1 second (I haven't tested milliseconds), suggesting the parameter is of type datetime (at least);
• the SP uses the Date Modified OS property (not the Date Created).