SMS Maint Plan is Not Smart Enough To Know When A Database Is Deleted When It Runs

Question

SMS Maint Plan is Not Smart Enough To Know When A Database Is Deleted When It Runs

JC-3113

SSCrazy Eights

Points: 8371
More actions
June 30, 2011 at 8:45 am

#243565

Hi Folks
I am trying to figure out why SMS is smart enough to remove a database from a Maintenance Plan when a database is deleted, but dumb when it comes to running the plan. By That I mean, the database was in the MP and it ran fine, then the database was deleted and SMS removed it from the MP, BUT, when the MP runs, it stlil thinks the database is still part of the MP.
So I don't get it.
Does something have to be re-cycled ?
Anyone with more experience than myself care to chime in ?
Thanks
Jim

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

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

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 1

How many subplans are in our maintenance plan? Do you have explicit databases selected in each of the subplans? How about the setting that says basically ignore it if its not available? Why don't you go into some detail about your plans.

CEWII

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

H ielliot

apparently my reply did not go throuigh last week

How many subplans are in our maintenance plan?

1

it does dbcc, rebuild index, full backup

Do you have explicit databases selected in each of the subplans?

Yes

I am selectign Specific databases for this plan

How about the setting that says basically ignore it if its not available?

I do not see that in 2005

Why don't you go into some detail about your plans.

My maint plans are set up for full and differential of specific databases (non-system and non adventureworks and northwind) those have their own plans

its funny that my differential plan works fine with the same databases selected

Thanks

Jim

Meet George Jetson SSChampion Points: 11539 More actions · Answer 3

Some information got orphaned, take a look at the following:

--RUN AGAINST msdb database

select * from sysjobs --this will give you the job id

select subplan_id from dbo.sysmaintplan_subplans where job_id = 'job_id' --this will supply the subplan_id

delete from dbo.sysmaintplan_log where subplan_id = 'subplan_id'

delete from dbo.sysmaintplan_subplans where job_id = 'job_id'

delete from dbo.sysjobs where job_id = 'job_id'

Chris Powell

George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.

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

Hi Chris

thanks, but i do not want to delete the maint plan as I am using it everyday

the plan runs but generates a message complaining that a database does not exist

but is included in the set of specific databases i have seleted when in fact it does not

Message:

One or more selected databases no longer exist on server.

Database 'SS66_WSS_Search1' is not valid to be included in the maintenance plan.

SQL Server is smart enough to know the database does not exist but not smart enough to know that it is NOT in the maint plan. It still thinks it is for some reason

Thanks

Jim

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 5

The maintenance plans are fairly basic and resolve the database names when the plan is built. It's one reason why so many people avoid them as they advance in their knowledge

Lowell SSC Guru Points: 323504 More actions · Answer 6

you'll need to edit or rebuild your plan, i think.

I just created a new main plan, and when i look a tthe details, depending on how you set it up at the beginning, selecting all databases, or selecting all the options for each database affected the built plan, obviously.

I think specific databases were selected, so you can see it's actually just writing the commands to do the maintenance as of the day you create the plan;

the "all databases" selection seems to make a cursor to look through items in the metadata.

Eliiot mentioned the 2008? option to ignore if it is not available, which might be helpfula s well.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

Hi Steve

i agree, but until I can get there myself, I need to rely on these Maint Plans

It seems to me that something so basic should work correctly

This is just one of the many issues I have with MS and their software regarding maint plans

Thanks

Jim

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

Thanks Elliot

I did not want to do that as I have added a lot of Notifications and T-SQL to the plans

What really bugs me is that the Differential plan uses the same database selections of databases as the Full and it has no issues.

I wil figuire out something

Thanks again

Jim

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 9

You might check out Ola's scripts. Lots of people use them instead of maintenance plans

http://www.sqlservercentral.com/scripts/Backup+%2f+Restore/62380/

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

Hi Steve

thanks

I had been looking at them when I was emailing Brent Ozar a while back

He also recommened her scripts

Jim