Maintenance plan delete sometimes fails

  • I have a strange issue... in addition to our normal backups, there are a bunch of databases on our production SQL server that I backup nightly to an attached drive. We have a separate server that we use for developing reports against, and I use these backups every month or so to copy over the most recent data.

    Anyway, the setup is very simple - I have a maintenance plan that first deletes any files in my folder older than one day, then it backs up the databases.

    My problem is that occasionally the delete step appears not to work. I don't see any errors in SQL or in the event logs on the server; it just fails to actually delete the previous backups. Unfortunately the drive is only large enough to keep one days worth, so the last few fail.

    I am wondering if anyone has any idea why these deletes would be failing and how to resolve this.

  • Probably a timing issue. Its not seeing the previous days backup as actually older than 1 day yet. To get around this can set maint plan to delete older than, say, 23 hours.

    Also, I understand you only have room for only 1 days backup, but I would caution you not to delete the only backup you have before creating another backup. If your backup should fail you will now have no backups. Either get more disk space for multiple backups and/or copy your existing backup to another server or tape before deleting.

  • Hmmm... I hadn't considered that 🙂 I will try changing to 23 hours and see if that does it.

    As for the one copy thing, these backups are for my convenience only; they are in addition to our primary backups, which have been working without issue.

  • Just to confirm that changing backup retention period from 1 day to 23 hours solved this mystery (from time to time failed old backup cleaning) for me too

Viewing 4 posts - 1 through 3 (of 3 total)

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