Home / server / Questions / 60956
Accepted
Jason Kleban
Asked: 2009-09-01 14:17:21 +0800 CST

Smarter SQL 2000 and 2005 maintenance plans?

  • 772

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!

sql-server sql-server-2005

1 Answers

  1. Best Answer

    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.

    • 5