Clear Up SSRS Subscriptions

One of the things with SSRS subscriptions is that they create a load of Agent Jobs that don’t clearly link back to specific reports.

SQL warns you not to mess with these directly, so you need to find the report and remove or edit the subscriptions via the SSRS interface.

We had loads that were expired, but the question was, what reports to they relate to?

The list below shows a number of Subscriptions that have not run for a while and are not scheduled to run again.

So, how do we find the associated reports?

If you double click a Job you’ll see the screen below

Click On “Steps”

Then Click on “Edit”

What we want is the @EventData which is the Subscription ID

If you then open MS Management Studio and run the following Code against your SSRS Report Database (replace the id with the one shown in the @EventData above):

SELECT 
	c.Path,
	c.Name
FROM dbo.Subscriptions s JOIN dbo.Catalog c
	ON s.Report_OID = c.ItemID
WHERE s.SubscriptionID = '56d31dd0-aca0-420c-940b-7d45f862db1c'

This will then show you the report path and name that the subscription relates to.

You can then connect to your SSRS reports and delete the subscription via the subscription options.