Maintenance Plan mystery

  • Arsh

    SSCertifiable

    Points: 6092

    Hello Everybody,
    A maintenance plan in one old server (running SQL server 2008 R2 SP3)  has 'backup' and 'Maintenance Clean up' tasks to perform . Though the backup part does its job , the clean up task doesn't delete the old files. Strangely it reports as successful . In experience I could work around by deleting and recreating the whole plan / job but this isn't working for this server . Any help ?

    Thank you...Arshad

  • Sue_H

    SSC Guru

    Points: 90673

    Arsh - Monday, August 14, 2017 2:31 AM

    Hello Everybody,
    A maintenance plan in one old server (running SQL server 2008 R2 SP3)  has 'backup' and 'Maintenance Clean up' tasks to perform . Though the backup part does its job , the clean up task doesn't delete the old files. Strangely it reports as successful . In experience I could work around by deleting and recreating the whole plan / job but this isn't working for this server . Any help ?

    Thank you...Arshad

    There can be a few different reasons. The most common is using the extension .BAK (with a dot) instead of just BAK (without the dot)
    Pointing to the wrong folder or not using the Include First-Level Subfolders when needed can also be problems. Also double check the workflow/precedence constraints.

    Sue

  • Kenny Jozi

    SSCrazy

    Points: 2004

    Sue_H is right , maintenance plans don't delete file if they have ".' on the extension. Also check if the SQL Agent account has access to delete in the directory

    SQL 2000/2005/2008/2012 DBA - MCTS/MCITP

  • Arsh

    SSCertifiable

    Points: 6092

    These is no "." in the extension . I just kept it as "bak". Thanks.
  • Sue_H

    SSC Guru

    Points: 90673

    Arsh - Tuesday, August 15, 2017 4:07 AM

    These is no "." in the extension . I just kept it as "bak". Thanks.

    And the four others? As I said, it can be a few different things - the naming of the extension being the most common. Kenny and I mentioned others. And what is the t-sql for the task? And is there other information in the report and are you logging extended information?

    Sue

  • Arsh

    SSCertifiable

    Points: 6092

    Sue_H - Tuesday, August 15, 2017 8:14 AM

    Arsh - Tuesday, August 15, 2017 4:07 AM

    These is no "." in the extension . I just kept it as "bak". Thanks.

    And the four others? As I said, it can be a few different things - the naming of the extension being the most common. Kenny and I mentioned others. And what is the t-sql for the task? And is there other information in the report and are you logging extended information?

    Sue

    The remaining ones are also correct Sue. I'm considering creating a dos batch file to do the delete by calling it from the Maintenance plan as work around .  Thank you.

  • Sue_H

    SSC Guru

    Points: 90673

    Arsh - Thursday, August 17, 2017 8:58 AM

    The remaining ones are also correct Sue. I'm considering creating a dos batch file to do the delete by calling it from the Maintenance plan as work around .  Thank you.

    Don't think you can use any of the Maintenance tasks directly to do that in SQL Server 2008. You may want to consider just a job step so it's not as convoluted.
    If it's none of the issues posted and the file isn't in use, you get no  error and have nothing wrong and keep having the issue (you said you have always rewritten them before), you should open up a case with Microsoft. Not sure how a bug wouldn't have been picked up in the almost ten year span that SQL Server 2008 has been around but I guess it could happen. They don't charge when it's a bug.

    Sue

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

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