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…

EXECUTE master.dbo.xp_delete_file
0,                                                    -- Either a 0 (Backup File) or 1 (Maintenance Plan Log File)
N'',                                                  -- Location of Files. I use a remote SAN storage location.
N'TRN',                                            -- Extension of file to clean up.
N'2012-03-28T09:38:06',                    -- Date threshold to delete files older than...
1                                                     -- 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.

Brandon has worked in IT for nearly 20 years, and currently serves as a SQL Server DBA for a healthcare company in California. In his spare time, he runs for miles and helps maintain RealCajunRecipes.com. Brandon is a certified SQL Server administrator.

Posted in SQL Server
3 comments on “When xp_delete_file doesn’t work as expected…
  1. Brett says:

    Thanks, this is the first post to highlight the fact that it is reading the header of the files, which as highlighted an important distinction from a simple delete command.

  2. Kristy says:

    I had this same problem. I have a folder structure which only contains .bak files, so I replaced the ‘.bak’ with a ‘*’. Worked for me.

  3. Bogdan Dumitru says:

    I agree with what Brett said! This was the first post that stated the fact that ta xp_delete_files procedures it is reading the header of files… so it actually solved my problem, because , knowing that, i figured out that my problem was actually that my cleanup job is on sql 2005 server , and it try to delete different backups from wich ones are compressed backups… and sql server 2005 does not have a clue what it is a compressed backup…

    So, thanks Brandon!

Leave a Reply

Your email address will not be published. Required fields are marked *