How is the new system stored procedure sys.xp_delete_files different from sys.xp_delete_file?

Question

SQL Server 2019 introduced a new (and undocumented) system stored procedure:

sys.xp_delete_files

What, exactly, does this new extended stored procedure do? How does it differ from xp_delete_file that has been around for years? Does it replace xp_delete_file?

Answer 1

To start with, the older xp_delete_file (also undocumented) is quite limited:

1. It can only delete either backup (.bak / .trn) or report (??) files (I believe it actually scans the first part of them to verify the file "type"
2. It only deletes by file extension (i.e. not specific files, or whatever matches based on wildcards) and based on being older than the supplied date
3. It's picky and requires a trailing slash on the directory / folder
4. It can only delete files, not folders
5. It can recurse down through subfolders (ok, so this one is not a limitation)

For more info on it, please see:

• How to Use xp_delete_file to Purge Old Backup Files
• SQL Server xp_delete_file not deleting files

---

The newer sys.xp_delete_files has the following syntax:

EXEC sys.xp_delete_files 'fileSpec.01' [, 'fileSpec.02' [, ...] ] ;

Notes:

1. File type / extension does not matter
2. Works with standard DOS wildcard characters:
   • * = zero or more of any character
   • ? = exactly one of any character
3. Does NOT recurse through subdirectories (for deleting files) (this is the only "limitation")
4. Can specify multiple, fully-qualified path specifications, each being able to handle wildcards
5. Can remove entire non-empty subfolder structure!! (might need to run a few times to delete everything)
6. Must be a member of the sysadmin fixed Server Role in order to execute (use Module Signing instead of adding application Login to sysadmin fixed Server Role; please see: Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level )
7. Backported to SQL Server 2017 (possibly in CU18)

For complete details, please see my post:

sys.xp_delete_files and ‘allow filesystem enumeration’: two new undocumented items in SQL Server 2019