THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

Pay Attention to Maintenance Cleanup Job Configuration (If you use Maintenance Plans)

I am currently in the process of implementing standardized backups and maintenance at my new job and as a part of this, I am eliminating the existing Maintenance Plans and Jobs.  One of the reasons for doing this is that Maintenance Plans require manual configuration, are less flexible than a good script, and are easily configured in a manner that violates best practices.  A commonly misconfigured task in Maintenance Plans is the Maintenance Cleanup Job Task.

A common Maintenance Plan usually has backups, reindexing, statistics updates, and cleanup configured for it.  For example, a plan has Full, Differential, and Log backup tasks configured in it with the Full Backup task occuring weekly, Differential task occuring daily and Log backups occuring every hour.  Sounds like a really good plan so far, it covers backup properly.  It also has reindexing weekly before the full backup, and nightly statistics updates.  Still good.  However, when it comes to the Maintenance Cleanup Task included in the plan I’ve seen a number of plans configured to delete bak Files older than 3 days old from the Backup folder.


Where does that leave you in the event of a disaster?  Trying to sail upstream without a paddle.  Its really an attention to detail kind of thing and even when not using a Maintenance Plan you can still make this mistake.  Wherever you configure your clean up, it has to take into account the frequency of each type of backup so that a complete set of backups exists including the Full, last Differential (or all the logs since the Full), and subsequent TLogs are retained.

Published Wednesday, February 10, 2010 9:55 PM by Jonathan Kehayias



Ben Nevarez said:


Most of the times this is not the final destination for these backup files and in case of a disaster you may not even have the three days of backups you are asking to keep: that disk may be also gone. Usually these backups are stored on some other permanent location, like a tape, and outside the data center. This value is just for the files that you can keep according to your available disk space.



February 10, 2010 10:25 PM

Jonathan Kehayias said:


Unfortunately I have to disagree with you about the final destination comment.  For an experienced DBA, the local disk isn't the final destination, but most times people using Maintenance Plans solely as their maintenance tool, the local disk backup only goes to the local disk.  I have seen it numerous times on the forums as well as in real life where the backup files land on local disks and as you say it in a disaster all is lost.  The backups should be stored in another location, as well as offsite, ABSOLUTELY, I completely agree there, but a lot of times that just isn't the case for most places where I've seen Maintenance Plans doing all of the maintenance.

February 10, 2010 10:44 PM

Uri Dimant said:


I would prefer explicity to specify cleanup configuration for database and log file backups, I mean ucheck first -level subfolders option. We have log file backups appended into the one file (WITH NOINIT)and hence there is no problem to delete them as well :-))

February 11, 2010 1:05 AM

AaronBertrand said:

I agree Jonathan, most of us know that backing up to local disk as final destination is about as useful as not backing up at all.  However, "disasters" are not always synonymous with lost disk.  Backing up to local disk *and* combining that with deleting backups more than 3 days old when you are on a weekly full schedule makes it impossible to recover even if the disaster was that a few days ago someone deleted the wrong row.  You can't restore to a point in time (even on some other server just to recover that one row) if you've deleted the full backup because it was more than 3 days old.  

And yes, I too have seen this exact type of configuration.  In one situation, not only was the practice used because they didn't have enough room on the local disk to store a full backup plus a week's worth of logs (the "DBA" thought that log backups were enough!), they also scoffed at my brain dead suggestion to move backups to a different machine.  They didn't have enough space to store the backups on any other server either, and even if they were able to find room, they were worried about "flooding" their network with a backup (or copy of a local backup).  It seems they utterly missed the point of backups.

February 11, 2010 1:07 AM

AaronBertrand said:

Uri, what browser or tool are you using to leave comments on this site?  The last few I've seen from you (on multiple individual blogs) have been posted three times.

February 11, 2010 1:08 AM

TiborKaraszi said:

I think it is also worth pointing out that the cleanup task is in no way automatically connected to a prior backup task (if such exists). Say you have a backup tsk to do backup for database "pubs" to a certain folder and you configurea cleanup task in same plan to remove files. The task will remove all bak files - not just for the pubs database.

Also, I typically have three cleanup tasks. One to remove bak files, another to remove trn file and yet another to remove maint plan report files.

February 11, 2010 5:56 AM

Matt said:

You absolutely need to move SQL backups to another server or SAN at that location in addition to taking the files off-site. This allows you to recover from a local server hardware/disk failure much more quickly. It elminates the double hop of getting from tape.

Regarding maintenance plans, someday the SQL Team will catch on and put some real power into the maintenance plans. In the meantime, I also use scripts. I actually call a dynamic vbs script that uses both date and archive bit to determine when to delete backup files. If the archive bit is still set then it will not delete the file.

I would rather fill up the backup disk then delete a backup that never made it off-site. But you better have a solid process to catch that problem. In my case, all types of alarms start going off after the backup disk fills up to a certain point and/or the backup jobs fail.

But, YMMV.

February 11, 2010 8:48 AM

Jonathan Kehayias said:


That is a great point about the disconnection between the cleanup tasks and actual backups that may be configured.  If you have every database going to a single folder and different schedules, you could really have a problem.

February 11, 2010 9:19 AM

merrillaldrich said:

Jonathan - I got so frustrated with this exact issue that I wrote my own CLR procedure to handle clean-up of backup files, including dependencies between them and whether they've been written out to backup media (DDR or tape). It's a bit of a sledgehammer, but it's workin' for me :-)

February 11, 2010 11:28 AM

mpeide said:

Merril - Thanks for sharing the CLR approach. I'll have to give it a spin.

February 12, 2010 7:42 AM

Eric Wisdahl said:


Respectfully, I'd have to disagree with the fact that scripts are better than maintenance plans.  You can write bad scripts just as easily as you can write bad maintenance plans.  In either case, you have to pay attention to the details.  I'm sure that you are used to checking for the proper syntax, properties and errors in scripts more readily than you are used to checking for those same properties and errors in the plans - most DBAs are... It doesn't mean that the plans can't be just as effective if set up and configured properly.

Now, am I saying that you should use the plans over the scripts? Of course not.  But there is nothing wrong with using the plans as long as you are diligent about their configuration.

February 12, 2010 9:39 AM

Sankar Reddy said:

I wrote up another reason why I don't like maintenance plans. Not all of the options available with T-SQL scripts are incorporated into the maintenance plans and one of them is the ability to use BACKUP CHECKSUM. It may NOT be the single factor that will sway your decision.

February 12, 2010 5:03 PM
Anonymous comments are disabled

This Blog


Privacy Statement