Why Are Maint Plans So Buggy

Question

Why Are Maint Plans So Buggy

JC-3113

SSCrazy Eights

Points: 8371
More actions
June 9, 2010 at 8:52 am

#233620

Hi Folks
I seem to be at odds with this Maint Plan Software
It never seems to work after changes are made
Yesterday, I deleted several databases from the server
They nom no longer exist
Now my maint plan blows up because it is still trying to log on to these databases that do not exist
They are not in the maint plan, so i do not know what is going on
Does anybody know what i need to do to fix this issue.
Sample error:
06/09/2010 07:43:01,,Error,[298] SQLServer Error: 4060<c/> Cannot open database "SS64_SharedServices1_DB" requested by the login. The login failed. [SQLSTATE 42000]
06/09/2010 07:43:01,,Error,[298] SQLServer Error: 18456<c/> Login failed for user 'LOSANGELES-2K\llspsqlservice'. [SQLSTATE 28000]
06/09/2010 07:43:01,,Error,[298] SQLServer Error: 4060<c/> Cannot open database "SS63_SharedServices1_DB" requested by the login. The login failed. [SQLSTATE 42000]
06/09/2010 07:43:01,,Error,[298] SQLServer Error: 18456<c/> Login failed for user 'LOSANGELES-2K\llspsqlservice'. [SQLSTATE 28000]
Thanks
Jim

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Remake the plan would be an obvious one. Assuming you are able to edit the plan, then maybe add a db then take it out again should regenerate the objects and you'd be good to go.

Also another path is to script it all out and have 100% control over the plan that way... also it allows you to deploy to any number of servers as needed.

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

Thanks SS Champion

that is a lot of work to redo

i have already spent a lot of hours making these plans

they work fine as long as you do not make a change

that is the problem

Thanks

Jim

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

Dis-regard those errors above

this is the maint plan error:

One or more selected databases no longer exist on server.

They are no longer in the plan, so why would it say this ?

Thanks Jim

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

JC-3113 (6/9/2010)
Dis-regard those errors above
this is the maint plan error:
One or more selected databases no longer exist on server.
They are no longer in the plan, so why would it say this ?
Thanks Jim

Because the backup database task was set to backup all user databases. When it was saved, it generated the list of databases on the server to be backed up. When you removed the databases, the maintenance plan was not updated.

To fix this particular issue, all you have to do is to edit the task and re-save it.

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 5

Hi Jeffrery

you think it would be that simple eh ?

when you delete the databases off the server and check the plan, you will find that they are removed automatically

I re-checked the entire plan to make sure they are gone and they are

whatever program runs these plans is too stupid to understand the deleted databases are no longer in the plan, so I am not sure what it is looking at, because the databases do not exist anywhere except where this stupid program is looking

Thanks

Jim

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

Hi Folks

found this bit of info that seems to solve the problem:

--fix maint plan(s) after deleting databases

issue: One or more selected databases no longer exist on server.

the db is deleted and there is no reference to the deleted db within the plan.

error: One or more selected databases no longer exist on server.

ref: http://forums.databasejournal.com/showthread.php?t=51776

Don't need rebuild plan, just go to backup step and choose dbs again.

I basically selected all databases for each task and saved the plan

Then i selected specific databases (which i had originally) that i wanted

did not see any errors this time

Jim

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

Yes, the list of databases is generated when the plan is saved.

You have to make a change to the backup task and re-select the list of databases to be backed up and save the plan. That will correct this problem.

If you are going to be adding/removing databases on a regular basis, then I would not recommend using the backup task. Instead, you can use an Execute SQL Task and generate the list of databases to be backed up yourself, as well as building the backup command and filename.

I use this technique for my Litespeed backups - and it works quite well.

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

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 8

I always create one package per task per database. So if anything were to change, I have to only delete the one package and the job that fires off that package, or simply disable that one job.

That means that I have one SSIS package that performs a full backup for one database, I have another SSIS package that performs the differential backups for that one database, I have another SSIS package that performs the index rebuilds for the one database. I an easily manage any of them without affecting anything else.

I would suggest that you not use the wizard to create the maintenance pacakges, create them by hand manually.

Andrew SQLDBA

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

AndrewSQLDBA (6/9/2010)
I always create one package per task per database. So if anything were to change, I have to only delete the one package and the job that fires off that package, or simply disable that one job.
That means that I have one SSIS package that performs a full backup for one database, I have another SSIS package that performs the differential backups for that one database, I have another SSIS package that performs the index rebuilds for the one database. I an easily manage any of them without affecting anything else.
I would suggest that you not use the wizard to create the maintenance pacakges, create them by hand manually.
Andrew SQLDBA

That could be a lot of plans to manage on a system with a lot of databases. My approach is to identify the types of databases on the system and build a plan (or subplan) for each one.

In one case, I have several databases that are lookup databases. They get updated once a week, so I back them up once a week in a single plan. On this same system, I have four transaction databases that are all related - they get backed up in the same plan, etc...

On another system, databases are created every week by the application. So, I created an Execute SQL Task that executes code to identify the databases to be backed up.

In other words, there is no generic one size fits all maintenance wizard plan that will work for every system. You have to identify the requirements for each system and build the plans to support that system.

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

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 10

I like Jeffrey's scheme. I do that as well, one maint plan for each "maint scheme". So one for system dbs (no t-log backup), one for user dbs that need full/log backups, maybe more if I have dbs that need diffs, or that don't do indexing or something else.

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

Hi Guys

thanks for all the valuable input.

Appreciate it 🙂

Jim