xp_delete_file does not remove older backup files

Question

Post reply

xp_delete_file does not remove older backup files

mauro.branca

Old Hand

Points: 379
More actions
March 25, 2016 at 4:35 am

#314239

Hi All,
My issue is the following:
O.S. Microsoft Server 2012 R2
SQL Server 2014 with mixed autentication mode build 12.0.2269
user connected: domain admin (so with full rights all over the disk)
Maintenance procedures tries to delete old backup files, with a command like this
---
DECLARE @ReturnCode int
EXECUTE
@ReturnCode =
[master].sys.xp_delete_file
0,
N'C:\temp',
'trn',
'2016-03-23T08:00:00'
IF @ReturnCode <> 0 RAISERROR('Error deleting files.', 16, 1)
---
the command runs succesfully, no errors on SQL logs, no erros on Windows logs, but files are still there...
I tried with "[master].dbo.xp_delete"_file too, some results
Thanks for Your help.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Jon.Morisi SSChampion Points: 12846 More actions · Answer 1

Another possible route is to make a CmdExec job step that runs a del cmd.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

Heh... it's funny that you brought this up. I've found and deleted more than 60,000 such files on one of my legacy servers. It believe it may be that it won't find files unless they're in one of the tables in MSDB as a part of the backup set. Not sure about that, though and won't be in the near future. I changed the backup system we have to do it though a call to xp_CmdShell and to make it so it ensure there are at least two good backups available along with the related trans files. Can't trust things like the simple age of a file.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mauro.branca Old Hand Points: 379 More actions · Answer 3

thanks jon, Jeff.

First of all: my issue is born after upgrading my SQL system from 2005 to 2014, I use maintenance procedures of Ola Hallengren - allways worked fine in SQL server 2005.

Obviously if I'll not solve this issue, I'll search some other solution, but I was curious to understand what's happening...

Thanks for Your answers, I'll do some more test.

Mauro

WorkasDBA SSCommitted Points: 1684 More actions · Answer 4

Sometimes it likes a dot with extension '.trn' or '.bak'

rvsc48 SSCertifiable Points: 7685 More actions · Answer 5

The retention time to keep backups on disk is embedded within the backup script which Ola provides - Example - sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d zUTILITY -Q "EXECUTE [dbo].[DatabaseBackup] @databases = 'USER_DATABASES', @Directory = N'G:\Backups', @BackupType = 'FULL', @verify = 'N', @CleanupTime = 24, @checksum = 'Y', @LogToTable = 'Y'" -b.

What happens if you run this code instead, taking special note of the @CleanupTime variable?

Michael L John One Orange Chip Points: 27055 More actions · Answer 6

I'm with Jeff.

There some sort of secret that makes this work, or keeps it from working.

I took your code, and try to run it in every way possible. It did not work on the local folder on my PC

I created a maintenance plan, picked the proper values, and copied the T-SQL code from the step of the plan.

It worked.

The code from the plan:

EXECUTE master.dbo.xp_delete_file 0,N'C:\Backups',N'bak',N'2016-03-28T15:18:03',1

The parameters, as I understand them, from this link http://www.patrickkeisler.com/2012/11/how-to-use-xpdeletefile-to-purge-old.html

File Type = 0 for backup files or 1 for report files.

Folder Path = The folder to delete files. The path must end with a backslash "\".

File Extension = This could be 'BAK' or 'TRN' or whatever you normally use.

Date = The cutoff date for what files need to be deleted.

Subfolder = 0 to ignore subfolders, 1 to delete files in subfolders.

Which appears to be incorrect, because the code generated has no backslash.

I use Ola's scripts. That seems to work the majority of the time. But on certain servers, it simply does not work.

One thing. You said

user connected: domain admin (so with full rights all over the disk)

I think the account running SQL is what needs the permissions, not the user running the code.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

mauro.branca Old Hand Points: 379 More actions · Answer 7

WorkasDBA:

Sometimes it likes a dot with extension '.trn' or '.bak'

Grasshopper:

The retention time to keep backups on disk is embedded within the backup script which Ola provides -

Example - sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d zUTILITY -Q "EXECUTE [dbo].[DatabaseBackup] @databases = 'USER_DATABASES', @Directory = N'G:\Backups', @BackupType = 'FULL', @verify = 'N', @CleanupTime = 24, @checksum = 'Y', @LogToTable = 'Y'" -b.

What happens if you run this code instead, taking special note of the @CleanupTime variable?

Michael L John:

(...) I think the account running SQL is what needs the permissions, not the user running the code.

----

thanks all for Your replies.

WorkasDBA: I know this is a frequent error, but nothing change (with or without the initial dot).

Grasshopper, Michael: the command I wrote in my post is obtained directly from stored procedures created by Ola's maintenance script.

I Know that the account runnins SQL is what need the permissions, but trying to investigate about my problem I "extrapolated" the delete

command and I tested with an user that surely doesn't have permissions problems...

Mauro.

Adrian_1 SSC Veteran Points: 280 More actions · Answer 8

It is worth noting that the Date applies to the Created Date and not the Modified Date

!

nizamettin@hotmail.com Newbie Points: 1 More actions · Answer 9

I have experienced the same problem and found the solution at the end. I have given "full control" permission on the folder which is the files to be deleted to SQL Server service account. Then the job deleted old files.

jcarranza 23978 Ten Centuries Points: 1380 More actions · Answer 10

For anything windows file related I recommend you use Powershell.

Create the Poershell script. Save it. Schedule a Task Schedule Job and run the Powershell at a specific time.

as_1234 SSCrazy Points: 2859 More actions · Answer 11

Something I've found on 2016 and 2017 (I appreciate you are using 2014, I don't know if it's the same) is that if you use a maintenance plan that does the backup and then deletes old files and you don't use the verify backup option the delete doesn't work.

Please note this is something I found through testing. It is not something that I've seen documentated so I can't confirm whether it will work for you or if it could cause other problems. It worked for me but I don't know why.