Orphan SSRS SQL Agent Jobs?

  • MK Morris

    Old Hand

    Points: 309

    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.%’

  • Sue_H

    SSC Guru

    Points: 89426

    MK Morris - Monday, January 7, 2019 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.%'

    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

  • MK Morris

    Old Hand

    Points: 309

    Thanks Sue!

  • MK Morris

    Old Hand

    Points: 309

    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?

  • Sue_H

    SSC Guru

    Points: 89426

    MK Morris - Monday, January 7, 2019 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?

    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

  • MK Morris

    Old Hand

    Points: 309

    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.

  • Sue_H

    SSC Guru

    Points: 89426

    MK Morris - Monday, January 7, 2019 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.

    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

  • MK Morris

    Old Hand

    Points: 309

    In this case the command was –

    exec [ReportServer].dbo.AddEvent @EventType=’ReportExecutionUpdateSchedule’, @EventData=’c3f3ba1a-ab7e-4613-9016-7f397538419c’

  • Sue_H

    SSC Guru

    Points: 89426

    MK Morris - Monday, January 7, 2019 2:02 PM

    In 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

  • MK Morris

    Old Hand

    Points: 309

    Thanks Sue.  Your help is much appreciated.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply