none
Automatically Delete SQL Agent job - SSRS

    Question

  • Our business users have been scheduling tons of report subscriptions and this has resulted in creation of enormous number of SQL Agent Jobs.

    Here are my Questions:

    1. Is there any way from the Reporting Service Side (Ours is SharePoint Integrated mode)  to delete the subscriptions automatically after it has succeeded and is not a recurring job.

    2. If not, then can the DBAs automatically set the "Notifications" of all the SQL AGent jobs to "Automatically delete Job" when it succeds. Also, what happens to the recurring jobs?

    3. Does reproting service delete all the SQL agent jobs automatically after the subscription associated with it has been deleted?

    Monday, January 07, 2013 5:02 PM

Answers

  • Hi,

    3. Yes Reporting Services delete the SQL Agent Job for a subscription when it is deleted, in addition to that when the service starts it checks every subscription has a SQL Agent job, If there is missing jobs it will create them automatically.

    1. There is not any built in mechanism in Reporting Services to delete subscriptions automatically

    2. You could try that option but as I mention before if the subscription still exists in Reporting Services the job will be recreated when the service is restarted.

    I would recommend to create a program or PowerShell script that uses the Reporting Services Web Service to query for the subscriptions under certain criteria and delete those programmatically

    For subscription Web Service API reference check http://msdn.microsoft.com/en-us/library/ms154486.aspx

    Jaime -  http://blogs.msdn.com/b/jtarquino/

    This posting is provided "AS IS" with no warranties, and confers no rights

    • Marked as answer by SiddM Monday, January 07, 2013 6:24 PM
    Monday, January 07, 2013 6:07 PM

All replies

  • Hi,

    3. Yes Reporting Services delete the SQL Agent Job for a subscription when it is deleted, in addition to that when the service starts it checks every subscription has a SQL Agent job, If there is missing jobs it will create them automatically.

    1. There is not any built in mechanism in Reporting Services to delete subscriptions automatically

    2. You could try that option but as I mention before if the subscription still exists in Reporting Services the job will be recreated when the service is restarted.

    I would recommend to create a program or PowerShell script that uses the Reporting Services Web Service to query for the subscriptions under certain criteria and delete those programmatically

    For subscription Web Service API reference check http://msdn.microsoft.com/en-us/library/ms154486.aspx

    Jaime -  http://blogs.msdn.com/b/jtarquino/

    This posting is provided "AS IS" with no warranties, and confers no rights

    • Marked as answer by SiddM Monday, January 07, 2013 6:24 PM
    Monday, January 07, 2013 6:07 PM
  • This is really helpful. So to summarize everything, managing from Database will not help. We willl have to go the Powershell route.
    Monday, January 07, 2013 6:25 PM