February 20, 2008 at 8:29 pm
Hello,
a maintenance plan was created prior to renaming the server on which SQL2k5 was installed. When I attempt to delete the maintenance plan now I get an Microsoft SQL server error 53. I guess it has to do with the fact that the server name has been changed after sql2k5 was installed and the maintenance plan created.
It is possible to remove entries from the system tables and if so how?
Rob
February 21, 2008 at 8:12 am
Robert,
Your msdb database holds the jobs.
You will not be able to right-click and open table on the system table you will need to do the delete from query window
Do a select on the following tables
msdb.dbo.sysdbmaintplan_databases
msdb.dbo.sysdbmaintplan_history
msdb.dbo.sysdbmaintplan_jobs
msdb.dbo.sysmaintplans
Locate the job ID and delete from these tables...
Also review for the job ID on those and remove
dbo.sysjobhistory
dbo.sysjobs
dbo.sysjobschedules
dbo.sysjobsteps
dbo.sysjobstepslogs
February 21, 2008 at 8:37 am
Forgive me for speaking about SQL 2000 in this forum but that's what were running here. When we would replace servers, there is a table in msdb called sysjobs. By updating the originating_server field on this table to the NEW server name, we were good to go. Anything comparable in SQL 2005?
-- You can't be late until you show up.
February 22, 2008 at 10:52 am
In SQL 2005 in msdb.dbo.sysjobs that field is no longer there.
It is replaced by the originating_server_id which is an INT datatype.
Haven't need to delve into this much but my values are 0 which probably indicates 'LOCAL'
February 24, 2008 at 6:47 pm
Hello jsheldon,
thanks for your helpful hints regarding removing the defunct system maintenance plan. Questions lead to discovery! There are zero rows in the following tables
dbo.sysdbmaintplan_databases
dbo.sysdbmaintplan_history
dbo.sysdbmaintplan_jobs
dbo.sysdbmaintplans has 1 row. This row has a field called plan_id.
I can remove the job_id in the following tables
dbo.sysjobhistory
dbo.sysjobschedules
dbo.sysjobstepslogs
except there is a referential integrity constraint issue with the dbo.sysjobs table when I try to remove the row with job_id for the old system maintenance plan.
"The delete statement conflicted with the REFERENCE constraint "FK_subplan_job_id". the conflict occurred in database "msdb", table, "dbo.sysmaintplan_subplans", column, 'job_id'
So job_id in sysjobs has a foreign key in sysmainplan_subplans. I can't delete the row in sysjobs without creating orphan rows elsewhere.
I'll sniff around and see if I can work back deleting records in the right order. Anyway if you have anymore time to play around with this good - otherwise thanks for your helpful reply.
Regards
Rob
February 27, 2008 at 12:44 pm
Rob,
Try this script to find all columns of job_id within the msdb database. If this script doesn't work I have two more search scripts:
/* This command using the undocumented sp_MSforeachdb stored procedure searches all database objects
for the given name provided */
USE master
GO
EXECUTE sp_MSforeachdb @command1 =
'SELECT ''?'' AS db,
o.name AS object,
c.name AS col,
o.xtype
FROM ?..sysobjects o, ?..syscolumns c
WHERE c.name LIKE ''vendor_id%''
and o.id = c.id'
GO
August 27, 2009 at 2:25 pm
To solve your problem, to delete Maintenance Plans on SQL2005, you will have to connect to Integration Service and delete them in Integration Service. That's why you won't find them on the tables that you were listed. 😀
You may already find the solution, but I didn't see the right answer here.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply