Maint Plan Issues With Disabling Databases

Question

Maint Plan Issues With Disabling Databases

JC-3113

SSCrazy Eights

Points: 8371
More actions
September 13, 2010 at 10:21 am

#226812

Hi Folks
can someone please tell me why the heck SQL Server tries to execute a Maint Plan on Database(s) that have been Disabled and removed from the Maint Plan. I cannot figure out why SQL Server is not smart enough to realize that the database(s) are disabled and that they have indeed been removed from the Maint Plan.
I just don't get it.
Any advise would really be appreciated
Thanks
Jim

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Derrick Smith SSChampion Points: 10833 More actions · Answer 1

In the maintenance plan where you select the databases, just check the box that says do not attempt on databases where status is not online.

I agree that it's silly that it tries to begin with...and by default to top it off. I guess they left it in as a way to give you a heads up if a database is offline.

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 2

Hi Derrick

sorry i do not see that anywhere in the maint plan

can you be more specific. i am using sql server 2005

now i have another issue with this stupid min-max date value error

so now i have to fix this again

I am really getting to hate Maint Plans

Value of '9/14/2010 12:00:00 AM' is not valid for 'Value'. 'Value' should be between 'MinDate' and 'MaxDate'.

Parameter name: Value (System.Windows.Forms)

Thanks

Jim

Derrick Smith SSChampion Points: 10833 More actions · Answer 3

Open up the maintenance plan, open up the task you're running that's failing, and then click the Databases dropdown, and you can select which databases the task runs against. At the bottom, there's a checkbox for "Ignore Databases where state is not online"

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 4

Hi derrick

well I must be having a really bad day as i cannot see it

i canot open up the task that does the backup as that gete ther min-max error

i am enclosing a pic of select databaes for integrity check task and you can see that it is not on the window

Jim

Derrick Smith SSChampion Points: 10833 More actions · Answer 5

Ah..you're completely right. I looked it up and it looks like it's a 2008 feature. I wasn't aware of that. Sorry about sending you on a wild goose chase.

I guess your only other option would be to set up your own script that runs through the database list and manually skips anything where state_desc <> 'ONLINE' (sys.databases table).

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 6

Thanks Derrick

thought i was getting goofey for a minute there

no problem

appreciate you taking the time

Jim

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 7

JC-3113 (9/14/2010)
Hi derrick
well I must be having a really bad day as i cannot see it
i canot open up the task that does the backup as that gete ther min-max error
i am enclosing a pic of select databaes for integrity check task and you can see that it is not on the window
Jim

This usually happens when your client tools have not been upgraded to at least the same version level as the server you are accessing. Or, you are accessing a pre-SP2 system with client tools that have been upgraded to post-SP2.

Verify the version level on the server you are connecting to - and your client tools. Both should be at least 9.0.3054 or greater. If not, you need to upgrade to at least that version - preferably SP3 if you can (at least the client tools, if you can't upgrade the server).

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 8

H iJeffrey

yes you would think that escpecially after i have researched the issue

I am on SQL Server 2005 Developer 64-bit 9.00.4053.00 SP3

I was able to find the solution (work-around) to the issue

Thanks

jim

Derrick Smith SSChampion Points: 10833 More actions · Answer 9

JC-3113 (9/14/2010)
H iJeffrey
yes you would think that escpecially after i have researched the issue
I am on SQL Server 2005 Developer 64-bit 9.00.4053.00 SP3
I was able to find the solution (work-around) to the issue
Thanks
jim

Can you please post what it was in case anyone else looks at this thread for something similar?

Thanks 🙂

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 10

Sorry Guys

I should have done that

Here is what I found, where I found it and what I did:

Problem :

Error happens when expirydate is met in Maint Plan

You will not be able to edit the task

TITLE: Microsoft SQL Server Management Studio

------------------------------

Cannot show the editor for this task.

------------------------------

ADDITIONAL INFORMATION:

Value of '9/14/2010 12:00:00 AM' is not valid for 'Value'. 'Value' should be between 'MinDate' and 'MaxDate'.

Parameter name: Value (System.Windows.Forms)

Solution:

http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/fc5741a2-c3bd-4143-a4a6-76abd53fe95f

You can select the maint plan in the designer and press F4 to open the properties

of the maint plan in the designer. In the properties you can change the expiry date,

this allows you to open the plan again for edit.

I changed the expirydate to Dec 1 of the following year

I am now be able to open the task

Jim