dbaSQL
asked on
Re-appearing SQL Server Agent jobs
I asked a question not too long ago about what would cause SQL Server Agent jobs to re-appear after they had been deleted. I was basically told by an Expert that because I deleted the jobs using DELETE sysjobs WHERE..., rather than using sp_delete_job, I was doing it an unsupported way, and nothing could be done.
Now I have the same problem again (same customer), where previously deleted SQL Server Agent jobs were removed, but they are magically re-appearring. Only this time, I am using sp_delete_job, as you can see from the attached script.
Per the customer, "after running the attached script, I verified that the jobs were gone. Yet, by the next morning they're back. They don't produce output because I have long since deleted the folders they write to".
They are SSRS Subscription Agent jobs. Can anybody tell me why they are re-appearing after being removed?
DeleteJobs.sql
Now I have the same problem again (same customer), where previously deleted SQL Server Agent jobs were removed, but they are magically re-appearring. Only this time, I am using sp_delete_job, as you can see from the attached script.
Per the customer, "after running the attached script, I verified that the jobs were gone. Yet, by the next morning they're back. They don't produce output because I have long since deleted the folders they write to".
They are SSRS Subscription Agent jobs. Can anybody tell me why they are re-appearing after being removed?
DeleteJobs.sql
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Now just to correlate the job_id's to the subscriptionIDs.... I think using the script I previously uploaded, I could add the job_ids into the @jobs table along with the names. Then I could use that to identify (and remove) the related subscriptionID. That's what I'm working, I will send status when I've got it.
ASKER
Hi Jim. Can you help out? See the code, I am only trying to associate the job with its subscription, so that it can then be removed. I thought I finally had it, but the SubscriptionID is the same for all values returned in this construct. My join isn't quite right.
Do you see it?
Do you see it?
declare @Jobs table (
JobName varchar(50),SupbscriptionID varchar(36),JobStatus int )
-- the following are 42 closed parish jobs:
insert into @Jobs (jobname,jobstatus)
values ('1F6D56BB-915A-41E3-8F4D-9548408EC842', 0),
('9740ED7F-7D1B-4FB2-86C9-D9CFBC806701', 0),
('A7386060-D522-4D6B-BB46-32F0E774EA5C', 0),
('DDAF7682-D9BA-49EF-BC08-BBA55CEB0DBF', 0),
('E7780320-0BB9-43E9-8F0A-15DE19DEBCF0', 0),
('ED2B2D22-875E-4BB8-96CD-9C300DB4DEBE', 0),
('4DEF774F-B208-4719-BB2F-6B2A38314ECC', 0),
('71DEE13A-241D-4DF8-A7A4-F0FF3BA92331', 0),
('A9BA39A8-FBF6-4931-A346-2FF3D8681AF3', 0),
('C92BBA6C-A878-44D9-98E4-1CB4423F66E9', 0),
('DB1A0DE8-30F7-4EEF-8049-09ABF8BA697D', 0),
('FBB80ADB-4F9B-4E7A-9BCD-FB3EDC472422', 0),
('2B367512-2C55-4246-8C94-91DAED75EA32', 0),
('6773E903-B520-453C-8185-2D2963BF024E', 0),
('B4F67D9F-11CC-4CB8-BE92-D4CECF8433C4', 0),
('ED1133C2-0FB8-43F9-B78A-45AC14C83D92', 0),
('F238CC8F-8B12-4C9F-8E23-804EF0D9A330', 0),
('F454DDBF-C3D1-45A7-9576-7F64A8DCD7C7', 0),
('406A1BC6-047C-4F32-9EF6-0AB63436F3AF', 0),
('4CA2C0D0-83BC-47D3-B66E-F6DE93F83753', 0),
('5EE6AC0A-7E60-4A07-A78D-C090D3D088BC', 0),
('737EA669-6526-4C2C-A567-B0CE21A8C4C7', 0),
('A1B50ED2-42CD-45EB-8AAC-36ABF9A4A574', 0),
('CF153700-D4FB-4989-BDDD-94BF6C229ECC', 0),
('535FEED3-F9FD-4D4B-A586-03E71B44E20D', 0),
('5445350A-43A8-4EE7-BBB6-7EE15146A577', 0),
('55CD8F71-25F9-4359-9779-16EFF03EB0B5', 0),
('76FFD517-2B52-4392-B1EC-4DDEBB3A395A', 0),
('A43E07B2-BBF7-4B04-8080-8EFFA0CDDB18', 0),
('D7359A5E-1CE8-4709-8587-B276434C2C91', 0),
('2EA3E304-F3F3-493B-A0FD-A8AAFF961E61', 0),
('36BAE3D5-5F86-4F73-ABC6-43841C4FB37A', 0),
('4EEE492C-DCEF-45E4-8EED-D17B53AF38EE', 0),
('7FEE0226-4A3C-45A3-AA0E-DECBB7D757E0', 0),
('B8F69C1D-CE6D-4174-B657-DCD29AA0922C', 0),
('E2AB28BB-6CFC-4ED3-8AA9-F87936DD5465', 0),
('27050324-9495-4811-BFAA-E3B290DFC50B', 0),
('600A5E93-2594-40A5-9279-45D3EA5304CC', 0),
('6510564C-9456-4B51-BC54-E8F4526D3ED1', 0),
('7DE13B6F-1962-42A6-8C7D-FFD383DC8E3F', 0),
('DEE2FE1A-C489-4FBA-86AF-FA19710170B1', 0),
('FEC9F0C6-D57A-488D-84BD-0329C8412E6D', 0),
-- the following are the 6 duplicate parish ,jobs
('F8533BDF-D67B-471B-97B6-F677AB09D07E', 0),
('4C9B44CA-CDE2-4CF6-B5D6-7E1EE54D8997', 0),
('D7027F69-48CF-4ED1-AFEF-A328AD1CBECA', 0),
('6B13C3C8-86D5-4B10-AB4C-6E09CD17F2F2', 0),
('5D430432-5AEE-48F3-B45B-7B832E234A5E', 0),
('F0EFC956-7FCC-44CB-895A-4BC1C047CADD', 0),
('', 0)
--select count(*) [TotalJobs] from @Jobs
UPDATE @jobs
SET SupbscriptionID = CONVERT(VARCHAR(36),sub.SubscriptionID)
FROM ReportServer.dbo.ReportSchedule rs INNER JOIN ReportServer.dbo.Schedule s
ON rs.ScheduleID = s.ScheduleID INNER JOIN ReportServer.dbo.Subscriptions sub
ON rs.SubscriptionID = sub.SubscriptionID
WHERE EXISTS(SELECT 1 FROM @Jobs j JOIN msdb..sysjobs sj
ON j.JobName collate SQL_Latin1_General_CP1_CI_AS = sj.name collate SQL_Latin1_General_CP1_CI_AS
WHERE CONVERT(VARCHAR(36),s.ScheduleID) = j.JobName)
SELECT * FROM @jobs
ASKER
Jim, do you see the flaw in my logic?
ASKER
I've corrected my join. All is well. Thank you for the help, Jim. Much appreciated.
ASKER
No. I thought I had it right, but the deletion of the subscriptions did not complete correctly. Are there any SSRS Experts who can help me to properly associate a handful of jobs to the appropriate Subscriptions, so they can be deleted while using sp_delete_jobs to remove the job? The code that I've copied in does everything except remove the subscription. It failed to do so with this error:
Cursor Fetch: Implicit conversion from data type uniqueidentifier to int is not allowed.
This is the problem spot:
DELETE a
FROM dbo.Subscriptions a INNER JOIN #jobsToProcess jtp
ON a.SubscriptionID = jtp.SubscriptionID
WHERE jtp.JobStatus = 1
To correct it, I changed it to this:
DELETE a
FROM dbo.Subscriptions a INNER JOIN #jobsToProcess jtp
ON CAST(a.SubscriptionID AS VARCHAR(40)) = jtp.SubscriptionID
WHERE jtp.JobStatus = 1
After the failure, I wrote the 48 jobs back into sysjobs (from JobsTMP that is commented out in the code) -- but now the code as I've posted it is not writing anything to #jobsToProcess. It only writes the jobnames into the @jobs table variable.
Jim, I'm unsure if you will see this because I closed it prematurely. Please let me know if you do, and whether you'd like me to open a new one.
Cursor Fetch: Implicit conversion from data type uniqueidentifier to int is not allowed.
This is the problem spot:
DELETE a
FROM dbo.Subscriptions a INNER JOIN #jobsToProcess jtp
ON a.SubscriptionID = jtp.SubscriptionID
WHERE jtp.JobStatus = 1
To correct it, I changed it to this:
DELETE a
FROM dbo.Subscriptions a INNER JOIN #jobsToProcess jtp
ON CAST(a.SubscriptionID AS VARCHAR(40)) = jtp.SubscriptionID
WHERE jtp.JobStatus = 1
After the failure, I wrote the 48 jobs back into sysjobs (from JobsTMP that is commented out in the code) -- but now the code as I've posted it is not writing anything to #jobsToProcess. It only writes the jobnames into the @jobs table variable.
Jim, I'm unsure if you will see this because I closed it prematurely. Please let me know if you do, and whether you'd like me to open a new one.
SET NOCOUNT ON;
DECLARE @Jobs TABLE (JobName VARCHAR(50))
DECLARE @JobErrors TABLE (
JobName VARCHAR(50),
Job_ERROR_NUMBER INT,
Job_ERROR_SEVERITY INT,
Job_ERROR_STATE INT,
Job_ERROR_MESSAGE VARCHAR(255))
-- the following are 42 closed parish jobs:
INSERT @Jobs (jobname)
VALUES('1F6D56BB-915A-41E3-8F4D-9548408EC842'),
('9740ED7F-7D1B-4FB2-86C9-D9CFBC806701'),
('A7386060-D522-4D6B-BB46-32F0E774EA5C'),
('DDAF7682-D9BA-49EF-BC08-BBA55CEB0DBF'),
('E7780320-0BB9-43E9-8F0A-15DE19DEBCF0'),
('ED2B2D22-875E-4BB8-96CD-9C300DB4DEBE'),
('4DEF774F-B208-4719-BB2F-6B2A38314ECC'),
('71DEE13A-241D-4DF8-A7A4-F0FF3BA92331'),
('A9BA39A8-FBF6-4931-A346-2FF3D8681AF3'),
('C92BBA6C-A878-44D9-98E4-1CB4423F66E9'),
('DB1A0DE8-30F7-4EEF-8049-09ABF8BA697D'),
('FBB80ADB-4F9B-4E7A-9BCD-FB3EDC472422'),
('2B367512-2C55-4246-8C94-91DAED75EA32'),
('6773E903-B520-453C-8185-2D2963BF024E'),
('B4F67D9F-11CC-4CB8-BE92-D4CECF8433C4'),
('ED1133C2-0FB8-43F9-B78A-45AC14C83D92'),
('F238CC8F-8B12-4C9F-8E23-804EF0D9A330'),
('F454DDBF-C3D1-45A7-9576-7F64A8DCD7C7'),
('406A1BC6-047C-4F32-9EF6-0AB63436F3AF'),
('4CA2C0D0-83BC-47D3-B66E-F6DE93F83753'),
('5EE6AC0A-7E60-4A07-A78D-C090D3D088BC'),
('737EA669-6526-4C2C-A567-B0CE21A8C4C7'),
('A1B50ED2-42CD-45EB-8AAC-36ABF9A4A574'),
('CF153700-D4FB-4989-BDDD-94BF6C229ECC'),
('535FEED3-F9FD-4D4B-A586-03E71B44E20D'),
('5445350A-43A8-4EE7-BBB6-7EE15146A577'),
('55CD8F71-25F9-4359-9779-16EFF03EB0B5'),
('76FFD517-2B52-4392-B1EC-4DDEBB3A395A'),
('A43E07B2-BBF7-4B04-8080-8EFFA0CDDB18'),
('D7359A5E-1CE8-4709-8587-B276434C2C91'),
('2EA3E304-F3F3-493B-A0FD-A8AAFF961E61'),
('36BAE3D5-5F86-4F73-ABC6-43841C4FB37A'),
('4EEE492C-DCEF-45E4-8EED-D17B53AF38EE'),
('7FEE0226-4A3C-45A3-AA0E-DECBB7D757E0'),
('B8F69C1D-CE6D-4174-B657-DCD29AA0922C'),
('E2AB28BB-6CFC-4ED3-8AA9-F87936DD5465'),
('27050324-9495-4811-BFAA-E3B290DFC50B'),
('600A5E93-2594-40A5-9279-45D3EA5304CC'),
('6510564C-9456-4B51-BC54-E8F4526D3ED1'),
('7DE13B6F-1962-42A6-8C7D-FFD383DC8E3F'),
('DEE2FE1A-C489-4FBA-86AF-FA19710170B1'),
('FEC9F0C6-D57A-488D-84BD-0329C8412E6D'),
-- the following are the 6 duplicate parish ,jobs
('F8533BDF-D67B-471B-97B6-F677AB09D07E'),
('4C9B44CA-CDE2-4CF6-B5D6-7E1EE54D8997'),
('D7027F69-48CF-4ED1-AFEF-A328AD1CBECA'),
('6B13C3C8-86D5-4B10-AB4C-6E09CD17F2F2'),
('5D430432-5AEE-48F3-B45B-7B832E234A5E'),
('F0EFC956-7FCC-44CB-895A-4BC1C047CADD');
SELECT COUNT(*) [@jobs] FROM @jobs
-- put it into #jobsToProcess with the SubscriptionID
IF OBJECT_ID('tempdb..#jobsToProcess') IS NOT NULL
DROP TABLE #jobsToProcess;
SELECT CAST(sj.name AS VARCHAR(40))[JobName],sub.SubscriptionID,0 [JobStatus]
INTO #jobsToProcess
FROM msdb.dbo.sysjobs sj INNER JOIN msdb.dbo.sysjobsteps js
ON sj.job_id = js.job_id INNER JOIN @jobs j1
ON sj.name COLLATE SQL_Latin1_General_CP1_CI_AS = j1.JobName COLLATE SQL_Latin1_General_CP1_CI_AS INNER JOIN dbo.Schedule sc
ON CAST(sc.ScheduleID AS VARCHAR(40)) = j1.JobName INNER JOIN dbo.ReportSchedule rs
ON sc.ScheduleID = rs.ScheduleID INNER JOIN dbo.Subscriptions sub
ON rs.SubscriptionID = sub.SubscriptionID
-- create bkup tables
--IF OBJECT_ID('JobsTMP','U') IS NOT NULL
--DROP TABLE dbo.JobsTMP;
--SELECT * INTO JobsTMP FROM msdb.dbo.sysjobs
--WHERE name IN(SELECT JobName FROM #jobsToProcess)
--IF OBJECT_ID('SubscriptionsTMP','U') IS NOT NULL
--DROP TABLE dbo.SubscriptionsTMP;
--SELECT * INTO SubscriptionsTMP FROM ReportServer.dbo.Subscriptions
--WHERE SubscriptionID IN(SELECT SubscriptionID FROM #jobsToProcess)
--SELECT COUNT(*) FROM dbo.JobsTMP
--SELECT COUNT(*) FROM dbo.SubscriptionsTMP
-- declare our cursor for the job and subscription removal
DECLARE
@JobName VARCHAR(50),
@SubscriptionID VARCHAR(40),
@JobStatus INT,
@JobCursor CURSOR
SET @JobCursor = CURSOR FOR
SELECT JobName, CAST(SubscriptionID AS VARCHAR(40)) [SubscriptionID], JobStatus
FROM #jobsToProcess FOR UPDATE OF JobStatus
OPEN @JobCursor
FETCH NEXT FROM @JobCursor INTO @JobName,@SubscriptionID,@JobStatus
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
-- remove job
EXEC msdb.dbo.sp_delete_job @job_name = @JobName
-- update #jobsToProcess.JobStatus
UPDATE #jobsToProcess SET JobStatus = 1 WHERE CURRENT OF @JobCursor
-- remove related subscription
DELETE a
FROM dbo.Subscriptions a INNER JOIN #jobsToProcess jtp
ON a.SubscriptionID = jtp.SubscriptionID
WHERE jtp.JobStatus = 1
END TRY
BEGIN CATCH -- set status to zero if an error occurs trying to delete job
UPDATE #jobsToProcess SET JobStatus = 0 WHERE CURRENT OF @JobCursor
INSERT INTO @JobErrors VALUES(-- write errors, if necessary
@JobName,
ERROR_NUMBER(),
ERROR_SEVERITY(),
ERROR_STATE(),
ERROR_MESSAGE()
)
END CATCH
FETCH NEXT FROM @JobCursor INTO @JobName, @SubscriptionID, @JobStatus
END
CLOSE @JobCursor;
DEALLOCATE @JobCursor;
SELECT COUNT(*) [JobsDeleted] FROM #jobsToProcess WHERE JobStatus = 1
SELECT COUNT(*) [Job_NotDeleted] FROM #jobsToProcess WHERE JobStatus = 0
SELECT JobName, Job_ERROR_MESSAGE FROM @JobErrors
SET NOCOUNT OFF;
ASKER
That must be it. I don't know why I did not think about that. Thank you, jim!!! I simply need to remove the subscriptions, too.