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.
» 
Comment and Contribute
