Test Reporting Services Server sends old subscriptions when turned on - help!!!

Question

We created a new Reporting Services server that we are now running tests on.  I backed up our production ReportServer & ReportServerTempDB databases and restored them to the new server keeping RS off.  After a few weeks, I turned Reporting Services on on the new server for testing and it started running / sending subscriptions that were "missed" since the restore time.  I shut down reporting services to research. 

Even though Reporting Services was stopped, I learned that subscriptions were being queued b/c both the relational engine & sql agent had been running the whole time.  As a result, I thought I could restart Reporting Services as long as the sql agent was stopped.  I was wrong!!!  I don't think any new subscriptions were firing, but a lot of our people were receiving emails for reports executed on the date of the first mishap yet sent today (one week later).

Can anyone explain what is happening?  Also, is there a way to clear the queue for the next time we need to turn RS on?  I ran some queries that are saying no subscriptions ran the past few days so everything happening must be from before.  Thanks!!! 

Answer 1

I will reply to my own question in hopes that it will be helpful to others.  Here's what happened: we set up rs on a test server and restored ReportServer & ReportServerTempDB databases from production to the test server; we kept rs turned off, but the mssql & sql agent remained on; we turned rs on for testing 2 wks later and saw that it was sending out subscriptions "missed" in the past 2 wks; we turned rs off at this point; 2 wks later we turned rs on again, but stopped the agent in order to prevent subscriptions from running; we saw that subscriptions were being sent out again except this time with an execution date from two wks prior; once again we turned rs off.

Here's what I think happened at each step: 

- we turned rs on for testing 2 wks later and saw that it was sending out subscriptions "missed" in the past 2 wks

This happened because over the two week span the sql agent was still on.  Subscription jobs were firing regularly writing events to the ReportServer event table to be executed when rs would be turned back on

- 2 wks later we turned rs on again, but stopped the agent in order to prevent subscriptions from running; we saw that subscriptions were being sent out again except this time with an execution date from two wks prior

This happened with the agent turned off because there were already events written to the event table from prior.  These events were then written by the rs service to the notifications table to execute as threads become available. 

*** Solution to make sure no subscriptions fire: When turning the test server back on, make sure both the Event & Notifications table in the ReportServer database have NO RECORDS.  Truncate / Delete from the tables to ensure this.  While rs is on, query the ReportServer ActiveSubscriptions table as well to make sure nothing is actively running.

Blogs / Posts that were helpful to me:

Adam W. Saxton: http://blogs.msdn.com/b/psssql/archive/2009/02/02/why-aren-t-my-subscriptions-working.aspx

Dean Kalanquin-MSFT: http://blogs.msdn.com/b/deanka/archive/2010/02/16/troubleshooting-subscriptions-part-ii-using-the-report-services-trace-log-file.aspx

Answer 2

Hi jb6238,

I am glad to hear that you have got the issue resolved by yourself. Thank you for the valuable sharing. I have marked your post as the answer so that other community members who face with the same issue can benefit from this thread.

Regards,

---

Mike Yin
TechNet Community Support