DB Maintenance Backup Job not deleting old backups

  • I have a DB Maintenance Backup Job not deleting old backups and it has filled up the drive. I am backing up 'ALL User Databases' not master, model and msdb. I am specifying that backups older than 1 day should be deleted and I am seeing backups about up to 4 days old there.

    Should I drop and recreate the maintenance plan?

  • Not sure if this is specifically your issue without more details - I've had a similar problem in which at very low disk space something like this happens:

    SQL200 runs the maintenance plan, it backs up the database(s). It seems that it tries to remove the old files only after it has performed the new backup (sensible) and if it lacks a certain amount of disk space to do this, this part will fail, allowing the disk to fill up. Unless you're getting a specific error (permissions, etc) that indicates your problem lies elsewhere, I would attempt to clear enough disk space for at least 1.5 * the total size of all backups being made (if this is possible), leaving several very old files that should unquestionably be deleted by the maintenance plan (for starters) and run it to test if it removes the old files.

    -- http://dbachman.blogspot.com

  • Do your log files indicate that there is any issue at all with the jobs? It is indeed true that if the job fails, the cleanup will not run.

     

  • The maint plan aren't great. If one part fails, like a backup can't complete, it doesn't get to the cleanup as mentioned abovce. I'd check the log history and be sure that it's actually completing the backups.

  • But as I indicated I had old backups from up to 4 days ago. I do not believe disk space was an issue in the last 4 days.

  • Change the deletes to 22 hours old. I've run into issues where it won't delete the backups because it run a second earlier then it did last night.

  • Are you taking the backup of all the databases through one maintenance plan only. If the answer is yes then there must a backup which would be failing and no further backups or deletion of old backup after that would be happenning.
     
    So you need to check the server logs to see if any backup is failing or not.
    It is advisable to create separate maintenance plans for different dbs.
  • Deedee (7/11/2006)


    Not sure if this is specifically your issue without more details - I've had a similar problem in which at very low disk space something like this happens:

    SQL200 runs the maintenance plan, it backs up the database(s). It seems that it tries to remove the old files only after it has performed the new backup (sensible) and if it lacks a certain amount of disk space to do this, this part will fail, allowing the disk to fill up. Unless you're getting a specific error (permissions, etc) that indicates your problem lies elsewhere, I would attempt to clear enough disk space for at least 1.5 * the total size of all backups being made (if this is possible), leaving several very old files that should unquestionably be deleted by the maintenance plan (for starters) and run it to test if it removes the old files.

    I am sure this is the issue with mine. I never thought it would not delete the files first even thought that makes more sense. I will change the backup to another drive and try it there.

  • Hello,

    Can anyone tell me where to set the job to delete the old backups first instead of at the end?

    😀

  • In my memory, SQL 2000 backup a database first and then delete old one if the backup is successful by default.

    You can remove the old backup files using xp_cmdshell command in SQL Server.

    When you said that the old databases could not be deleted automatically, take a look at whether or not there is any application running against your databases, such as whether or not there is any tape backup on these databases. If so, the databases are in use, SQL Server could not delete them.

    Hopefully, they are helpful.

  • can i user database maintenance plan for only deleting old backup files in sql server 2005

    pls help

  • Just like to say thanks to the posters on this thread and the advice of checking that all the jobs ran successfully. As a result I managed to solve a problem that should have been obvious!

    I recently added a new database which was set to Simple recovery model. I added this db to an existing Database Maintenance Plan that included a transaction log backup and had a retention period for tlog backup files.

    The tlog backup for this db obviously failed and subsequently the maintenance plan did not continue with deleting the tlog backups for all our db's as per retention period. This filled up our transaction log volume quite quickly and caused a minor outage.

    I have created a new db maintenance plan specifically for this db that excludes a tlog backup. The original maintenance plan is now happilly deleting the tlog backups as normal.

  • martincjones (11/27/2008)


    Just like to say thanks to the posters on this thread and the advice of checking that all the jobs ran successfully. As a result I managed to solve a problem that should have been obvious!

    I recently added a new database which was set to Simple recovery model. I added this db to an existing Database Maintenance Plan that included a transaction log backup and had a retention period for tlog backup files.

    The tlog backup for this db obviously failed and subsequently the maintenance plan did not continue with deleting the tlog backups for all our db's as per retention period. This filled up our transaction log volume quite quickly and caused a minor outage.

    I have created a new db maintenance plan specifically for this db that excludes a tlog backup. The original maintenance plan is now happilly deleting the tlog backups as normal.

    What I do is have separate subplans

    1 for FULL

    1 for DIFF

    1 for TLOG

    1 for cleanup

    This way cleanup ALWAYS runs independent of the results of previous 3 steps

    Also, in Maint. Plan interface (SSIS look), you can change the "green line" conditions to set it to ALWAYS run

    Defaults to condition "success" only, but I believe you can change it to "completion" so that a 2nd task is run always as well

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005
  • my sql server 2000 db maintanence plan is not deleting the old backups.because of this backup is failing due to space issue.i set to delete backups older than 23 hours.

  • How often are you backing up? I have maintenance plans that delete files older than 2 weeks and have never seen it fail to do it. How big are your databases? Maybe time to add more disk or copy them from the local drives to a network share where there is more space available. Be sure NOT to delete the file until a new backup file has been created. If the backup fails and you've deleted your last copy, it could spell trouble.

    Edit - You really should've opended a new thread as opposed to hijacking an existing one. Posters who've maybe already posted here or read it may not come back again. A new thread would probably get you more responses - IMO.

    -- You can't be late until you show up.

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

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