Sean Wallbridge
I’ve been seeing this happen quite a bit…
WHAT?
SQL Server disks are filling up with backups from Database Maintenance Plans despite having settings that they should be removing transaction log or database backups after X days.
SO WHAT?
The problem is that a Database Maintenance Plan cannot properly purge these backups if the job contains any databases that have mismatched backup job ‘recovery modes’. For example, most databases by default start off with the ‘Full’ Recovery Mode, yet some system databases only allow the ‘Simple’ Recovery Mode.
As defined in http://support.microsoft.com/kb/303292/en-us , this just won’t work (i.e. your backup files will grow and grow and grow and never get lopped off ‘after Y days’ as you might have defined in your maintenance plan.
NOW WHAT?
Well, you can approach this several ways but possibly the best way is to follow these simple rules:
– Create a separate database backup plan for your User Databases (production) and a separate database plan for your System Databases
– Should you decide to change the recovery mode for a Production Database, be sure to have a separate Database Maintenance Plan (or perhaps have a ‘Full Database Recovery Maintenance Plan’ and a ‘Simple Database Recovery Maintenance Plan’
– Consider having a separate plan altogether for the ‘other’ actions you might want, such as optimizations and integrity checks so that any failure of these do not disrupt the success of your backups