Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

SQL Server Reporting Services (SSRS) Log Files for Troubleshooting


By:   |   Read Comments (1)   |   Related Tips: > Reporting Services Administration

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, http://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.

log file settings

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.


Log File Begin

Log File Part2

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. 

Clean Log

Alternately, you may actually see warning message in the log, as illustrated in the next screen print.

Warn Message

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.

Errror2


error1

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.

http trace

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.

http trace

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


Last Update:






About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Friday, September 26, 2014 - 9:39:22 AM - Greg Robidoux Back To Top

Hi Scott,

Congratulations on your 50th published tip on MSSQLTips.com.

Thanks for all of your contributions.

-Greg


Learn more about SQL Server tools