SSRS Subscription jobs disabled, yet still running
SQL Server v2012 - we have a bunch of SSRS subscription Agent jobs in place on a server that I removed the schedules for a couple days ago, using this method:
SELECT ss.*
--DELETE ss
FROM msdb.dbo.sysjobschedules ss JOIN msdb.dbo.sysjobs sj
ON ss.job_id = sj.job_id
WHERE sj.[description] = 'This job is owned by a report server process. Modifying this job could result in database incompatibilities. Use Report Manager or Management Studio to update this job.'
Worked just fine. I checked the jobs afterward via tSQL and the guie -- none of them had a schedule.
The following morning I found them to be scheduled again. Didn't have the time to get in there and figure it out, so I just disabled the jobs with this:
update sysjobs
set enabled = 0
where enabled = 1
and description like 'this job is owned by%'
I checked again with this, and none of the Report Server Subscription jobs were enabled:
SELECT * FROM msdb..sysjobs WHERE enabled = 1 AND [description] LIKE 'this job is owned by%'
Yet last night they jobs ran. Right click, view history --- every one of them ran last night. I have just now checked sysjobs again, and they are still disabled.
SSRS is running, but again, all of those jobs are disabled within sysjobs.
What is it that is running these jobs, and how are my Agent job schedules being recreated after I've removed them?
I realize directly editing the system tables is not thought to be the best approach, but I have been using this method to enable or disable these SSRS Subscription jobs for nearly a year now, never with any problem. It was only after I removed the schedules that the disabled jobs actually ran. The delete from sysjobschedules -- I have never run that before. This project is a very long, drawn out project, and I have updated msdb.dbo.sysjobs.enabled numerous times. Never had any problems until this one -- which was prefaced with that top piece of code in this post. Any insight is much appreciated.
SELECT ss.*
--DELETE ss
FROM msdb.dbo.sysjobschedules ss JOIN msdb.dbo.sysjobs sj
ON ss.job_id = sj.job_id
WHERE sj.[description] = 'This job is owned by a report server process. Modifying this job could result in database incompatibilities. Use Report Manager or Management Studio to update this job.'
Worked just fine. I checked the jobs afterward via tSQL and the guie -- none of them had a schedule.
The following morning I found them to be scheduled again. Didn't have the time to get in there and figure it out, so I just disabled the jobs with this:
update sysjobs
set enabled = 0
where enabled = 1
and description like 'this job is owned by%'
I checked again with this, and none of the Report Server Subscription jobs were enabled:
SELECT * FROM msdb..sysjobs WHERE enabled = 1 AND [description] LIKE 'this job is owned by%'
Yet last night they jobs ran. Right click, view history --- every one of them ran last night. I have just now checked sysjobs again, and they are still disabled.
SSRS is running, but again, all of those jobs are disabled within sysjobs.
What is it that is running these jobs, and how are my Agent job schedules being recreated after I've removed them?
I realize directly editing the system tables is not thought to be the best approach, but I have been using this method to enable or disable these SSRS Subscription jobs for nearly a year now, never with any problem. It was only after I removed the schedules that the disabled jobs actually ran. The delete from sysjobschedules -- I have never run that before. This project is a very long, drawn out project, and I have updated msdb.dbo.sysjobs.enabled numerous times. Never had any problems until this one -- which was prefaced with that top piece of code in this post. Any insight is much appreciated.
ASKER
As I said, I realize that the updates to system tables are illadvised. Regardless, I am still looking for an answer to exactly what happend with my job schedules and the execution when they were disabled. Your document is great, but it does not tell me what happened.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It may be that I have not expressed the problem well enough, but no reason to keep the inquiry open. Thank you for the input, Nakul.
For this case, now that you have already updated the system tables, please restart the SQL Server Database Engine, SQL Agent and SSRS services and you should be all set.