SQL Server 2008 R2 backup plan not deleting files older than X days set in maintenance plan

sql-server-2008

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?

Thanks.

Best Answer

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

Related Topic