ProjectSMM.com
Gonzo TechNet
How to delete a rogue maintenance plan from SQL Server 2005
Issue: Can't modify or delete a SQL 2005 maintenance due to error:
"Exception has been thrown by the target of an invocation (mscorlib).
An error has occurred while establishing a connection to the server.
When connecting to sql server 2005, this failure may be cause by the fact that under the default settings, sql server does not allow remote connecitons. (provider: tcp provider, error: 0 no such host is know) error 11001"
Description:
I was on a contract that had 160+ SQL Servers with over 1,2000 databases targeted to be upgraded to SQL 2005.
Following some upgrades, we discovered that occasionally a previously existing (SQL 2000) maintenance plan had become corrupted to the point where it couldn't be modified or deleted.
When we tried to delete it using SSMS (SQL Server Management Studio) by doing a right-click -> delete, the delete operation would fail with the above error.
The exact cause of the corruption is unknown, but, the cure requires you to go into the MSDB tables and manually delete the plan.
This may sound simple but, a "maintenance plan" is actually a rather intricate set of related records that is even further complicated during an upgrade due to changes in the way it is stored in SQL 2005.
So, after some investigation, I wrote the following SQL code to perform the clean up.
Note:
It is CRITICAL that you set the variable @mpname to the NAME of the rogue maintenance plan!
USE msdb
go
BEGIN TRY
DECLARE @errno INT, @errmsg varchar(100)
DECLARE @mpname varchar(200)
--Set the NAME of the Rogue Maintenance plan here!
SET @mpname = 'RoguePlanName'
BEGIN TRAN
-- Delete the JobStep Logs for this plan's jobs
-- SELECT jl.*
DELETE dbo.sysjobstepslogs
FROM
dbo.sysdtspackages90 as pk
JOIN dbo.sysmaintplan_subplans as sb on (pk.id = sb.plan_id)
JOIN dbo.sysjobsteps as js ON (sb.job_id = js.job_id)
JOIN dbo.sysjobstepslogs as jl on (js.job_id = jl.step_uid)
WHERE pk.name = @mpname
-- Delete the JobHistory for this plan's jobs
--SELECT jh.*
DELETE dbo.sysjobhistory
FROM
dbo.sysdtspackages90 as pk
JOIN dbo.sysmaintplan_subplans as sb on (pk.id = sb.plan_id)
JOIN dbo.sysjobhistory as jh ON (sb.job_id = jh.job_id)
WHERE pk.name = @mpname
-- Delete the Job Schedules for this plan
--SELECT js.*
DELETE dbo.sysjobschedules
FROM
dbo.sysdtspackages90 as pk
JOIN dbo.sysmaintplan_subplans as sb on (pk.id = sb.plan_id)
JOIN dbo.sysjobschedules AS js on(sb.schedule_id = js.schedule_id)
WHERE pk.name = @mpname
-- Delete the JobSteps for this plan's jobs
--SELECT js.*
DELETE dbo.sysjobsteps
FROM
dbo.sysdtspackages90 as pk
JOIN dbo.sysmaintplan_subplans as sb on (pk.id = sb.plan_id)
JOIN dbo.sysjobsteps as js ON (sb.job_id = js.job_id)
WHERE pk.name = @mpname
-- Delete the Jobs for this plan
--SELECT sj.*
DELETE dbo.sysjobsteps
FROM
dbo.sysdtspackages90 as pk
JOIN dbo.sysmaintplan_subplans as sb on (pk.id = sb.plan_id)
JOIN dbo.sysjobs as sj ON (sb.job_id = sj.job_id)
WHERE pk.name = @mpname
-- Delete the Plan LOGS for this plan
--SELECT pl.*
DELETE dbo.sysmaintplan_log
FROM
dbo.sysmaintplan_log as pl
join dbo.sysdtspackages90 as pk ON (pk.id = pl.plan_id)
WHERE pk.name = @mpname
-- Delete the SUB-Plans for this Plan
--SELECT sp.*
DELETE dbo.sysmaintplan_subplans
FROM
dbo.sysmaintplan_subplans as sp
join dbo.sysdtspackages90 as pk ON (pk.id = sp.plan_id)
WHERE pk.name = @mpname
-- Delete the PLAN
-- SELECT *
DELETE dbo.sysdtspackages90
FROM dbo.sysdtspackages90
WHERE name = @mpname
-- All done!
COMMIT WORK
END TRY
BEGIN CATCH
SELECT @errno = ERROR_NUMBER() ,@errmsg = ERROR_MESSAGE()
IF (@@TRANCOUNT > 0)
BEGIN
ROLLBACK TRAN
END
RAISERROR(@errmsg,16,127)
END CATCH
GO