Troubleshooting Reporting Services

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
    FilesMicrosoft SQL ServerInstanceNameLogFiles
    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 serverMSSQL.xReporting
      ServicesReport Serverbin
      folder

    • Report Server Web service – Web.config
      in the Microsoft SQL serverMSSQL.xReporting
      ServicesReport ServerReportServer
      folder

    • Report Manager – Web.config
      in the Microsoft SQL serverMSSQL.xReporting
      ServicesReport ServerReportManager
      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
    FilesMicrosoft SQL Server90Setup BootstrapLOG
    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

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles