David Davis

I have 3 maintenance plans inplace

One to backup our 3 production databases, master, model, msdb, one to backup the log files of our production databases and one to optimize our production databases. The backup plans are set to delete old backup 3 or more days old

When the plans were first put into place they worked correctly. Lately (month or 2), the backup plans have not been working correctly.

When I first noticed the problem, 1. I saw the the drive I'm putting the backups on was almost out of space and 2 the delete part of the backup was not deleting backups more than 3 days old. When I deleted the old backups, the plans worked as normal until the hd became full again.

I have 2 questions.

1. When does the "Delete" part of the plan work before or after the "Backup" part.

2. If the "Delete" is after the "Backup" operation, then if the hd is almost filled how will the plan react.

Re: SQL 2000 Maintenance Plan failure

Buck Woody

David -

1. You can set up the plan steps in any order, but by default the delete comes at the end. I would suggest that you leave the remove steps after the backups, for safety.

2. If the delete step is in-line after the backup, then it will not fire if the backup fails due to lack of space. That may be what you're facing here. You could set the delete to happen regardless, but I don't recommend it, because you might get into a situation where you have no backup files at all on the drive.

What I do in a tight drive-space situation is to add a step to the maintenance plan that runs an operating system batch job to zip (compress) the backup files after the last step. That saves me some drive space, since backups are full of a lot of compressable bits.

Buck Woody

Re: SQL 2000 Maintenance Plan failure

April B


I am experiencing a similar problem. I like your resolution but how do you create a O/S batch job to zip (compress) the backup files



Re: SQL 2000 Maintenance Plan failure

Buck Woody

I create a batch file in the os that looks something like this:

@echo off


REM This part does the SQL backup - put your specifics in here, such as calling a maintenance plan or whatever

osql -E -q "BACKUP DATABASE foo TO DISK = 'c:\backupdir\database.bak'"

REM This part does the compression

pkzip -a c:\backupdir\database.zip c:\backupdir\backup.bak

REM this part does the copy

copy c:\backupdir\*.zip \\servername\sharename

REM also a good idea to put some logic in here to make sure all this worked, or don't run this line

del c:\backupdir\*.*

Another method might be to just add a step in the maintenance plan once you've created it that does the compress/copy part. Either way, you want to clean up the dir so that you have room the next time the backup rolls around.

Re: SQL 2000 Maintenance Plan failure

April B

Thank you, I will give it a try.

Re: SQL 2000 Maintenance Plan failure


I am noticing the delete step randomly skipping backups older then the maintenance setting. These backups were named by the maint plan and were not changed. For example, of the 5 databases that I created a full backup for, 1 failed. The maint plan log stated that there 0 files to delete even though there was definitely one to delete.

Space was not an issue. The db backup that was skipped is 1.5mb and I have over 40 GB free on the drive.

Any suggestions


Re: SQL 2000 Maintenance Plan failure

Deepak Rangarajan

Can you try creating a log file in the jobs step of the maintenance plan job so that the operations will be logged the next time the job runs and post that results
- Deepak

Re: SQL 2000 Maintenance Plan failure

Joe Webb

You can also check into some third party backup utilities that compress and/or encrypt data as it's being written to disk. Here are a few; I'm sure there are more out there.

Red-Gate SQL Backup

Idera SQLSafe

Quest Litespeed for SQL Server

Additionally, SQL Server 2008 will have backup compression built in.



Re: SQL 2000 Maintenance Plan failure

gabriel bozzolasco

How can I do that Could you explain me how to do it