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 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:
- http://msdn2.microsoft.com/en-us/library/ms165307.aspx
Logging
- Report Server exposes a number of log files, these include:
- Information on report executions and whether they were successful and additional data related to the execution
- Detailed error stacks that show what the problems were
- 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:
Diagnosing processing and rendering problems – topic name “Processing Large Reports”
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
-
http://msdn2.microsoft.com/en-us/library/aa972240(SQL.80).aspx
- Monitoring Report Execution Performance with Execution Logs
- http://msdn2.microsoft.com/en-us/library/aa964131.aspx
Specific actions to help diagnose problems:
- Adjusting Memory limits – see section “Report Size in Memory”
- Adjusting concurrency for Scheduling:
- 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
- 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:
How to configure a scale-out deployment:
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
- http://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