December 4, 2014 at 7:44 am
Hello,
Question that i cannot find a definitive answer to.
We turned off SSRS service over the weekend due to maintenance. We have a number of reports with timed subscriptions.
When i started the service the server went crazy for a while and caused jobs to hang so i turned it off until i could sit and watch it properly....which i have done this morning. However, users have been stating that there are multiple reports outputs in a particular hour (for both the first and seconds restarts) when there should only be 1. They dont have the date/time range for the data in the report....only when the report was run....so cannot track it that way.
But they are stating that some missed subscriptions (while the service was off) have been run. I have checked some of the code and it doesnt seem like it uses the last runtime as a basis.
As an example....a report that has a timed subscription that runs every hour and uses DATEADD(DAY, -1, GETDATE()) as the range for the data executed 60 times within the last hour since i started the service and is no running once an hour as it should.
But the service was off for around 140 hours....which doesnt make sense.
Can someone give me an idea of what may cause this? Havent even found anyone mentioning this as yet....
Regards,
Samuel
December 4, 2014 at 9:13 am
OK....i have run a simple test (select getdate()) and can confirm that SSRS will rerun missed subscriptions.
- created a very simple report to output to a file (filename appended)
- set the subscription to run every minute
- waited for 3 runs
- stopped the SSRS service
- waited 3 minutes and started the service
- saw 3 new reports appear - all with the same createddate
- subscription carries on as configured once that has happened
this is a simple report using default SSRS settings:
- always run report with most recent data
- no caching of the report
- no render from snapshot
i am running through the SSRS config file to see if i can spot anything....
thanks
samuel
December 4, 2014 at 9:26 am
lilywhites (12/4/2014)
OK....i have run a simple test (select getdate()) and can confirm that SSRS will rerun missed subscriptions.- created a very simple report to output to a file (filename appended)
- set the subscription to run every minute
- waited for 3 runs
- stopped the SSRS service
- waited 3 minutes and started the service
- saw 3 new reports appear - all with the same createddate
- subscription carries on as configured once that has happened
this is a simple report using default SSRS settings:
- always run report with most recent data
- no caching of the report
- no render from snapshot
i am running through the SSRS config file to see if i can spot anything....
thanks
samuel
There are actually two separate processes at work here... When you schedule a report subscription in SSRS, it creates a job with a GUID name in SQL Server Agent. So in your test, even though SSRS is down for three minutes, SQL Server Agent is still firing off that job every minute against the ReportServer database.
The way SSRS works is when you run a report, the results are pushed into a temporary table in ReportServerTempDB and then that is what gets rendered (or delivered) by the web service. My guess is that even though SSRS is down, the results are still being queued up from the SQL Server Agent job and then SSRS sees those and renders them when it comes up again.
December 4, 2014 at 9:34 am
ah ha!!
so if we are to turn off the SSRS service we need to disable all SQL Agent SSRS jobs??
is there no way to do this through SSRS??
thanks
December 4, 2014 at 11:26 am
lilywhites (12/4/2014)
ah ha!!so if we are to turn off the SSRS service we need to disable all SQL Agent SSRS jobs??
is there no way to do this through SSRS??
thanks
I believe if you go into Reporting Services and click into Site Settings, then Schedules, you can select all schedules and click Pause. If I am not mistaken this will cause the corresponding jobs in SQL Server Agent to be disabled until you resume them.
Note that this will only work for shared schedules; not for reports that have their own specific schedule.
December 5, 2014 at 2:46 am
ok thanks....ill check it out and give it a test
most of our schedules are independent but that could be changed to make this easier
December 5, 2014 at 4:38 am
Copying the results of this into SSMS might be quicker (category_id 100 should be Report Server - check this first!):
select 'exec msdb.dbo.sp_update_job @job_id = ''' + cast(job_id as varchar(50)) + ''', @enabled = 0;'
from msdb.dbo.sysjobs
where category_id = 100
December 5, 2014 at 8:20 am
Gazareth (12/5/2014)
Copying the results of this into SSMS might be quicker (category_id 100 should be Report Server - check this first!):
select 'exec msdb.dbo.sp_update_job @job_id = ''' + cast(job_id as varchar(50)) + ''', @enabled = 0;'
from msdb.dbo.sysjobs
where category_id = 100
The thing to be careful about if you're going that route is that there might already be jobs that are disabled because they aren't wanted anymore. If you re-enable *all* of your SSRS jobs using the same method, you will inadvertently re-enable those as well.
For me, it's just cleaner to use the Reporting Services interface but YMMV.
There are ways via SQL script to tell what job goes with what report; but even that seems like a lot of extra work compared to clicking a box and pressing pause 😀
December 5, 2014 at 8:31 am
thanks....im hoping this wont be a regular thing....services was stopped due to a power down of the office building and SSRS was delayed until log shipping had caught up for 60 hours of missed transfers.
It was during this catchup that the reports were stored in temp.
Thanks for the options....i will come up with some sort of script to disable the jobs most likely....but store the results so i know what to turn back on.
thanks for all your help
December 5, 2014 at 9:40 am
cphite (12/5/2014)
Gazareth (12/5/2014)
Copying the results of this into SSMS might be quicker (category_id 100 should be Report Server - check this first!):
select 'exec msdb.dbo.sp_update_job @job_id = ''' + cast(job_id as varchar(50)) + ''', @enabled = 0;'
from msdb.dbo.sysjobs
where category_id = 100
The thing to be careful about if you're going that route is that there might already be jobs that are disabled because they aren't wanted anymore. If you re-enable *all* of your SSRS jobs using the same method, you will inadvertently re-enable those as well.
For me, it's just cleaner to use the Reporting Services interface but YMMV.
There are ways via SQL script to tell what job goes with what report; but even that seems like a lot of extra work compared to clicking a box and pressing pause 😀
Good point 🙂
December 5, 2014 at 9:52 am
can use this query to match jobs and schedules
select
c.name [SSRS Report Name],
sj.name [SQLAgent Job Name],
c.itemid [SSRS ReportID],
sj.enabled [SQLAgent Job Status],
rs.scheduleid [SSRS SubscriptionID]
from msdb..sysjobs sj
join reportserver..ReportSchedule rs
on sj.name = convert(varchar (40), rs.ScheduleID)
join reportserver..catalog c
on c.itemid = rs.ReportID
where c.Name like '%%'
order by c.Name asc
can always build an enable/disable script from it
i got over 150 reports so this is quicker than clicking in the long run!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply