CODEWITHSUNDEEP
sqljobs
Mustafa Kapasi
Mustafa Kapasi

Reputation: 11

Cannot Delete a SQL job

I have disabled log shipping on a SQL 2005 database and deleted the log shipping DB on the secondary server. However i cannot delete the LSRestore_DB___ job, either by T-SQL (sp_delete_log_shipping_primary_secondary, sp_delete_job) or using the management studio on the secondary server. It just wont go. The query keeps on executing for a good 7 hours. Tried disabling, still doesn't delete. Restarted the server too. Also tried the Can anyone help me delete this SQL job please ?

Upvotes: 1

Views: 1367

Answers (2)

huoxudong125
huoxudong125

Reputation: 2056

There is a good article Can’t Delete Jobs.In the article the author provided a script to solve the problem,good job! 

`CREATE PROC dbo.DropJob
@JobName AS VARCHAR(200) = NULL 
AS 
DECLARE @msg AS VARCHAR(500);

IF @JobName IS NULL
BEGIN 
SET @msg = N'A job name must be supplied for parameter @JobName.';
RAISERROR(@msg,16,1);
RETURN;
END
IF EXISTS (
SELECT subplan_id FROM msdb.dbo.sysmaintplan_log WHERE subplan_id IN
( SELECT subplan_id FROM msdb.dbo.sysmaintplan_subplans WHERE job_id IN 
(SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = @JobName)))
BEGIN
DELETE FROM msdb.dbo.sysmaintplan_log WHERE subplan_id IN
( SELECT subplan_id FROM msdb.dbo.sysmaintplan_subplans WHERE job_id IN 
(SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = @JobName));

DELETE FROM msdb.dbo.sysmaintplan_subplans WHERE job_id IN
(SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = @JobName);

EXEC msdb.dbo.sp_delete_job @job_name=@JobName, @delete_unused_schedule=1;
END
ELSE IF EXISTS (
SELECT subplan_id FROM msdb.dbo.sysmaintplan_subplans WHERE job_id IN 
(SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = @JobName))
BEGIN 
DELETE FROM msdb.dbo.sysmaintplan_subplans WHERE job_id IN 
(SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = @JobName);

EXEC msdb.dbo.sp_delete_job @job_name=@JobName, @delete_unused_schedule=1;
END
ELSE
BEGIN 
EXEC msdb.dbo.sp_delete_job @job_name=@JobName, @delete_unused_schedule=1; 
END 
GO`

Now you can call the SP with the following;

`USE [msdb];
EXEC dbo.DropJob @JobName = N'Shrink_AWP_Databases.Subplan_1';`

Upvotes: 1

mattruma
mattruma

Reputation: 16677

Have your tried setting the allow editing of system tables, and going directly into the system table that holds the job information and tried deleting the row from there?

Make sure to be extra careful when doing this, not recommended, but sometimes direct editing to the system tables needs to be done.

Upvotes: 0

Related Questions