Unanswered: Clean-Up task without using a maintenance plan
As maintenance plans seem to have a habit of failing or corrupting when you least want them to I've reverting back to using SQL Agent jobs with my own TSQL so that I can control preceisely what's going on and, more importantly, have more information to troubleshoot if there is a problem.
I've managed to integrate all the checks, backups, etc ... that a maintainance plan does via the wizard (with my own additional checks), but I've hit a brick wall with the clean-up operation. Whilst this is the least of my worries if it goes wrong, I'd like to move away from any maintenance plans and therefore would like to elimate the need for this to be done via the maintenance plan wizard i.e. Maintenance Clean-Up.
Is anyone aware of a clever way of doing this? Can I simply invoke some VBScript that will do the same job? I know that it's probably irrational to declare all out war on maintenance plans, but that's where I am and any ideas would be arreciated!
Nope, I'm with you - I don't like maintenance plans. So much so, I've forgotten what clean up is - is it the one that deletes old backup history?
If so, this is just sp_delete_backuphistory.
Tara rolled her own here; Delete Backup History stored procedure
(I've never used BTW)
She's also posted loads of her admin scripts - might be worth a gander to see if you get any further inspiration for your own.
Thanks for the replies guys. Both are very useful.
My primary need is to emulate the functionality offered by the cleanup task regarding files age i.e. I want to delete all .bak files from a certain folder / set of sub folders that are older than 3 days old.
If anyone knows of a specific way of doing this I'd be very grateful. Faling that, I'll take a look at the product suggested and I'll also review deleting backup history to ensure that everything is cleaning up after itself!
Yes, I like it, It's great for controlling the workflow, but most of the tasks I use are embedded .cmd scripts (like the flexibility). I find some of the packaged functions a bit lacking such as for running DTS, Agent jobs and SQL commands, as to why I use .cmd scripts, which give much better output. The use of Plan/Job variables are great, that can be passed to job tasks very simply. If you are interested, keep in mind you would not have to purchase a separate execution agent license for each sql server you reference, it can be done from one execution agent. The only reason you would need additional execution agents is to run remote processes such as a .bat or .exe file on a remote server.