Troubleshooting Reporting Services

Monday Oct 12th 2009 by Marcin Policht

Marcin Policht continues his discussion of implementing Reporting Services on SQL Server 2005 Express Edition. In this article, we will turn our attention to troubleshooting methods that can be employed to identify and resolve problems affecting reporting functionality and performance.

In recent installments of our series dedicated to the most prominent features of SQL Server 2005 Express Edition, we have been discussing its implementation of Reporting Services. So far, we have presented the majority of their characteristics and described steps involved in creating and rendering sample reports. In this article, we will turn our attention to troubleshooting methods that can be employed to identify and resolve problems affecting reporting functionality and performance.

Due to a wide range of services and software components that Reporting Services rely on, identifying the root cause of an issue (and providing a resolution) might be challenging. As usual, the choice of troubleshooting approach will depend primarily on circumstances and clues provided by interactive error messages and the content of logs or trace files, but in general, it is likely to fall into one of the following categories. (Note that this arrangement is fairly arbitrary, representing just one of several criteria that can be used to organize the subject of our discussion - since we could, for example, choose to take into consideration security, performance, and configuration as distinguishing factors):

  • evaluating overall health of the host operating system,
  • examining configuration and performance of Internet Information Services, ASP.NET, and Reporting Services Web Services,
  • verifying operability of SQL Server instance and Reporting Services database (this might include optimization of data sources that reports are based on),
  • correcting Report Server and Report Manager-specific misconfiguration or failure.

In order to properly investigate each of these options, it is important to be able to capture data that could assist in the troubleshooting processing. This is typically accomplished by leveraging the following sources:

  • Windows Event Logs - while you should be able to find some Reporting Services-related entries in the System log (since SQL Server Reporting Services functions as an operating system service), the majority of those of interest to us reside in the Application log. They can be identified based on their Source parameter listed as either Report Server Windows Service or Report Manager. Some of the more relevant events include Report Server and Report Manager startup and shutdown errors, configuration changes (for example, those reflecting modifications to RSReportServer.config file), security incidents (a potential denial of service attack) and activation (problems with encryption keys) incidents, as well as performance or logging issues (e.g. a failure to create a performance counter or write to a trace log). For the listing of all possible Application Log Reporting Services event IDs, refer to Reporting Services Errors and Events article in the SQL Server 2005 Books Online.
  • Reporting Services Trace Logs - if entries in the Application Event Log do not yield any conclusions, refer to these files for considerably higher levels of detail. Located in the Program Files\Microsoft SQL Server\InstanceName\LogFiles folder (where InstanceName represents the SQL Server 2005 Express Edition instance hosting Reporting Services database) and named according to the convention that incorporates their creation date and time, they contain data regarding Report Sever Windows service worker (ReportServerService_timestamp.log) and management (ReportServerService_main_timestamp.log) threads, as well as keep track of individual events triggered by its Web service (ReportServer_timestamp.log) and Report Manager (ReportServerWebApp_timestamp.log). Even though there is a built-in mechanism that facilitates control over their proliferation (forcing the creation of new logs at least once a day, limiting their growth to 32 MB, and automatically deleting those older than 14 days), with relatively active instances of Reporting Services you might encounter disk space issues. In order to avoid them, consider either adjusting such values to the maximum size of logs and their retention period or lowering the level of logging by modifying the value assigned to DefaultTraceSwitch parameter in the following configuration files used by individual Reporting Services components (where 0 disables tracing completely, 1 records exceptions and restarts, 2 keeps track of exceptions, restarts, and warnings, 3 includes additional status messages, and 4 designates verbose mode, intended strictly for temporary use during troubleshooting):
    • Report Server Windows service - ReportingServicesService.exe.config in the Microsoft SQL server\MSSQL.x\Reporting Services\Report Server\bin folder
    • Report Server Web service - Web.config in the Microsoft SQL server\MSSQL.x\Reporting Services\Report Server\ReportServer folder
    • Report Manager - Web.config in the Microsoft SQL server\MSSQL.x\Reporting Services\Report Server\ReportManager folder

    ...where MSSQL.x designates local SQL Server 2005 Express Edition Reporting Services instance. You also can adjust the value of Components setting of the RSTrace parameter, which determines individual components to be traced, along with their corresponding trace level (for more information on this subject, refer to Reporting Services Trace Logs article in the SQL Server 2005 Books Online).

  • Report Server Execution Log - each report execution is recorded in the dbo.ExecutionLog table in the Report Server database. Data stored there includes such information as the report identifier, name of a user who launched it, rendering format (such as HTML, PDF or Excel), parameters, start and end time, status (success or failure with corresponding error code), as well as performance related statistics (e.g. time of data retrieval, processing, and rendering or total byte and row counts). While Microsoft has provided sample Execution Log reports, their functionality is dependent on an Integration Services package not supported in SQL Server 2005 Express Edition, so if you want to view their content, you need to develop your own custom mechanism to present it in a friendly format. Keep in mind that logs are purged by default every 60 days, although this value can be modified using the Remove log entries older than this number of days textbox on the SQL Server Reporting Services Site Settings page. If desired, it is also possible to disable report execution logging by clearing the Enable report execution logging checkbox at the same location.
  • IIS Server Logs and Failed Request Tracing - while not specific to Reporting Services, entries recorded in logs generated by the Internet Information Services site hosting Reporting Services virtual directories could give an indication of connectivity or application pool-related errors. The configuration process differs depending on the version of IIS. In the case of IIS 5 and 6 (running on Windows XP or Windows Server 2003 operating systems), this is done via the Enable Logging checkbox on the Web Site tab of the Reporting Services site's Properties dialog box, where you can specify Active log format as well as General Properties (such as New Log Time Period or Log file directory) and Extended Properties (determining specifically what data you want to record). Starting with Vista and IIS 7, you first need to make the desired logging and tracing options available via the Turn Windows features on or off Control Panel applet (from there, navigate to the Health and Diagnostics subnode of the World Wide Web Services node). Once this step is completed, add Failed Request Tracing Rules on the Web site level within Internet Information Services (IIS) Manager.
  • Setup Logs - created during each invocation of the SQL Server setup program, are stored within the Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG directory structure. Summary.txt, residing in its root, provides a basic overview (name and version, installation results, and the name of a corresponding, product-specific log file) of each locally installed product of SQL Server 2005 Express Edition (such as support files, SQL Server Native Client, SQL Server Management Studio Express, or Reporting Services). In case of a failure, you can examine the referenced file (its name is in the form of SQLSetupxxxx_hostname_productname.log, where xxxx is a consecutive integer automatically assigned to each new installation, hostname represents the name of the local computer, and productname is the name of a product being installed, such as RS in case of Reporting Services).
  • Windows Performance Logs - when running into issues with responsiveness in the course of data retrieval as well as during report processing or rendering, you should examine the performance of individual components that Reporting Services relies on. These typically include operating system resources (such as memory or processor usage levels, disk paging, length of read and write queues), IIS and ASP.NET, as well as SQL Server and Reporting Services-specific counters. (Note, however, that delays might also result from poorly formed queries or misconfiguration on the database level, such as non-optimal indexing). Identifying the cause of such problems involves (depending on the operating system) use of System and Performance Monitors (or Task Manager in more obvious cases), counter and trace logs or data collector sets, as well as SQL Server-specific performance troubleshooting tools. (Keep in mind, that even though Profiler is not available in SQL Server 2005 Express Edition, there are some open source free alternatives).

In the next article of our series, we will examine some of the more common methods helpful in dealing with performance issues affecting SQL Server 2005 Express Edition-based Reporting Services.

» See All Articles by Columnist Marcin Policht

Mobile Site | Full Site