We have a database maintenance plan set to do a full backup of all user databases nightly. The backups are set to expire after seven days and they are backed up to disk with the file extension "bak".

Problem is that the job is not deleting any backup sets older than seven days (same goes for the job that backs up the transaction log).

All the databases being backed up have their recovery models set to "Full".

SQL Server Agent is logging on as Local System and "SYSTEM" has full rights to the folders where these backups are bieng saved.

The jobs are not reporting any errors when they are manually run and there are no errors in the log files.

If I create a test maintenance plan to delete text files in a directory and run the generated T-SQL, the command completes successfully but the text file in the directory does not get removed.

EXECUTE master.dbo.xp_delete_file 0,N'D:\TEST',N'.txt'

I know it's a rights issue but everything looks okay on our end.

Anyone have any ideas?



As I understand you delete them in Maintenance plan using special Maintenance Cleanup Task .. or via "Execute T-SQL Statement Task"? Although it should not matter.

By looking at your SQL statement I think it could be the file extension: it should be without dot, just extension on it's own.

I doubt it is a permission issue as you are able to create backups in first place. But check if SQLServerMSSQLUser$... group has full access -- that is how I have on all "backup" locations (these permissions should be set up automatically if you set that folder as default backup destination during initial setup).

AFAIK xp_delete_file is undocumented and I have found no references in SQL Books to it. Apparently SQL Server checks what file it is before deleting it -- it works with own files only (to prevent wrong usage/security, I guess). Here are the format and parameters:

EXECUTE master.sys.xp_delete_file
0, -- FileTypeSelected (0 = FileBackup, 1 = FileReport)
N'D:\SQLbackups\', -- folder path (trailing slash)
N'bak', -- file extension which needs to be deleted (no dot)
N'2011-06-15T15:10:04', -- date prior which to delete
1 -- subfolder flag (1 = include files in first subfolder level, 0 = not)

By looking at your example SQL statement I see quite a few differences:

  • In first parameter you have 0 (File Backups) but attempting to delete Text file
  • File extension should exclude dot
  • You should provide expiry date (files older than that will be deleted)

Link: http://sqlblog.com/blogs/andy_leonard/archive/2009/03/11/xp-delete-file.aspx

  • No, I delete them in the backup maintenance plan using the "Backup set will expire" option. I just tried that statement to see if it was a problem with the plan or if it was a more general issue. I gave that user full control to the directory and it still doesn't work. – Tom Jun 29 '11 at 14:03
  • @Tom I have updated my answer with some info about xp_delete_file. Try changing your SQL command accordingly. In any case -- include Maintenance Cleanup Task in your maintenance plan to actually delete the unneeded files. – LazyOne Jun 29 '11 at 14:22
  • The SQL command was generated straight from the maintenance plan wizard for Maintenance Cleanup Task and I only included it here to illustrate that any deletion of files, whether it be through a SQL Server generated maintenance plan or the actual T-SQL generated to delete files, is not working. Also, it doesn't work with or without the dot. – Tom Jun 29 '11 at 14:35
  • have you included the trailing slash in the folder path? – johnh Jun 29 '11 at 17:07
  • Yeah, I've tried seemingly every permutation of variables. Went to another SQL Server we have where the plans are running fine and checked all the settings which are the same, still no dice. – Tom Jun 29 '11 at 17:47

This is just a shot, but I've been bitten by including a "." in the file extension box when creating maintenance plans. Check to see if you have ".bak" instead of "bak".

  • Yeah, no dice, dot or no dot =( Thanks for the suggestion though. – Tom Jun 29 '11 at 13:58
  • @Tom Are these native SQL Server maintenance plans or from a third party app like LiteSpeed? – squillman Jun 29 '11 at 14:14
  • Native maintenance plans. – Tom Jun 29 '11 at 14:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.