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
Sourceparameter listed as either
Report Server Windows Serviceor
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.configfile), 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\LogFilesfolder (where
InstanceNamerepresents 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
DefaultTraceSwitchparameter in the following configuration files used by individual Reporting Services components (where
0disables tracing completely,
1records exceptions and restarts,
2keeps track of exceptions, restarts, and warnings,
3includes additional status messages, and
4designates verbose mode, intended strictly for temporary use during troubleshooting):
Report Server Windows service -
Microsoft SQL server\MSSQL.x\Reporting Services\Report Server\binfolder
Report Server Web service -
Microsoft SQL server\MSSQL.x\Reporting Services\Report Server\ReportServerfolder
Report Manager -
Microsoft SQL server\MSSQL.x\Reporting Services\Report Server\ReportManagerfolder
Report Server Execution Log - each report execution is recorded
dbo.ExecutionLogtable 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
Remove log entries older than this number of daystextbox on the
SQL Server Reporting Services Site Settingspage. If desired, it is also possible to disable report execution logging by clearing the
Enable report execution loggingcheckbox 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 Loggingcheckbox on the Web Site tab of the Reporting Services site's
Propertiesdialog box, where you can specify
Active log formatas well as
General Properties(such as
New Log Time Periodor
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 offControl Panel applet (from there, navigate to the
Health and Diagnosticssubnode of the
World Wide Web Servicesnode). Once this step is completed, add
Failed Request Tracing Ruleson 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\LOGdirectory 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
xxxxis a consecutive integer automatically assigned to each new installation,
hostnamerepresents the name of the local computer, and
productnameis the name of a product being installed, such as
RSin 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).
designates local SQL Server 2005 Express Edition Reporting Services instance.
You also can adjust the value of
setting of the
parameter, which determines individual components to be traced, along with
their corresponding trace level (for more information on this subject, refer to
Services Trace Logs article in the SQL Server 2005 Books Online).
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.