March 22, 2017 at 4:40 am
required ur help in one the issue I m facing.
We have an environment with active passive cluster with 4 sql reporting servers. there is process which generate reports for 1000 branches there is a procedure which runs and generate report (pdf format) on one of the report server. Issue in this is it generates the report for around 80 branches and then halt for 10 to 15 mins then it start the process again and then again it generates some report and the other halts for 10 to 15 mins it the reports gets generated for 1000 branches. No memory pressure or disk issue I have run the scenario with 100 branches but the issues was the same.so will like to know where shud I diggin.when it gets halt no active sessions are found.
March 22, 2017 at 7:58 am
samsql - Wednesday, March 22, 2017 4:40 AMrequired ur help in one the issue I m facing.
We have an environment with active passive cluster with 4 sql reporting servers. there is process which generate reports for 1000 branches there is a procedure which runs and generate report (pdf format) on one of the report server. Issue in this is it generates the report for around 80 branches and then halt for 10 to 15 mins then it start the process again and then again it generates some report and the other halts for 10 to 15 mins it the reports gets generated for 1000 branches. No memory pressure or disk issue I have run the scenario with 100 branches but the issues was the same.so will like to know where shud I diggin.when it gets halt no active sessions are found.
Probably a good place to start digging in would be the ReportServerService log file. Look in your Reporting Services\LogFiles directory.
You could check the execution log view (dbo.ExecutionLog3) but it likely has nothing to tell you what is happening during the "pause" time for the report generation. However, it may help you track down the exact times of when the report processing seems to stop for awhile. If you order it by execution times, you should see your gaps. And you could just track the last reports executed to see if there is any pattern to that.
Have you also checked whatever process it is that is generating the reports? Is there any logging with that process?
Sue
March 22, 2017 at 1:52 pm
I am assuming this is 1000 SSRS subscriptions.
On top of what Sue suggested, I would check for database growth of both tempdb and your report server database and log files on both of those.
You could also check your SSRS server agent for the jobs and ensure that they are all set to trigger at the same time and not send out in batches of 80 as you are noticing.
When it gets stuck, you say you saw no memory pressure or disk issues and you find no active sessions - is that on both your SSRS server AND your data server (data warehouse for example)? It could be the stalling is not related to SSRS but is on the SQL Server that is holding the data for the SSRS reports. Or, if it is on the data server you looked, it could be the SSRS server is out of memory. SSRS runs in different memory space than SQL Server.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 22, 2017 at 7:42 pm
No there is nothing we have 4 report server as well even if have checked the logs on reporting server.but I not able to track why there are no session of reports when searching on database server.
March 23, 2017 at 7:26 am
samsql - Wednesday, March 22, 2017 7:42 PMNo there is nothing we have 4 report server as well even if have checked the logs on reporting server.but I not able to track why there are no session of reports when searching on database server.
The only way I know of being able to disable the tracing is in the ReportingServicesService.exe.config file. And that one has to exist.
For 2012, the default directory would be: \Program Files\Microsoft SQL Server\MSRS11.<instance name>\Reporting Services\ReportServer\bin
Open the file in notepad and check the <system.diagnostics> section. There will be a value for "DefaultTraceSwitch". If that's set to 0 then it's disabled. The default is 3. I'd check to see if it's disabled. If it is disabled, you'd want to find out why it was disabled before making any changes. Could be there were issues before scaling out, not sure.
Sue
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply