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 theirSource
parameter listed as eitherReport Server
or
Windows ServiceReport
. Some of the more relevant events include Report Server
Manager
and Report Manager startup and shutdown errors, configuration changes (for
example, those reflecting modifications toRSReportServer.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 theProgram
folder
FilesMicrosoft SQL ServerInstanceNameLogFiles
(whereInstanceName
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 toDefaultTraceSwitch
parameter in the following configuration files used by individual Reporting
Services components (where0
disables tracing completely,1
records exceptions and restarts,2
keeps track of exceptions, restarts, and warnings,3
includes additional status messages,
and4
designates verbose
mode, intended strictly for temporary use during troubleshooting): -
Report Server Windows service –
ReportingServicesService.exe.config
in theMicrosoft SQL serverMSSQL.xReporting
folder
ServicesReport Serverbin -
Report Server Web service –
Web.config
in theMicrosoft SQL serverMSSQL.xReporting
folder
ServicesReport ServerReportServer -
Report Manager –
Web.config
in theMicrosoft SQL serverMSSQL.xReporting
folder
ServicesReport ServerReportManager -
Report Server Execution Log – each report execution is recorded
in thedbo.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 asHTML
,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 theRemove log entries older than this number of days
textbox on theSQL Server Reporting
page. If desired, it is also possible to
Services Site Settings
disable report execution logging by clearing theEnable 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 theEnable Logging
checkbox on the Web Site tab of the Reporting Services site’sProperties
dialog box, where you can
specifyActive log format
as
well asGeneral Properties
(such asNew Log Time Period
orLog file directory
) andExtended 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 theTurn Windows features on or off
Control
Panel applet (from there, navigate to theHealth
subnode of the
and DiagnosticsWorld
node). Once this step is completed, add
Wide Web ServicesFailed Request Tracing Rules
on the Web
site level withinInternet Information
.
Services (IIS) Manager -
Setup Logs – created during each invocation of the SQL Server
setup program, are stored within theProgram
directory
FilesMicrosoft SQL Server90Setup BootstrapLOG
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 ofSQLSetupxxxx_hostname_productname.log
,
wherexxxx
is a
consecutive integer automatically assigned to each new installation,hostname
represents the name of the
local computer, andproductname
is the name of a product being installed, such asRS
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).
…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).
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.