SSRS - Reporting Services Subscriptions not sending

Recently we ran into an issue with a Reporting Service Instance that was not sending any email subscriptions.  We could find no errors or obvious issues.  Of course a quick reboot solved the problem, but only temporarily, the next day, the issue was right back.  Time to call out for help, Microsoft Support.

Microsoft Support found the problem and provided us some useful scripts.  Basically the issue was that someone had created a snapshot, that ran every night.  The snapshot ran for 12+ hours, causing performance issues and other problems with all other subscriptions.  This is a "shared" reporting service instance, set up for several application groups.  The scripts from Microsoft were quite useful, i've listed them here, all of them are in my Script Vault.

I found the scripts very useful, as it addresses one of my continual issues, which is addressing capacity in shared environments.  For our Shared Reporting Service Environment, we create a Directory / Folder for each group and tweak permissions to keep each application separated, than we turn over full permissions to each sub-folder to the application group. 

Obviously the danger here is that someone can create a poorly performing report that effects the entire environment.  The scripts included below can help identify which reports run the longest.  We then modified this query a bit more to roll-up the report times, by sub-directory, so we can determine which application is using the most capacity in the environment.  We can then engage this group to optimize their reports or began capacity planning to build them a dedicated environment.

Here is a script listing of what I added to the Script Vault

1.  Find What User runs What Report - (Rpt Svc - Who Runs What)
2.  Find what subscriptions have been run and when - (Rpt Svc - Subscr Run When)
3.  Find what SQL Agent Schedule goes with what Report (Rpt Svc - SQL Agent to Report)
4.  Average Report RunTime - (Rpt Svc - Avg Rpt Runtime)
5.  Average Report RunTime Per Parent Folder - (Rpt Svc - Avg RunTime Pnt Fld)

We then used the last two queries to develop a Reporting Services reports that is available to the administrators of each directory, so they can manage their own performance and capacity, thanks to Tom Reeves,  for developing the last two.