none
SQL 2005 Maintenance Cleanup Task is not deleting files on remote file share

    Question

  • I have the following issue with Maintenance plan backups that work for BAK DIF and TRN to a remote server share.

    When I try and remove the old files with a clean up task I get an error and the files don't get deleted.

     

    The version is as follows
    Microsoft SQL Server 2005 - 9.00.3054.00 (X64)   Mar 23 2007 18:41:50  
    Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition (64-bit) on
    Windows NT 5.2 (Build 3790: Service Pack 2) 

    The error result is as follows, 

    FailedSad-1073548784) Executing the query "EXECUTE master.dbo.xp_delete_file
    0,N'\\\\EXECUTE master.dbo.xp_delete_file 0,N'\\\ABCD-A1\\BACKUPS\\ABCD_BACKUP\\ABC_DAILY\\ABCD',N'trn',N'2008-01-13T12:52:49'" failed with the following error: "xp_delete_file() returned error 2, 'The
    system cannot find the file specified.'". Possible failure reasons: Problems
    with the query, "ResultSet" property not set correctly, parameters not set
    correctly, or connection not established correctly.

    The maintenance plan seems to be adding extra "\" though when i enter the
    code directly in a query i get same error.

     

    Query:

    EXECUTE master.dbo.xp_delete_file 0,N'\\ABCD-A1\BACKUPS\ABCD_BACKUP\ABC_DAILY\ABCD',N'trn',N'2008-01-13T12:52:49'


    Error:

    xp_delete_file() returned error 2, 'The system cannot find the file specified.'

     

    The servers belong to the same domain and are using the same Service account which has all the necessary rights to the share and the file directory location. The backups work but i get the error on the cleanup task.

     

    Trying to figure out how to get the Cleanup task to delete old files. The same happens for all file extensions and I have tried other locations with simpler file paths same error.



    Regards,
    Scott

     

    Wednesday, January 16, 2008 12:29 AM

All replies

  • The Service account for the SQL Server must have FULL CONTROL rights on the remote file share, both through the sharing UNC and the underlying NTFS file system.

     

    Monday, January 21, 2008 4:36 PM
  • I had similar problems so i made the job be the sa account it worked after this, using the SQL account wouldn't

     

    Saturday, January 26, 2008 3:32 PM
  • Hi I am not an Programmer just a server admin.. 

     

    I have managed to setup a backup job to backup a database onto the local drive (.bak files) I would like to setup auto deletion of the backup files 8 days after creation can you guys help me?  I have tired History clean-up task but not sure if this is what i should be doing as there has been no affect on the old backups...

     

     

    Thanks in advance for your help

     

    Marcus

     

    P.S hope you dont mind me jumping onto your post just thought they are similar and may get the same sort of answers..

    Monday, January 28, 2008 11:49 AM
  • Yes maintenance clean up tasks, search for folder and delete put your path here and then in file extension put in bak tick the include first level sub folders,   delete files after x days.

     

    This will then create a SQL job look under sql server agent,  here i used the login of sa if i used another account i had problems so i left this as sa.   It should be able to run as the account that sql is running but i could not get it to work.

     

     

    Monday, January 28, 2008 2:12 PM
  •  SQLCraftsman wrote:
    The Service account for the SQL Server must have FULL CONTROL rights on the remote file share, both through the sharing UNC and the underlying NTFS file system.

     



    Thanks for the info I was having the same problem, this done the trick.
    Monday, July 14, 2008 10:25 AM
  • Remember, the sql agent service account must be sysadmin and have the rights in the folder where the backup files reside.

    Second, if you setup the maintenance plan remember not to put the ".bak" in the file extension just put "BAK" because the job is going to failed and give you the message of cannot find the file.

    I'd tested with maintenance plans in 2005/2008 and both of them work.

    If you have any concern or any question don't hesitate in contact me.

    Have a nice day.

    Thursday, January 12, 2012 3:29 PM