When xp_delete_file doesn't work as expected...

Whether you use a Maintenance Cleanup Task as part of a SQL 2005 or 2008 Maintenance Plan, or script out your own cleanup t-SQL, you are probably using the xp_delete_file extended procedure. For quite a while, I've occasionally come across old TLog or backup files that were missed. Without questioning it, I would clean them up manually.

Recently I had to disable my 3rd party compression utility due to issues and noticed that all of the SQL instances using that compression utility stopped cleaning up their TLog files. I decided I had to investigate why this was happening. My original assumption with xp_delete_file was that it went to a directory that you specify, looked for the file extension and deleted any matches older than the date. But there is a missing component here.

Let's look at the syntax for xp_delete_file in my particular case...

1EXECUTE master.dbo.xp_delete_file
20,                          -- Either a 0 (Backup File) or 1 (Maintenance Plan Log File)
3N'',                        -- Location of Files. I use a remote SAN storage location.
4N'TRN',                     -- Extension of file to clean up.
5N'2012-03-28T09:38:06',     -- Date threshold to delete files older than..
61                           -- 0 (Current Directory) or 1 (Recursively through subdirectories)

The secret to this procedure is the first argument, the specification of the type of file you are deleting. You may wonder why it would care what kind of file it is, as long as you can match up the extension, but the procedure is actually reading the header of the files to determine that they are indeed backups.

This is why my backups stopped cleaning up after I disabled my compression software. The native ability of SQL Server could not read the header of my TLog files, and therefore could not verify that they are backup files. So it did not delete them. The issues I had occasionally with missed files were with corrupted backup files, usually aborted during writing for one reason or another.

In order to get around the issue, I tried telling the procedure that the files were just logs, but that was not successful in cleaning up my backups either. So the decision has to be made on if this is acceptable behavior, or if I want to go through the trouble of crafting a new method of deletion for all of my SQL installs. At this point, I think I can live with it, even though forcing a deletion would be handy.