Hi,

We have a data driven subscription that creates and xml file for export to a third party for processing. This has been working fine for several months but now it is running twice for a single schedule.

I found a thread (https://social.msdn.microsoft.com/forums/sqlserver/en-US/55a8410f-e29e-4918-9cce-3de525755236/sql-server-2008-r2-subscription-running-twice) which has a similar story but for me the SSRS log doesn't help. All this shows is that the job ran successfully twice.

We are using SQL Server 2012 Enterprise.

Any suggestions would be appreciated?

JST

Hi John,

Per my understanding that your data driven subscription one day suddenly send the same subscription twice one day which work fine before, right?

Does the problem occurred only once or many times? The issue can be caused by many factors:

1. When a scheduled operation is defined, the report server creates a corresponding job in SQL Server Agent and runs the job on the date and time specified in the schedule. If somebody run the subscription manually after the job executed, the users can got two emails, so please make sure the subscription isnt run manually during the time by someone.
2. The SQL Server Agent service calculates an incorrect NEXT_RUN_TIME value for the job. Therefore, the job does not always run at the correct times. This issue occurs because an error causes a date value to be changed unexpectedly, the date value is used to calculate the NEXT_RUN_TIME value. If you got the subscription execute twice many times. please edit reset the subscription.
3. Did it sent twice with the same time, if you have one subscription failed to send at the proper time and it have delayed, it may be send at the next time.
4. Gererally, use SQL Server Agent Service to fire an event. This event will add a record to a table in ReportServer database. SQL Server Reporting Services windows services monitor the table, if the table has data, a subscription will be delivered, and the record will be deleted.
   In this case, the issue seems to be caused by the record is not deleted correctly. It should happen because the record is locked when there has many subscriptions at the same time.
   To fix the issue, please make sure we don't have multiple subscriptions which are run at the same time.

If you still have any problem, please feel free to ask.

Regards
Vicky Liu

If you have any feedback on our support, please click here.

Hi Vicky,

Thankyou for your response, when I was unable to find a 'real' solution to the problem yesterday I did the equivalent of "turn it off and on again". I deleted the old subscription that was causing problems and created a new one to replace it. This morning the new subscription had run correctly (just the once) so further analysis is not feasible. That said, we will continue to monitor this and other subscriptions and get back should the problem return.

I can cover some of the points you asked in your response.

The problem had occurred on several occasions (3 to be exact) when the business complained about the issues being caused and I began investigation.

1. The job was not run manually, it was timed for 03:05 and there was nobody using the system at that time on the days in question.

2. I am not quite sure what you are asking here, are you saying a bug in the agent service can duplicate the next_run_time and if this is happening to recreate the subscription? If so, that is the action I took.

3. The times that the subscription ran were not identical, there was between 10 and 15 seconds between executions as recorded in the Report Server database. How much margin would you say would count as simultaneous? 

4. We are currently delivering very few reports by subscription and no others at this exact time so I do not feel that this is the issue here, unless there is a background process running against this table that might lock it?

Again, thankyou for your time and we will add to this thread should the problem arise again.

John

Did recreating the subscription work?

It did for the remainder of my contract - about 4 weeks. I can't comment beyond that I'm afraid.