Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL 2005 Maintenance Plan Cleanup


SQL 2005 Maintenance Plan Cleanup

Author
Message
george sibbald
george sibbald
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7012 Visits: 13687
SQl 2005 standard edition SP2 GDR2 (9.00.3054)

This is a consolidated server. I backup to a set directory with a sub-directory for each database. For simplicity(!) maintenance plans are used to backup databases. A maintenence plan may backup one database or multiple databases (eg the system dbs are in one plan).

I want to cycle out old backups, so I add a maintenance cleanup task to delete older backups, I want to keep some backups longer than others. For this I specify option 'search folder and delete files based on an extension' and where the plan deals with more than one database specify the set database backup directory plus the 'include first level subfolders' button.

AAaaaargh! old backups are deleted from ALL the sub-directories not just the ones created as part of this particular maintenance plan!

Surely this cannot be correct. Is this really what microsoft intended or Have I missed something or is this another maintenance plan bug I thought were all fixed by GDR2?

---------------------------------------------------------------------
Andy sql
Andy sql
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1221 Visits: 1311
I think the cleanup is working as planned. Whether you (we) agree with the design is another matter! Having tripped over this before, I now think of the cleanup task as a pretty dumb shell script, that searches for all files with the specified file extension, up to one subfolder deep, and deletes all old files.

The simplest workaround I know is to separate out your backups into folders (at the same level), and then create a cleanup task for each folder. Unfortunately this involves creating multiple cleanup tasks. Still, at least you can see what is happening.

Andy
george sibbald
george sibbald
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7012 Visits: 13687
Andy,

thanks. yes it is pretty dumb, especially as SQL2000 could handle any requirements fine. I will have to layout my backups and maintenance plans to work around this foible, though hopefully MS will fix this in the future.

I put it down to using SSIS for maintenance plans, which I have never liked.

---------------------------------------------------------------------
Andy sql
Andy sql
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1221 Visits: 1311
The addition of the include first level sub-folders option after SP1 (I think that's when it appeared) clearly shows that this function of the Maintenance Plan was a big let down for admins. Still, I don't remember it working perfectly in 2000 either....

Andy
Deadwood
Deadwood
Old Hand
Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)

Group: General Forum Members
Points: 335 Visits: 160
Hi...

To prevent backups from deleting, you could always change the backupextension for each maintenaince plan and deletion plan. Make one plan ba1, another ba2, etc... and set the delete task to the same..

However, there is not much we can do about it, so we just gotta work with what we got.

Cheers

Thanks
george sibbald
george sibbald
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7012 Visits: 13687
ok, thanks andy and 'deadwood'.

As deadwood says, work with what we got (or around it).

---------------------------------------------------------------------
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search