Maintenance Plan mystery

  • 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

  • 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

  • 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

  • These is no "." in the extension . I just kept it as "bak". Thanks.
  • 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

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

  • 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 6 (of 6 total)

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