I tried creating a Maintenance Plan on my SQL Server 2008 database and was impressed by some of the things that are available out of the box. However, after it was created, it seemed difficult to customize. This left me wondering if I should have just created SQL jobs with the activities I wanted done. Are there any benefits to using the maintenance plan over manually creating SQL jobs?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
I can't think of any benefits, but I am also biased towards using my own jobs for maintenance.
I think that MPs are very good for new or accidental DBAs, as they allow for some maintenance activities to be done (which is better than having none at all). But as most DBAs progress in their experience they tend to start developing their own custom scripts instead.
I don't think the maintenance plans are hard to change.. There's just a simple designer for all action items.
But I'm also more in favor of SQL Jobs then maintenance plans. Actually I'm using plans only on servers I don't really touch and just want to use a wizard to do simple tasks (backups, validation, cleaning of old files). On all other servers I prefer jobs.
I think I'm using the plans only for the cleaning of old files. Don't remember anything else, the SQL Agent jobs are just too customizable to be ignored :-).
If you are looking at alternatives to using Maintenance Plans or are looking for something that is easy to customize, you should really look into the Ola Hallengren maintenance solution.