Help on deleting old backup files

Question

Post reply

Help on deleting old backup files

Jonathan Stokes

SSCrazy Eights

Points: 9861
More actions
July 6, 2007 at 3:24 am

#120737

Can anyone tell me the parameters used in xp_delete_file.
I have the xp working and can delete backup files older than 7 days in a folder.
In particluar I am looking for a paramater to enable me to delete through all the subfolders without having to write a looping procedure of some kind.
------------------------------
The Users are always right - when I'm not wrong!

Viewing 8 posts - 1 through 7 (of 7 total)

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

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

Hi

take a look at this. i copied the same from another site.

xp_delete_file

0, -- delete files

N'\\server02\dbbackup\sql2005', -- full path to the main directory

N'trn', -- file extension

N'08/08/2006 13:29:51', -- delete files created before this timestamp

1 -- Including first-level subfolders

"Keep Trying"

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 2

I tried that and it did not work.

In fact, I cannot even delete the files by pointing directly at the subfolder with the older files in it now.

------------------------------
The Users are always right - when I'm not wrong!

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 3

Thanks, Chiraq.

That works. It appears that you cannot delete dumps from 2000 sql for some reason using xp_delete_file.

------------------------------
The Users are always right - when I'm not wrong!

Ivan Weaver SSC Veteran Points: 204 More actions · Answer 4

I have found that Uppercase letters for the extension are needed.

I did

DECLARE

@dtOlderThanThisDate datetime

DECLARE @SearchDepth int

SET

@dtOlderThanThisDate = DateAdd(dd, -7, GetDate())

SET @SearchDepth = 1

EXEC

master.sys.xp_delete_file 0

, '<Fullpath to Backup Root,varchar(255),UNC or Local Path>'

, 'trn'

, @dtOlderThanThisDate

, @SearchDepth

and nothing happened - it said 'Completed Successfully' with no pause after hitting F5.

So, I changed the extension and tried with 'TRN' in place of the 'trn' and wuddayaknow, it took about 3 seconds to run (about what I figured with 13 sub-directories to search)

Ivan Weaver SSC Veteran Points: 204 More actions · Answer 5

Update:

It seems that I was a little wrong - When using one server to delete files on a remote server , setting the connection is not sufficient to remove the files using 'Local' references to the remote server.

Using: 'G:\SQLData\Backups' and 'TRN' does not work when G: is relative to the 'Connection' object. It seems that UNC is what is required.

So: '\\Remote1\G$\SQLData\Backups' and 'TRN' is the ticket.

Deepak-405225 SSCertifiable Points: 6120 More actions · Answer 6

you can make use of xp_cmdshell to delete the old files,

xp_cmdshell 'del path\.bak' will remove all the .bak files in the particular path you specify......xp_delete_file works well in sql 2005.....it is used in the cleanup task of the maintenance plan, its called inside the cleanup task

[font="Verdana"]- Deepak[/font]

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 7

Hi Folks

I just encountered this issue with Maintenance Cleanup Task

Error messaeg:

Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'',N'bak',N'2009-06-16T14:19:14'

" failed with the following error: "Error executing extended stored procedure: Invalid Parameter".

Possible failure reasons: Problems with the query,

"ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

T-Sql:

EXECUTE master.dbo.xp_delete_file 0,N'''',N''bak'',N''2009-06-16T14:19:14''

Executed in SSMS:

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'bak'.

Any idea on what the issue is since the Maint Plan is generating the code

Thanks

Jim