I discovered some servers a while ago that had a real problem, hundreds of thousands of maintenance plan text log files (and also agent job step output files) hoarded into the ERRORLOG directory.

At first I thought I could just use master.sys.xp_delete_file to clear them up but I had a few concerns holding me back.

  • I wasn't sure how the date criteria worked; was it checking the date in the filename, the creation date, or some other date?
  • I wasn't sure if it would delete files up to, or up to and including, the specified date.
  • I wasn't sure if it would recurse more than one folder deep.
  • I wasn't sure exactly what would be deleted, e.g. other text files, or misnamed files.

To test it out I created a simple maintenance plan storing backups and the maintenance plan log files to C:\Backup. I also used the View T-SQL button to get the format of the command.

Parameters

master.sys.xp_delete_file

  • File Type
    • 0 for Backups
    • 1 for Maintenance Plan Logs
  • Folder Name
  • Extension
  • Date (spoiler: up to and not including)
  • Include Sub Folder (spoiler: only one)

Question 1: How does the date criteria work?

I triggered a backup and made some copies of the maintenance plan log file in another folder (not a sub folder). Then I constructed a query to delete the file, and run a battery of tests.

Exec	master.dbo.xp_delete_file 1, N'C:\Backup', N'txt', N'2015-06-02T22:00:00', 1
  • Test run.
    File deleted.
  • Renaming the file (e.g. to 1.txt).
    File deleted. This proves the date is not based on what's encoded in the filename.

  • Changing the creation timestamp to the future.

    Get-ChildItem . "*.txt" | %{ $_.CreationTimestamp = (Get-Date).AddDays(1) }

    File deleted. This proves the date is not based on the creation timestamp.

  • Changing the date modified to the future.

    Get-ChildItem . "*.txt" | %{ $_.LastWriteTime = (Get-Date).AddDays(1) }

    File not deleted. This proves the date is based on the date modified (aka last write time).

Question 2: Is the date specified up to, or up to and including?

Get-ChildItem . "*.txt" | %{ $_.LastWriteTime = [DateTime] "2015-06-02 00:00:00" }

Exec	master.dbo.xp_delete_file 1, N'C:\Backup', N'txt', N'2015-06-02T00:00:00', 1

File not deleted. It's up to, not up to and including. I added a second to the query and the file was deleted, confirming this behaviour.

Question 3: Does it recurse any deeper into the sub folders?

This was easy. I put a file two sub folders deep and ran the test again.

File not deleted. So it really does only go one sub folder deep.

Question 4: What exactly gets deleted?

  • I ran the query to delete backup files, and renamed the existing maintenance plan log file to bak.
    File not deleted. Backup files have to be SQL Server backup files.
  • I ran the query to delete maintenance plan text files but added a carriage return to the start of the file.
    File not deleted. Maintenance plan files have to start with a specific header, exactly.

After some experimentation the magic words in the header are "Microsoft(R) Server Maintenance Utility". Any 0 or more characters can follow after that.

Case closed, though this also meant it wouldn't be possible to use this to delete agent job step files (as they don't have that as their first line), and I would have to resort to remote PowerShell instead.

Parting notes

Of course later I found Brandon Abshire had blogged about the same procedure, as had Patrick Keisler:

Don't let the stored procedure file name, xp_delete_file, fool you. Microsoft has some internal code that only allows for the deletion of database and transaction log backup files. The stored procedure cannot be used to delete any other type of file.

But I didn't feel my time was wasted as I've clarified it a little further.