SQL Server – Cannot Drop Idle Job

sql serversql-server-2005sql-server-agent

Currently, I am transitioning a server from a company to another.

They want to keep SQL Server installed on the server so we are wiping clean all the databases, maintenance plans and jobs.

I'm trying to delete a bunch of SQL Server Agent jobs but even though they are disabled they are still on "IDLE" mode so it raises me the following error when trying to delete them:

Drop failed for Job job_name.Subplan_1. (Microsoft.SqlServer.Smo)

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)

How can I remove the IDLE status from the job so the delete gets through?

Best Answer

You have maintenance plans. Try to follow these steps:

Find the maintenance plan name and id that you want to delete. Write down the id of the one you want to delete.

 SELECT name, id FROM msdb.dbo.sysmaintplan_plans

--Place the id of the maintenance plan you want to delete into the below query to delete the entry from the log table:

    DELETE FROM msdb.dbo.sysmaintplan_log WHERE plan_id = ''

Place the id of the maintenance plan you want to delete into the below query and delete the entry from subplans table:

    DELETE FROM msdb.dbo.sysmaintplan_subplans WHERE plan_id = ''

Place the id of the maintenance plan you want to delete into the below query to delete the entry from the plans table:

    DELETE FROM msdb.dbo.sysmaintplan_plans WHERE id = ''

Now you can delete the jobs from Management Studio.

Related Solutions

Sql-server – Unable to delete SQL Server 2008 R2 login

If you have many jobs and do not know which job is owned by this login, this query will tell you the name of jobs owned by them:

SELECT j.name
FROM msdb.dbo.sysjobs AS j
INNER JOIN sys.syslogins AS l ON j.owner_sid = l.sid
WHERE l.name = 'loginYouWantToDelete'

Once you have found the job, you can delete it using: sp_delete_job: (From MSDN)

EXEC msdb.dbo.sp_delete_job
    @job_name = N'JobToDelete';

SQL Server 2005 – Foreign Keys Can’t Be Added Because They Exist But They Don’t Exist

You cannot add the constraints to tblUserRoles because the data within this table already violates the constraints that you are trying to add.

a) Double check that all UserID values in tblUserRoles reference UserID values in tblUsers

b) Do the same as the above for tblRoles and RoleID.

c) As I imagine both of the above will return records that are not in the referenced table, you'll need to clean up your data before adding the Foreign Keys

d) Name you foreign keys something useful! If I came to work on your database, how am I supposed to know what Relationship26 means?!

Best Answer

Related Solutions

Sql-server – Unable to delete SQL Server 2008 R2 login

SQL Server 2005 – Foreign Keys Can’t Be Added Because They Exist But They Don’t Exist

Related Question