Problem
Your SQL Server Report Server service will not start or you keep getting an error when you navigate to the SSRS URL? What logging tools are available to assist you with diagnosing SSRS problems, issues, or errors? Check out this tip to learn more.
Solution
Most SSRS report designers are well aware of the Execution Log tables which are available in the ReportServer database. If you are not, head over to this tip, https://www.mssqltips.com/sqlservertip/2722/sql-server-report-server-2008-r2-execution-log-reports/, which goes over how the Execution Log works. Although, that tip is for SSRS 2008R2, the bulk of the tip still applies to SQL Server and Reporting Services 2012.
However, if your Report Server will not start or you are seeing other errors on your SSRS website, there are additional tools available which could assist with troubleshooting the problem. In actuality, there are several different logs which are at our disposal when working with SSRS.
- First, is the Report Server Service Trace Log; this log contains a wealth of information which includes information posted by the report server, information posted from the SSRS web service, and details posted from the Report Manager. Additionally, the main log file can help track down specific errors which may be occurring and also notates unexpected errors experienced by Reporting Services.
- Second, the Report Server HTTP log can be used to trace all the http requests and responses made against your SSRS web service. This log is like a “log book” of your visitors. However, as compared to the trace log mentioned, above, it is not enabled by default. It must be enabled in the ReportingServicesService.exe.config file. We will talk about each of these two logs in the next example sets.
SQL Server Reporting Services Trace Log
As mentioned above, the Execution Log retains details about query and report execution in the ReportServer database and is probably your first troubleshooting stop when errors arise. The SSRS trace log is likely your second stop in your trouble shooting journey. The information in this log is extensive and maybe in some ways overwhelming. However, to assist in troubleshooting a problem, this level of detail is significant and necessary. If you have already reviewed the execution log and did not find what was causing an error, the level of detail in the trace log will be helpful in reviewing an issue.
Likely, the most common search you will do, at least initially, is for an error. This log is on by default and can be found in: C:\Program Files\Microsoft SQL Server\MSRSXX.SQL2012\Reporting Services\LogFiles or some variation depending on your SQL Server installation. The file name starts with “ReportServerService_ “and then is suffixed with the date and time and “.log”. You can, if you would like, change the default name for the trace file.
Furthermore the log file is in plain text, so it is readable with any text editor. You can actually set the trace level from 0 (tracing turned off) to 4 (full verbose messaging). In order to make these and other adjustments to the trace file, you need to modify the ReportingServicesService.exe.config file in the …\Microsoft SQL Server\MSRSXX.SQL2012\Reporting Services\ReportServer\bin (my directory is for example: C:\Program Files\Microsoft SQL Server\MSRS11.SQL2012\Reporting Services\ReportServer\bin). As shown below, you can adjust several of the settings in the ReportingServicesService.exe.config in order customize specific log file settings including the level of logging, the name of the log file, and the number of days to keep the log files.
Notice in the above screen print, that we can additionally adjust the file size. Microsoft does recommend not changing the following log settings: Prefix or TraceFileMode. Finally the last line in the screen print specifies not only the trace level (3 in the above example), but it also allows you to specify the component categories to be included in the trace. The default level for these options is: “all:3”.
The categories which can be specified include:
- RunningJobs – Traces all in progress reports or subscriptions
- SemanticQueryEngine – Records semantic queries used in adhoc queries
- SemanticModelGenerator – Records trace generation for semantic models
- All – Traces all activity for all process
Now that we have the trace setup, we can now review what is actually in the file. As shown in the below example, the beginning of the trace file shows a whole sundry of different values which are set when SSRS starts. We see location information, operating system information, and various startup settings including memory, secure connection defaults, and clean up recycle times. The setting list actually goes on and on, so we will not cover all the settings, but this screen print shows how we can check.
Often, as you review the log, you are going to see what I call a clean log, which appears similar to the below screen print. Note how the messages state they are “INFO” only or informational in nature.
Alternately, you may actually see warning message in the log, as illustrated in the next screen print.
Of course, these warning items should most often be looked into to determine their root cause, but they likely would not prevent the report server from starting.
The last main item to look for in the SSRS trace log pertains to actual errors. The below screen prints show two such error rows in the log files. The first error was actually caused by an out of memory situation which rendered the Report Server “out of commission”. The second error notes a failure to load data into the Execution Log.
As you can see the trace logs contain a wealth of information to troubleshoot issues within SSRS. Fortunately, we have a second set of logs which trace the http requests and responses to further assist with troubleshooting. We will discuss that log next.
SQL Server Reporting Services Report Server HTTP Log
The SSRS http log provides SSRS administrators with details pertaining to the requests initiated against the SSRS web service. This log is not on by default, but you can adjust the same ReportingServicesService.exe.config file used to adjust the setting for the SSRS trace log to turn on. We again can set the http log file name using the HttpTraceFileName setting while the HttpTraceSwitches setting defines which items are logged to the file. Included in the switch list are:
- Date and time
- ClientIP-address
- UserName
- ServerPort and Host
- Method, Protocol, and Protocol version
- Bytes received and time taken
- User Agent
- UriStem and UriQuery
- Cookies Received and Sent
- Referrer
Thus, as shown below, we add the HttpTraceFileName and HttpTraceSwitches to the RStrace section of the config file and finally we must add “http:4” to the component area of the config file.
Now that our http traces file is turned on, we can review the http trace log. If you have ever looked at IIS or other web server logs, these entries, shown below, should look familiar.
Again, these log items can assist you in trouble hooting user issues with their SSRS requests.
Conclusion
SSRS is setup and ready to help you troubleshoot issues with your SSRS site. In addition to the Execution Log, SSRS administrators can also review the SSRS trace log which is turned on by default. This log contains a wealth of information including exceptions that were generated, background processing review, and logs of operations completed by the Report Manager and web service. Most “clean logs” will contain many INFO messages, but reviewing the logs will also provide details about Warning and ERROR messages. In addition to the default trace log, the SSRS http trace log provides details about the SSRS web services receipt of requests and it’s subsequent response to those requests. Both of these logs allow for customizing specific settings via the ReportingServicesService.exe.config file.
Next Steps
- Turing on SSRS Logging when in SharePoint Integrated Mode- http://msdn.microsoft.com/en-us/library/ff487871(v=sql.110).aspx
- Errors and Event References for SSRS- http://msdn.microsoft.com/en-us/library/ms165307.aspx
- All SQL Server Reporting Services Tips
