Post reply

Help on deleting old backup files

  • 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!

  • July 6, 2007 at 3:52 am

    #717623

    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"

  • July 6, 2007 at 4:05 am

    #717625

    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!

  • July 6, 2007 at 5:23 am

    #717639

    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!

  • July 31, 2007 at 1:34 pm

    #723704

    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)

  • July 31, 2007 at 2:05 pm

    #723713

    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.

  • August 1, 2007 at 7:42 pm

    #724043

    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]

  • July 16, 2009 at 3:33 pm

    #1025927

    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

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

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