Home / server / Questions / 30769
Accepted
Sean Earp
Asked: 2009-06-24 20:48:36 +0800 CST

SQL Server Maintenence Plan error on an offline Database

  • 772

Today is SQL day for me :)

I have a maintenance plan that is failing to run with the following error:

Failed:(-1073548784) Executing the query "USE [SharedServices1_DB]" failed with the following error: "Database 'SharedServices1_DB' cannot be opened because it is offline.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

where SharedServices1_DB is a database that is set to offline. I would like to exclude this database from the maintenance plan, but when the database is offline, it does not show up at all as a "specific database" in the maintenance plan task, and if I bring it online, it is already unchecked in the maintenance plan task.

How can I exclude an offline database from a maintenance plan?

sql-server maintenance

5 Answers

  1. Yes we can exclude offline DB from specific maintenance plan. Please find below steps.

    1. Right click on maintenance plan -> select 'modify' -> you can see plan and subplans here ->right click on plan select 'properties' -> properties window will open in your right hand side > expand and search for the option 'IgnoreDatabaseInNotOnlinestate' -> check value set for this if its false then change it to 'True' with the help dropdown.

    It works well for me without recreating maintenance plans on server.

    • 3

  2. Sql server 2008 is the only solution I've found. It's got ignore offline option.

    This problem is driving me nuts.

    • 1

  3. We had a similar problem recently. We ended up going from an "include all" method to a 'pick all' method. It means that new databases are not automatically added to the maintenance plan, which means the DBAs need to let us know they're doing it. But the maintenance jobs no longer fail. A sub optimal solution, and perhaps there is a better way.

    • 0
  4. Best Answer

    Recreating the maintenance plan while the offline database remained offline ended up solving the problem. I will leave this question open in case somebody has the answer for resolving the problem without recreating the maintenance plan.

    • 0

  5. If it's SQL server 2000 or 2008, there won't be 'execlude offline databases' option. So below is what I did in SQL server 2000 as my Check DB maintenance plan was failing with 'couldn't execute because 'xo_xo' DB is offline' even if the offline DBs are not included in the maintenance plan.

    -> Changed the Databases drop down option to 'All databases' and executed. Job failed again.

    ->Now, changed it back to 'Specific databases' and selected all the dbs, executed the job. Job has succeeded :)

    I don't know whether this is the solution for it or not but it worked for me. Hope it helps :D

    • 0