locked
maintenance clean up task not deleting backup files RRS feed

  • Question


  • I have one maintenance plan configured in my SQL server 2008 RTM enterprise edition environment in which first cleanup task is set then full backup job set.
    suddenlty cleanup task is not deleting old files. because of this my drive gets full
    earlier it was working fine. I have created another cleanup task & provided path of my backup files.task is executed but its not deleting any backup files.I have restarted the sql agent also but no luck. there is no any error or log i have observed.
    All settings are perfect, not sure why its happen.
    Please help
    Wednesday, June 12, 2013 11:29 AM

Answers

  • Thanks Fanny for your reply.

    service account of the SQL Server Agent Service  already have sysadmin permission.but i have found that sql server service doesn't have sysadmin permission.just for testing purpose when I provided SA rights to sql service id & execute the job it was running successfull & deleted old backup files. I have done this 2-3 times, when i remove SA rights from sql service id  job was deleting backup files & when i reassigned SA rights it deleted all old backup files. I was really surprised.  My problem is resolved.

    could you tell me why this things happened?  is it really required to have SA rights to service id? because when i have done this testing on my home testing machine, then without SA rights job is working fine.

    • Marked as answer by Mayur-DEW Monday, June 17, 2013 6:20 PM
    Thursday, June 13, 2013 9:51 AM

All replies

  • Please post a screenshot of the maintenance plan settings..

    • Proposed as answer by achievers Saturday, March 23, 2019 4:31 AM
    Wednesday, June 12, 2013 11:49 AM
    • Proposed as answer by achievers Saturday, March 23, 2019 4:31 AM
    Wednesday, June 12, 2013 11:56 AM
  • please go through below link..same discussion here do tell us it matches ur issue..if not we will take another course of action

    http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/53a4afff-79ad-4202-8840-67958a3fd334

    PS: upgrade ur sql server to SP3


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Wednesday, June 12, 2013 12:06 PM
  • Look fine

    Are you sure the job is executing with no errors? And there are actually files older than 18hrs?

    Wednesday, June 12, 2013 12:07 PM
  • I have tried to delete the files by executing below query . command executed successfully but no luck.
    EXECUTE master.dbo.xp_delete_file 0,N'E:\Backups',N'bak'

    As per shankey's link i am facing the same problem,might be there is a problem with AD account

    will try. if any one has different solution pls post it.

    Wednesday, June 12, 2013 1:17 PM
  • Hello,

    Please try to grant the service account of the SQL Server Agent Service  with sysadmin premission on the instance and "Full control" premission to the backup folder.

    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support

    Thursday, June 13, 2013 6:24 AM
  • Thanks Fanny for your reply.

    service account of the SQL Server Agent Service  already have sysadmin permission.but i have found that sql server service doesn't have sysadmin permission.just for testing purpose when I provided SA rights to sql service id & execute the job it was running successfull & deleted old backup files. I have done this 2-3 times, when i remove SA rights from sql service id  job was deleting backup files & when i reassigned SA rights it deleted all old backup files. I was really surprised.  My problem is resolved.

    could you tell me why this things happened?  is it really required to have SA rights to service id? because when i have done this testing on my home testing machine, then without SA rights job is working fine.

    • Marked as answer by Mayur-DEW Monday, June 17, 2013 6:20 PM
    Thursday, June 13, 2013 9:51 AM