SQL Server - Cannot delete maintenance plan or scheduled job

Asked By Scot

07-Jan-09 11:34 AM

Hi Folks,

SQL Server 2005 SP2 32bit
Windows 2003

I created a MP (backup, integrity) and later renamed the server. The MP jobs
started failing as a result, so I created another MP to replace the failing
one. The new MP works fine. The problem now is I cannot delete the older
failing MP or individual jobs. Even though I modified the connection
properties within the failing MP, it still seems to be trying to connect to
the renamed connection.

sysservers does not contain the previous server name - I changed it to
reflect the new server name after renaming the server.

Thanks in advance.

07-Jan-09 01:48 PM

When you right-click the job in SQL Server Agent / Jobs in Object Explorer,
and click "Delete", what happens?





On 1/7/09 11:34 AM, in article
D8EBCBE6-71F7-4589-BAD9-DC5DB86F9C5B@microsoft.com, "Scott"

07-Jan-09 03:00 PM

Aaron,

An error dialogue box pops up with the following message

“Exception has been thrown by the target of the invocation (mscorlib) An
error has occurred while establishing a connection to the server …..”

When I review Managed Connections from within the SSIS package (MP) , the
local connection is grayed out, but I can see the old server name is still
being referenced . I think that this is my issue – it is still trying to
connect to the previous server name. I tried adding another connection and
then deleting it, but to no avail.

I guess the real question is, how do you change the properties of the local
connection within the MP?

07-Jan-09 03:14 PM

When right-clicking the job and selecting DELETE?  Have you established a
NEW connection to the server in Management Studio since renaming it?  The
job shouldn't care about your maintenance plan or the connection(s) within
it... it is merely an extension.  Kind of like changing the engine on your
Mustang and then expecting the tires to complain when you try to remove
them.


Don't know.  Not a big fan of maintenance plans (in fact recently eliminated
all of the maintenance plans at a client site), and further still, I am not
a big fan of renaming servers, too many things (like this) can go wrong and
are hard to test in advance.

A

08-Jan-09 08:20 AM

Interesting. Care to elaborate Aaron as to why you are not a fan of MP's, and
why you would remove them from your clients site? For backups and integrity
checks MP's take care of things quite simply and without fail - at least this
has been my experience over the past 10 years or so.

Scott

08-Jan-09 10:20 AM

Lack of control, buggy... I still see cases where the MPs don't delete files
older than what you specify.  They also just flat-out fail if you check
you want to back up different databases on different schedules you need to
manage multiple plans.  BACKUP DATABASE is not all that hard to script
yourself.  Also if you need to manage your system from multiple machines,
you need to make sure all systems are patched with the latest SSMS updates.
Were you around for all the debacles around SQL Server 2005 SP2, and what a
mess they made with maintenance plans during several post-SP2 patches as
well?

08-Jan-09 10:32 AM

(And when they fail you sometimes have to dig pretty hard to find the true
problem.  The error messages leave a lot to be desired.)


On 1/8/09 10:20 AM, in article C58B8403.B2E1%ten.xoc@dnartreb.noraa, "Aaron

09-Jan-09 09:07 PM

Hi Scott!

I had the same problem, and succesfully resolved with this:
http://www.dbforums.com/microsoft-sql-server/1627954-renamed-sql-2005-serve=
r.html#post6325252

To get the MP IDs:
use msdb
go
select * from sysmaintplan_plans


Then:
---
use msdb
go
declare @abcd as varchar(100)
SET @abcd =3D 'FC752629-AEFA-43B8-A1A5-F14C6E788F12'    -- Paste here
the next ID and then the next...
delete from sysmaintplan_log where plan_id =3D @abcd
delete from sysmaintplan_subplans where plan_id =3D @abcd
delete from sysmaintplan_plans where id =3D @abcd
---

bye,
B=E1lint