0

This command is not deleting backups:

EXEC xp_delete_file 0,N'F:\path\cms',N'*.bak',N'2014-01-30T21:08:04'

Also tried

EXEC xp_delete_file 0,N'F:\path\cms',N'bak',N'2014-01-30T21:08:04'

and

EXEC xp_delete_file 0,N'F:\path\cms',N'.bak',N'2014-01-30T21:08:04'

SQL Server Agent has permissions on the folder.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
Jeff Shain
  • 767
  • 3
  • 9
  • 21

4 Answers4

4

Did you try:

EXEC xp_delete_file 0,N'F:\path\cms\',N'bak',N'2014-01-30T21:08:04';
--- this slash may be important ---^

That said, you should simply not be using this stored procedure to clean up your backup folders. It is undocumented and unsupported. Take a look at this Connect item, which complains about exactly the same problem, seven years ago to the day. Note that it is closed as "won't fix" and of particular interest should be this official statement from Terrence Nevins of Microsoft:

The stored procedure was never intended to be called by an end user and the chances that you could ever be successful in having it do what you intend are almost zero. I took a quick peek at the implementation we are using and it takes some very specialzed arguments for a very specific task.

If you detemine that you do need to access the file system directly from a stored proc, then I would imagine you would need to write that yourself in .net. Or perhaps there is already a vendor that provides this.

We don't document this XP or promote its usage by anyone for very good reasons. Most of all, they can 'go away' from release to release.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • the t-sql is generated by the maintenance wizard task, which is also not deleting the old backups. I'm running it manually as a test but neither works. – Jeff Shain Feb 03 '14 at 07:51
  • @JeffShain if you want something you can rely on, you'll have to use your own. There are many replacements out there if you look. Hint: cleaning up old backup files doesn't have to be integrated into the maintenance plan (same goes for several other things that shouldn't be done through maintenance plans, too). – Aaron Bertrand Feb 03 '14 at 13:10
2

Solved: Both users for agent and sql server service need read/write/delete permissions on the backup folder.

Jeff Shain
  • 767
  • 3
  • 9
  • 21
1

Make sure that you have "Full Control" on the directory where your backups are. Unfortunately running the xp_delete_file doesn't return an error if you don't have the correct privs nor do you see anything in your SQL Server agent log files.

RobF
  • 11
  • 1
  • Please augment your answer to explain what "Full Control" means, or how to achieve it. Otherwise, this may be considered to be a comment, not an Answer. You will gain the ability to comment when your gain additional reputation. – SherylHohman Dec 28 '17 at 21:31
0

The path name must end with a \ and the extension must not contain the dot. Then it will work.

Dennis Munninghoff
  • 19