DB Maintenance Backup Job not deleting old backups

Question

DB Maintenance Backup Job not deleting old backups

zeo

SSC Eights!

Points: 914
More actions
July 11, 2006 at 8:18 am

#61927

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?

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

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

DeirdreBachman Mr or Mrs. 500 Points: 526 More actions · Answer 1

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

Private Gripweed Right there with Babe Points: 772 More actions · Answer 2

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.

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 3

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.

zeo SSC Eights! Points: 914 More actions · Answer 4

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.

Christian Benvenuto-248082 SSC Enthusiast Points: 163 More actions · Answer 5

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.

Maverick100877 SSCertifiable Points: 5240 More actions · Answer 6

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.

AESIT Valued Member Points: 55 More actions · Answer 7

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.

Mario Iglesias Valued Member Points: 53 More actions · Answer 8

Hello,

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

😀

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 9

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.

anjan.ashok SSCrazy Points: 2783 More actions · Answer 10

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

pls help

martincjones SSC Journeyman Points: 81 More actions · Answer 11

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.

Jerry Hung SSChampion Points: 12968 More actions · Answer 12

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

shanila_minnu Hall of Fame Points: 3465 More actions · Answer 13

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.

tosscrosby SSCoach Points: 18045 More actions · Answer 14

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.