Share via

How to diagnose issues when running reports in the report server?

  • Article

A customer came to the SQL Server Customer lab to investigate a problem with one of the feature I’m responsible for – data-driven subscriptions. In looking at their solution, we discussed any number of problems they had encountered. I realized that it can be difficult to find out where to start looking when a problem occurs. This post will hopefully provide you a starting point as you endeavor to fix the issues you run into.

Reports can take up

- a lot of memory,

- a lot of time to execute,

- a lot of CPU

Generally speaking, it is possible for errors to occur as a result:

- Out of Memory

- Internal Errors

- Rendering errors

This begs the question, “How to diagnose issues when running reports in the report server?”

General process to follow:

  • For Report Execution problems, start with the report server execution log

    • This log will tell you which reports are failing, who ran them, what parameters they used

    • It will also provide the time at which it failed and the server in your deployment on which the report failed

    • You can use the time and server to find the actual stack trace in the trace log files

  • Reference the trace logs based on timestamps ranges you find in the event log

  • You can find information on some of the error codes here:

Logging

- Report Server exposes a number of log files, these include:

o Information on report executions and whether they were successful and additional data related to the execution

o Detailed error stacks that show what the problems were

o Major events that occurred on your report server that you should be aware of are in the application event log in windows

- You can read up on all of this here:

o https://msdn2.microsoft.com/en-us/library/ms157403.aspx

Diagnosing processing and rendering problems – topic name “Processing Large Reports”

- https://msdn2.microsoft.com/en-US/library/ms159638.aspx

Monitoring Performance

- You will want to look at things like memory consumptions, application domain recycles, cpu usage, etc. To isolate problems you may adjust concurrency for the scheduling service so you know exactly which report is currently running (instructions are below).

- Application Domain recycles indicate the report server is under memory pressure. We use them to clean out memory. If your interactive report execution failed suddenly, and you're monitoring the performance counters for application domain recycles, you may see a correlation. You should also see information in the trace log related to this.

- This topic describes the performance counters

o https://msdn2.microsoft.com/en-us/library/aa972240(SQL.80).aspx

- Monitoring Report Execution Performance with Execution Logs

o https://msdn2.microsoft.com/en-us/library/aa964131.aspx

Specific actions to help diagnose problems:

- Adjusting Memory limits – see section “Report Size in Memory”

o If you’re seeing out of memory exceptions you can try increasing the memory used by report server

o https://msdn2.microsoft.com/en-US/library/ms156002.aspx

- Adjusting concurrency for Scheduling:

o While trying to determine what is happening, you might reduce the number of simultaneous report executions

§ If you’re always running extremely large reports, setting this to 1 will allow you to use all of the memory for the report

o In the file rsreportserver.config:

§ <MaxQueueThreads>0</MaxQueueThreads> determines concurrency for scheduling and delivery

§ “0” means the report server will determine the right number

Performance Whitepaper:

- It is recommended to hosting report server and the report server database and data sources from which you get report data on different computers

- https://www.microsoft.com/technet/prodtechnol/sql/2005/pspsqlrs.mspx

How to configure a scale-out deployment:

- Scale outs can increase throughput, reliability, and concurrency

- https://msdn2.microsoft.com/en-us/library/ms159114.aspx

Monitoring and triggering subscriptions:

- Sometimes making the report run on a schedule can help you isolate the performance issues (interactive report execution can lead to excessive load on your server)

· See ‘how to trigger a subscription’; this works on SQL 2000 RS, and SQL 2005 RS

· See ‘how to monitor a subscription’

Monitoring interactive report executions:

- The ListJobs SOAP API allows you to see which long running reports are currently executing

- https://msdn2.microsoft.com/en-gb/library/aa225969(SQL.80).aspX

If all else fails:

  • Use SQL Profiler to monitor the actions the report server is taking in the report server database

  • this is useful if you think the report server is not doing anything - you can watch the actual queries run through and watch report server respond to your actions

  • generally speaking, it is possible to see everything in our various log files, so there should be no need to go to this level

If you have additional questions, feel free to leave me a comment or post a question on my blog:

- Lukasz’s Blog: https://blogs.msdn.com/lukaszp

Take care and good luck,

-Lukasz

Comments

  • Anonymous
    January 31, 2007
    Hoy en cosas interesantes: Microsoft SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint

  • Anonymous
    February 02, 2007
    Sorry for the poor Spanish, but I can't resist :-) Si, es mucho interesante.  Es disponible con SQL Server 2005 SP2.  Los CTP de SP2 son disponible presamente. -Lukasz

  • Anonymous
    March 29, 2007
    I was poking around some other SSRS blogs from folks here on the product team, and I found this post

  • Anonymous
    June 09, 2007
    Over the weekend I was snooping around some blogs as I usually do, and came across this interesting article

  • Anonymous
    February 10, 2008
    These are good links when getting error "Out of memory" when running report in Reporting Service 2005. http://msdn2.microsoft.com/en-us/library/ms156002.aspx http://blogs.msd ...

  • Anonymous
    July 08, 2008
    In my last post(http://weblogs.asp.net/akjoshi/archive/2008/06/06/Installing-and-configuring-sql-server-2005-reporting-services.aspx) Grant O pointed out that I missed the troubleshooting section and same feedback came from my manager too; So I decided

  • Anonymous
    November 11, 2008
    I was troubleshooting a report the other day and found a great post from Lukasz on troubleshooting SQL Server Reporting Services. Not to take anything away from him... I did not write the post below. I reposting it for my own reference...General process

  • Anonymous
    June 22, 2016
    Hi Lukasz,My .NET application uses SSRS web services to render reports. Intermittently the webmethod Invoke failes for 4-5 mins. I get the below error message on the Web application logs;"The request failed with HTTP status 401: Unauthorized."When I looked into the SSRS server trace logs nothing is been logged exactly between this 4-5 minutes.What could be the reason?Thanks,Anji

    • Anonymous
      June 24, 2016
      The comment has been removed