3

Should SQL 2000 and 2005 maintenance plans be resilient to a database being detached or offlined?

I detached / offlined a few databases, and now the maintenance plans are failing in their entirity because SQL can't find/connect to the database. It seems to me a simple matter for SQL to either re-calculate the script upon such a change OR to have a wizard-generated maintenance plan that will (at worst) just emit a warning about the unavailable databases and continue with the independent parts of the script, such as the other databases.

As it is, it seems to me to be required that I go into each step-block of the "User DBs" maintenance plan, switch the setting to "All databases", ok out of the wizard, save, go back in and set it back to "All databases except the system databases", ok out of the wizard, and save. That's a lot of clicks! (That's 2005, with a similarly awkward list for 2000)

Is it normal? or is something else wrong?

Thanks!

Improve this question
1
  • Yup. Maintenance plans = nice for general usage, evil for big things.
    – squillman
    Aug 31, 2009 at 23:38

1 Answer 1

Reset to default
5

Jason,

Yes, that is normal, and that is why many people (myself included) have decided to "roll-our-own" maintenance plans.

Another quirk of MP's is that they will attempt to do a trx log dump for a database in SIMPLE recovery mode. You would think it is a simple check to make before attempting the database backup, right? Well, it is, but not inside of the MP, your job just fails.

There are lots of little quirks like that. If you rely on MP's to handle your database backups then you must be aware that any change made to the database can have an adverse affect on your MP, and you will need to review and act accordingly. Otherwise you will most likely be reminded when the job fails next time around.

Improve this answer
3
  • Yup! I ran in to that SIMPLE issue a few times lately. Has it been fixed since (2008)?
    – Jason Kleban
    Aug 31, 2009 at 22:59
  • not that i am aware of. i do, however, believe that the new MP's offer you some flexibility. They are supposed to be SSIS packages that you can edit. so, in theory, you could build your own logic into the plans. I continue to roll my own, however.
    – SQLRockstar
    Sep 1, 2009 at 0:11
  • ...and if you're too lazy to roll your own, take a look at Ola Halegren's scripts which don't suffer from the issues your describing. ola.hallengren.com
    – Nick Kavadias
    Sep 1, 2009 at 2:05

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .