I was contemplating running around in circles, as that's what my brain was doing trying to figure out this inane error with Microsoft SQL Server 2005 Maintenance Plans and associated jobs (subtasks).
I had a brand new install of SQL server and used the Maintenance Plan wizard to create a series of jobs for full backup, differential backup, and transaction log backup.
Seemed to be working just fine, until someone else changed the 'sa' password and all the jobs failed left right and center.
It took me a while to figure out that was the cause, and the last day to figure out how to delete a maintenance plan and associated jobs once the sa password was changed. Couldn't delete them manually, I got an error saying the login failed for user 'sa'.
I could delete all but one of the subplans by listing them first under maintenance plans, but one could never be deleted.
This turned out to be the major error:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
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'.
The statement has been terminated. (Microsoft SQL Server, Error: 547)
And at long last, after many, many searches, I found this marvelous post on how to manually delete the jobs and maintenance plans with TSQL code:
MS Forums
and this one, which was inordinately useful:
sql-server-2005-delete-maintenance-plan-error
And here's pretty much what I did in TSQL to delete the subplan and the maintenance plan 'SystemDB-MaintenancePlan':
USE [msdb]
declare @job_name varchar(100)
set @job_name = N'SystemDB-MaintenancePlan.Subplan_1'
delete sysmaintplan_log
FROM sysmaintplan_subplans AS subplans INNER JOIN
sysjobs_view AS syjobs ON subplans.job_id = syjobs.job_id INNER JOIN
sysmaintplan_log ON subplans.subplan_id = sysmaintplan_log.subplan_id
WHERE (syjobs.name = @job_name)
USE [msdb]
declare @job_name varchar(100)
set @job_name = N'SystemDB-MaintenancePlan.Subplan_1'
delete sysmaintplan_subplans
FROM sysmaintplan_subplans AS subplans INNER JOIN
sysjobs_view AS syjobs ON subplans.job_id = syjobs.job_id
WHERE (syjobs.name = @job_name)
declare @job_name varchar(100)
set @job_name = N'SystemDB-MaintenancePlan.Subplan_1'
delete
from msdb.dbo.sysjobs_view where name = @job_name
delete
FROM msdb.dbo.sysmaintplan_plans
where name = 'SystemDB-MaintenancePlan'
other commands I find useful:
select * FROM sysmaintplan_subplans
select * FROM sysmaintplan_plans
Happy deleting! Thanks so much Gedzuks!
No comments:
Post a Comment