2

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?

Improve this question

5 Answers 5

Reset to default
3

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.

Improve this answer
1

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

This problem is driving me nuts.

Improve this answer
1
  • This really helped. You have to go through each step in the plan, click on the "Database(s)" drop-down and then check the checkbox at the bottom "ignore databases whose state is not online."
    – mdma
    Nov 29, 2012 at 19:02
0

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.

Improve this answer
0

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.

Improve this answer
0

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

Improve this answer

You must log in to answer this question.

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