January 7, 2019 at 11:08 am
The following query returns 6 SQL Agent Jobs that appear to be for SSRS subscriptions that no longer exist.
Can anyone tell me if it's safe to delete these jobs?
SELECT j.*, js.*
FROM msdb.dbo.sysjobs j
LEFT JOIN msdb.dbo.sysjobsteps js
ON j.job_id = js.job_id
WHERE j.Name NOT IN
(SELECT
CONVERT(nvarchar(128), Schedule.ScheduleID)
FROM ReportServer.dbo.ReportSchedule
INNER JOIN ReportServer.dbo.Schedule
ON ReportSchedule.ScheduleID = Schedule.ScheduleID
INNER JOIN ReportServer.dbo.Subscriptions
ON ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID
INNER JOIN ReportServer.dbo.[Catalog]
ON ReportSchedule.ReportID = [Catalog].ItemID AND Subscriptions.Report_OID = [Catalog].ItemID
)
AND j.[description] LIKE 'This job is owned by a report server process.%'
January 7, 2019 at 11:37 am
MK Morris - Monday, January 7, 2019 11:08 AMThe following query returns 6 SQL Agent Jobs that appear to be for SSRS subscriptions that no longer exist.Can anyone tell me if it's safe to delete these jobs?
SELECT j.*, js.*
FROM msdb.dbo.sysjobs j
LEFT JOIN msdb.dbo.sysjobsteps js
ON j.job_id = js.job_id
WHERE j.Name NOT IN
(SELECT
CONVERT(nvarchar(128), Schedule.ScheduleID)
FROM ReportServer.dbo.ReportSchedule
INNER JOIN ReportServer.dbo.Schedule
ON ReportSchedule.ScheduleID = Schedule.ScheduleID
INNER JOIN ReportServer.dbo.Subscriptions
ON ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID
INNER JOIN ReportServer.dbo.[Catalog]
ON ReportSchedule.ReportID = [Catalog].ItemID AND Subscriptions.Report_OID = [Catalog].ItemID
)
AND j.[description] LIKE 'This job is owned by a report server process.%'
It should be fine. If something odd happens, you can restart reporting services and subscription jobs are checked at this time. If any subscription jobs are needed and not present, they will be created/recreated.
Sue
January 7, 2019 at 12:03 pm
Thanks Sue!
January 7, 2019 at 12:55 pm
Well I learned something new today. I was able to trace one of the SQL Agent jobs to an old report that was setup with the option "Render this report from a report snapshot" using a report-specific schedule.
In the Processing Options in Report Manager I switched it to "Always run this report with the most recent data" and "Do not cache..." then clicked Apply.
I refreshed my list of SQL Agent jobs and the one for running this snapshot was gone!
So a question I have now is, is there a way to tell SQL Agent jobs for snapshots from ones for subscriptions?
January 7, 2019 at 1:03 pm
MK Morris - Monday, January 7, 2019 12:55 PMWell I learned something new today. I was able to trace one of the SQL Agent jobs to an old report that was setup with the option "Render this report from a report snapshot" using a report-specific schedule.In the Processing Options in Report Manager I switched it to "Always run this report with the most recent data" and "Do not cache..." then clicked Apply.
I refreshed my list of SQL Agent jobs and the one for running this snapshot was gone!
So a question I have now is, is there a way to tell SQL Agent jobs for snapshots from ones for subscriptions?
Check the ReportSchedule table and try to verify some of this since none of the tables are documented.
When I was last testing it, In the ReportSchedule, ReportAction column the value 4 is subscription. I don't have a 2008R2 version to double check.
Others were: 1 for update cache, 2 for create snapshot, 3 for invalidate cache.
Sue
January 7, 2019 at 1:15 pm
That helps for the ones that exist in the Reporting Services tables (ReportSchedule, etc.). However, since this one (and 5 others) don't exist in the ReportSchedule table it won't help.
Because these SQL Agent jobs appear to be orphaned I only have the SQL Agent job properties for clues.
January 7, 2019 at 1:27 pm
MK Morris - Monday, January 7, 2019 1:15 PMThat helps for the ones that exist in the Reporting Services tables (ReportSchedule, etc.). However, since this one (and 5 others) don't exist in the ReportSchedule table it won't help.Because these SQL Agent jobs appear to be orphaned I only have the SQL Agent job properties for clues.
You would need to read the command in the job step for the jobs. They all call AddEvent but have different event types.
If I remember right, the subscriptions have Timed Subscription for event type. I need to look up in the API what the others would be - or you can try searching on AddEvent in the SOAP API. Otherwise I can check later today.
Sue
January 7, 2019 at 2:02 pm
In this case the command was -
exec [ReportServer].dbo.AddEvent @EventType='ReportExecutionUpdateSchedule', @EventData='c3f3ba1a-ab7e-4613-9016-7f397538419c'
January 7, 2019 at 2:11 pm
MK Morris - Monday, January 7, 2019 2:02 PMIn this case the command was -exec [ReportServer].dbo.AddEvent @EventType='ReportExecutionUpdateSchedule', @EventData='c3f3ba1a-ab7e-4613-9016-7f397538419c'
Subscriptions can launch with Event type TimedSubscription or SharedSchedule for shared scheduled reports.
However I believe SharedSchedule event type is used in some other situations as well.
Sue
January 7, 2019 at 2:16 pm
Thanks Sue. Your help is much appreciated.
May 21, 2020 at 5:00 pm
I am cleaning hundreds of orphaned report subscription jobs and wrote this:
select 'exec sp_delete_job '+job.name
from msdb.dbo.sysjobs job
inner join msdb.dbo.sysjobsteps jst on job.job_id=jst.job_id
left join reportserver.dbo.ReportSchedule rs on cast(rs.ScheduleID as varchar(255))=cast(job.name as varchar(255))
where jst.command like 'exec \[ReportServer\].dbo.AddEvent @EventType=%' escape'\'
and rs.SubscriptionID is null
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply