Click here to monitor SSC
  • Av rating:
  • Total votes: 85
  • Total comments: 13
Feodor Georgiev

Administrating SQL Server Reporting Services - Planning, Documenting and Troubleshooting

22 November 2011

DBAs are usually charged with the administration of Reporting Services, but are often short on guidance on how to go about such tasks as planning, documenting and troubleshooting those aspects that are specific to the SSRS service. Feodor gives some tips, custom SSMS reports, and  useful TSQL queries,  based on his experience.

This article touches on three important aspects of the task of administrating SQL Server Reporting Services (SSRS): Planning, documenting and troubleshooting.

SSRS (SQL Server Reporting Services) is a part of the SQL Server package and it has been available since 2005. It includes a rich set of features which are used to consume data from various sources and transform it into web-based graphical representations that can be geared to the requirements of different users by representing data in a variety of formats.

SSRS is used for

  • The distribution of Business data
    • Many companies deliver data and aggregations via SSRS to their employees, so they can make decisions and analyze business situations. This type of reports can be viewed in a browser or the users can subscribe to reports and receive them by email.
  • Integration with applications
    • As SSRS provides a web service which can be used at application runtime to deliver the ‘freshest’ data to the user, it can be integrated within an application.
  • Ad-hoc reporting
    • Users can develop their own reports by using templates in Report Builder.

Since SSRS can unify the data from various data sources, it can be used as a ‘common place’ for different departments that may have their data in different formats or in different types of systems.

SSRS presents the developers with a variety of ways to develop and manage reports, flexible security and subscriptions. The Reporting Services module of SQL Server consists of two components: a report server and a report designer.

Planning and documenting your SSRS projects

If SSRS is used by any data-dependent organization, it must be documented. Proper documentation can not only contribute to the fast recovery in the case of a disaster, but it can also point out potential design, configuration and performance improvements. I highly recommend documenting not only the infrastructural design and configuration, but also the security, user objects and the performance of the SSRS environment.

There is a tremendous difference between the 2005 and 2008 versions of SSRS, which makes the documentation different for these versions.

  • Dependencies
    • Starting with SSRS2008, the architecture is not completely dependent on IIS anymore.
  • New memory management
    • The report processing uses a file system cache to adapt to memory pressure and the administrator of the SSRS is able to set min and max memory usage targets.
    • In SSRS2005 the reports were memory bound, i.e. a request for a large dataset could cause ‘out of memory exception’ or fail other reports; in SSRS2008 when a report is run, only the first page is rendered and expressions are not rendered until the page which contains them is requested
  • GUI
    • SSRS2008 has its own standalone Report Builder

There are several parts of SSRS which should be documented:

  • Installation
  • Configuration – directories, security
  • User objects: data sources, reports, schedules
  • Performance and usage of the reports

In the following sections we will go into detail in each documentation aspect.


SSRS installs as part of SQL Server installation. At that point, it is important to consider and to document the topology and the installation settings. This documentation for the setup and installation of SSRS can well be split into three main topics:

  • the topology the single deployment or scale-out deployment (There is also a Sharepoint integration mode, but this is beyond the scope of this article))
  • the resources (design for performance from the start and not post-factum),
  • the security (password encrypted keys protect the data between the Report Server and the Report databases).

In general terms, there are three key players in a SSRS deployment: the Client, the Report Server and the Report Server Database. We can have many variations in topology, depending on the distribution of these components,

The main considerations for each key player are, of course, performance, scalability and failure tolerance.

In a Single deployment we have a single reporting server, which hosts both the Reporting Service and the Reporting Database. This setup is perfect for development or for evaluation, and even for small environments where the requests sent to the server are not unmanageable.

In the case where the performance is a high priority, we would most likely use the Standard layout of the environment where the Reporting Server and the Report Databases are each on different servers. This is intended to avoid the competition for resources between the workloads carried out by the Reporting Service and the Report Database.

The Report Database is usually very IO intensive (which also affects CPU) and memory demanding (especially in SSRS2005), while the Reporting Service is memory and CPU intensive. This is why it is a good idea to use different servers for each module in larger environments. This setup is called Standard Deployment.

In the case where the performance, scalability and fail tolerance are all important, we must use Scale-out Server deployment. This means that we have a client which sends a request to a set of load-balanced Report Servers, which in turn connect to a database server which hosts the Report Server databases.

Furthermore, to guarantee a full fail tolerance in our environment, we could decide to host our Report Server databases on a clustered SQL Server environment.

Regardless of the topology, it is always a good idea to use your favorite tool to document the setup and planning of the environment during setup. It is a good idea to use a charting tool to produce a drawing of the topology, with machine names, IP addresses, network connection speeds etc. In case of failure we can easily understand what caused it and in case we need more resources we would know where to add them; also, in case of taking on new team members it will be easier for them to get introduced to the system.

Encryption keys

All data is encrypted between the Reporting Services database and the Reporting Services Service. By ‘all data’ I mean connection strings, user names and passwords, connection strings for the SSRS databases, the service account information. Therefore, it is very important to back up the keys for the SSRS installation.

Should a disaster occur, the keys are required in order to connect to the SSRS databases after re-installation.

There are two ways to administer the encryption keys in an SSRS installation: either through the UI of the Reporting Services Configuration Manager or by using the command line utility RSKeyMgmt.exe which ships with SQL Server Reporting Services.

Again, the most important part to remember is to back up the keys in a safe place with a strong password and to document the procedure before a disaster happens.


The configuration files for the SSRS

There are several configuration files which come with the 2008 version of SSRS:


It contains ReportManager and ReportServer web settings. This is the main configuration file, and some of its contents are exposed through the UI of the Reporting Services Configuration Manager and through the UI of the Properties Tab in the SSMS of the Report Server.


It contains policy settings for the Report Server Web Service.


It contains policies for the Report Manager web application.


It contains ASP.NET settings for the Report Manager and the Report Server.


It contains trace and logging settings for the Report Server Service.


It contains settings for the Report Designer application.


It contains security policies for the server extensions.

What about the config files for 2005?

The policy settings of SSRS2005 are located in RSWebApplication.config. SSRS2008 does not use this file, but saves the configurations instead in RSSrvPolicy.config and RSMgrPolicy.config.

As usual, it is important to document all the changes to the config files, and to backup all the config files to a secure location on a separate server or in a file repository.

Here are the locations of the config files in SSRS 2008:


%Program Files%\Microsoft SQL Server\MSRS10.<instance name>\Reporting Services\ReportServer\bin


%Program Files%\Microsoft SQL Server\MSRS10.<instance name>\Reporting Services\ReportManager


%Program Files%\Microsoft SQL Server\MSRS10.<instance name>\Reporting Services\ReportServer


%Program Files%\Microsoft SQL Server\MSRS10.<instance name>\Reporting Services\ReportServer


%Program Files%\Microsoft SQL Server\MSRS10.<instance name>\Reporting Services\ReportManager


%Program Files%\Microsoft SQL Server\MSRS10.<instance name>\Reporting Services\ReportManager


%Program Files%\Microsoft SQL Server\MSRS10.<instance name>\Reporting Services\ReportServer

Also, here is an example of a T-SQL script which will give you the opportunity to read and parse the config files.

Keep in mind that this is just a sample script and you will have to change the path to the config file of interest and also you will need to write your parsing command as shown below (by using the OPENXML command).

Writing a set of scripts for parsing all config files and options is beyond the scope of this article, but I hope this script will give you some ideas and a good starting point.

@x INT
@FileContents VARCHAR(MAX)
DECLARE @xmlHandle INT
-- used later on for parsing

IDENTITY(1, 1) ,

SET @FileName = 'C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\rsreportserver.config'
SET @ExecCmd = 'type ' + '"' + @FileName + '"'
SET @FileContents = ''

EXEC MASTER.dbo.xp_cmdshell @ExecCmd

FROM    #configXML

SET @x = 0
WHILE @x <> @y - 1
@x = @x + 1
SELECT  @FileContents = @FileContents + [XMLValue]
FROM    #configXML
WHERE   PK = @x

-- display the file contents
SELECT  @FileContents AS FileContents

-- Parsing the config file XML

EXEC sp_xml_preparedocument @xmlHandle OUTPUT, @FileContents
FROM    OPENXML (@xmlHandle, '//Service', 2) WITH
IsSchedulingService VARCHAR(255) 'IsSchedulingService',
IsNotificationService VARCHAR(255) 'IsNotificationService',
PollingInterval VARCHAR(255) 'PollingInterval',
WindowsServiceUseFileShareStorage VARCHAR(255) 'WindowsServiceUseFileShareStorage'
EXEC sp_xml_removedocument @xmlHandle

There is also an object in the ReportServer database which gives information about the configuration.

You may execute it by running exec dbo.GetAllConfigurationInfo in the context of the ReportServer database.

Configuration – directories, security

Here is one of the main differences between SSRS2005 and SSRS2008 and up. SSRS2005 relies on an IIS server to manage the security, whereas later versions have their own directory and security handling.

Backing up the ReportServer database and the master key, as well as the above mentioned config files should be sufficient in order to recover from a disaster; however, do not forget to document the topology and the purpose of each server and each SSRS instance.

Here is a script which you can run to document the directories and the permissions for them in your ReportServer instance:

-- documenting the folders and the permissions
1 THEN 'Folder'
ELSE 'Report'
END AS [Type] ,
RSCatalog.Path ,
RSCatalog.Name AS Report ,
Users.UserName ,
FROM    [dbo].[Catalog] RSCatalog
INNER JOIN [dbo].[PolicyUserRole] PolicyUserRole ON RSCatalog.PolicyID = PolicyUserRole.PolicyID
INNER JOIN [dbo].[Roles] Roles ON PolicyUserRole.RoleID = Roles.RoleID
INNER JOIN [dbo].[Users] Users ON PolicyUserRole.UserID = Users.UserID
WHERE   RSCatalog.TYPE IN ( 1, 2 )
ORDER BY RSCatalog.Path ,
RSCatalog.Name ,

In SSRS 2008 we can run the following simple query to find out what user-defined objects reside in our Reporting Server instance:

FROM [dbo].[Catalog]

The Type column contains the following items:

  1. Folder
  2. Report (.rdl)
  3. .XML
  4. Link Report
  5. Data Source (.rds)
  6. model

In SSRS2008R2 there were 2 more types added:

  1. Shared Dataset
  2. Report Part

User objects: data sources, reports, schedules

The Reporting Services are based on two databases hosted on a SQL Server instance: Report Server database and Report Server Temp Database.

Generally, each instance of SQL Server can have Reporting Services installed (hence, there can be multiple instances of Reporting Services on the same machine), and it is up to the designer of the environment to decide what the names of the underlying databases will be.

The Report Server database contains all user generated objects (report definitions, data sources with encrypted credentials etc.), some metadata about the execution and performance of the reports, snapshots and some query results, schedules.

The Report Server Temp Database contains session and execution data, as well as cached reports.

Having said this, there are plenty of considerations for performance of the Report Server databases – the considerations are not too far behind from the general performance considerations of any other database (keep enough files, but not too many; use disk spindles and IO performance in a smart way; backup often, even the Temp database).

Here is a query which can help you look through the user created objects in our ReportServer. Just replace the Type in the search predicate with the type id of the object you are interested in ( look in the list of types above).

[Path] ,
[Name] ,
FROM    [dbo].[Catalog]
WHERE   [Type] = 2

Performance and usage of the reports

I’ve already mentioned that the Report Server database contains metadata about execution and performance of the reports.

Here is a query which gives you some performance statistics about your ReportServer instance:

-- report execution statistics and performance
ExecutionLog.Status ,
RSCatalog.Path ,
RSCatalog.Name AS Report ,
ExecutionLog.UserName ,
ExecutionLog.Format ,
FROM    [dbo].[ExecutionLog] ExecutionLog
INNER JOIN [dbo].[Catalog] RSCatalog ON ExecutionLog.ReportID = RSCatalog.ItemID
ORDER BY ExecutionLog.TimeStart DESC

And here is a useful query which allows you to look for some text in the report definitions – the ones that use ad-hoc queries. Just make sure you replace the text in the last line of the query with the text you are looking for.

Note that the definition of the ad-hoc query is stored in the Catalog table as a image datatype (in the [Content] column), so you have to convert it into something more appropriate – VARBINARY(MAX) – and then cast it as an XML, which you can parse and then query.

SELECT  c.Path ,
c.Name ,
DataSetXML.value('@Name', 'varchar(MAX)') DataSourceName ,
DataSetXML.value('REP:Query[1]/REP:CommandText[1]', 'varchar(MAX)') CommandText
FROM    ( SELECT    ItemID ,
FROM      [ReportServer].[dbo].[Catalog]
WHERE     TYPE = 2
) ReportXML
CROSS APPLY ReportXML.nodes('//REP:DataSet') DataSetXML ( DataSetXML )
INNER JOIN [dbo].[Catalog] c ON ReportXML.ItemID = c.ItemID
-- Search by part of the query text
WHERE   ( DataSetXML.value('REP:Query[1]/REP:CommandText[1]', 'varchar(MAX)') ) LIKE '% Enter object name here %'

So far we have covered SSRS installation, design and configuration and I also mentioned some possible ways to work with metadata about user objects.

Now I would like to go into the troubleshooting of performance issues with user objects, and specifically the methodology of approaching problems with Reporting Services.


There are several places where we can get information about SSRS errors when we need to troubleshoot problems.

One place to start is the Report Server Service Trace logs.

First, I have to mention that there were a few different log files in SSRS2005, but in SSRS2008 onwards all log entries were consolidated into one text file.

In SSRS2005 the following files were used:

  • ReportServerService_<timestamp>.log,
  • ReportServerWebApp_<timestamp>.log,
  • ReportServer_<timestamp>.log,
  • ReportServerService_main_<timestamp>.log.

In SSRS2008 and later all trace log data was merged into one file:

  • ReportServerService_<timestamp>.log.

The file / files can be found in the \Microsoft SQL Server\<SQL Server Instance>\Reporting Services\LogFiles folder.

As mentioned above, the config file for the Report Server Service Trace files is located in: \Program Files\Microsoft SQL Server\MSRS10.<instance name>\Reporting Services\ReportServer\bin foler, in the ReportingServicesService.exe.config.

There is no UI available for changing the configuration, but if some changes are needed, then a simple text editor will do the job.

The Trace logs roll over a period of 14 days, but this setting can be easily changed in the config file, as well as file size limits, tracing levels etc. For more information, look in BOL.

In the Trace files we find information about the Server, OS version, memory and number of processors; also SSRS version, events logged in the App log, exceptions thrown, low resource warnings, HTTP header, stack trace, debug trace and information about SOAP envelopes sent and received.

There is plenty of information available, which makes the Trace Logs file / files a good place to start debugging.

I would suggest that you enable the Remote Errors, so you can capture information about errors happening on remote servers. Here is a great article on how to do this. ( Also, it is a good idea to look into the Report Server Excecution logs. This is a broad topic, but here is a link which will get you started (

Keep in mind also that SSRS ships with a set of Performance counters in Perfmon in the MSRS Web Service and MSRS Windows Service category.

The detailed explanation of the counter sets is not in the scope of this article, but it is a good idea for the Report Server administrator to be familiar with them.

Some more considerations:

  • Always be on the watch for the CPU and Memory usage of the reports and the Report Server itself.
  • Always look at the IO as the biggest bottleneck (unless you run on SSDs, but even then you should be on the watch for adding good indexes).
  • Keep an eye on the concurrency of scheduling. In the RSReportServer.config file there is a MaxQueueThreads parameter which defines the concurrency for scheduling and delivery. 0 means that the server will determine the best value.
  • If you still need to get information about the performance of the queries going through the Report Server databases, don’t forget the Profiler.
  • I highly recommend enabling the Report Server HTTP log in SSRS2008 and up. This is a great feature which logs all HTTP request and responses which are handled by the Report Server. The feature is available only in SSRS2008 and up, and it is not enabled by default. To enable this feature, you have to look into the ReportServerService.config file.


In this article we started by exploring the purpose and structure of SQL Server Reporting Services, then we moved on to the configuration possibilities, then we looked at the inner workings of the user objects and their performance. And towards the end of this article we went over some important performance troubleshooting considerations.

I hope that your work as a SSRS administrator has just become a bit easier after reading this article. The scripts provided in the article should help you document and troubleshoot your system.

Recently I developed a set of reports which are to be rendered in SSMS as Custom reports towards the Report Server database. The reports give configuration, security and performance information. Of course, they can be used for overviewing, troubleshooting and documenting the system since they can be executed on demand or even saved and reviewed at a later time.

Here is a link to the download page of my SSRS Dashboard Reports: SSRS Performance Dashboard.

Feodor Georgiev

Author profile:

Feodor has been working with SQL Server since 2002, starting on the 2000 version and mixing it up as newer versions - 2005, 2008 and 2012 - were released. He specializes in database performance tuning, documentation and scalability management. He also works as project leader and mentor on SQL Server and Business Intelligence projects on Microsoft-based solutions. HIs specialties include: Database Architecture, Microsoft SQL Server Data Platform, Data Model Design, Database Design, Integration Solutions, Business Intelligence, Reporting, Performance Optimization, Big Data. When he is not busy with his DBA work, keeping up with the latest SQL Server tricks or sharing tips on forums, he writes articles on

Search for other articles by Feodor Georgiev

Rate this article:   Avg rating: from a total of 85 votes.





Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.

Subject: SSRS Documentation
Posted by: Ivomuk (view profile)
Posted on: Wednesday, November 23, 2011 at 1:52 AM
Message: There are two things i would like to comment about in the Administrating SQL Server Reporting Services - Planning, Documenting and Troubleshooting article.

1. There is a section "Regardless of the topology, it is always a good idea to use your favorite tool to document the setup and planning of the environment during setup. It is a good idea to use a charting tool to produce"...........i was wondering, wouldn't be a good idea to have mentioned the tools you were referring to.Even though the article is about SSRS it would help me improve my documentation of SSRS if i am using say word for my documentation.

2. Its a very good article since its a topic that is not covered in many articles about SSRS. The section of "Encryption keys" is of particular interest to me since its something i don't do.

Subject: SSRS Documentation
Posted by: sibir1us (view profile)
Posted on: Wednesday, November 23, 2011 at 2:14 AM
Message: @Ivomuk:
1. You are right: there are various tools which can be used for documenting the SSRS. The tools range from notepad and MS Word, to TFS source control.
What I would recommend as a first step, though, is to use my SSRS Performance Dashboard reports and save the reports from SSMS to a folder, together with config files and the topology diagrams (probably created in Visio or even XMind(
2. Yes, encryption keys are important, since they are used to maintain the connection between the Report Server service and the Report Database. Without restoring the keys after a failure, you will not be able to access the Report Server database.


Subject: SSRS
Posted by: Ivomuk (view profile)
Posted on: Wednesday, November 23, 2011 at 3:08 AM
Message: Thanks

Subject: Rodney P. Eady
Posted by: Rodney P. Eady (not signed in)
Posted on: Sunday, November 27, 2011 at 4:46 PM
Message: I wanted to thank you yet again for this amazing web-site you have designed here. It’s full of ideas for those who are definitely interested in this subject, especially this very post. You’re really all absolutely sweet and thoughtful of others plus reading your blog posts is a superb delight if you ask me. And what a generous gift! Ben and I are going to have fun making use of your points in what we should instead do next week. Our list is a mile long and tips might be put to good use.

Subject: SQL Server R2 2008 reporting...
Posted by: Anonymous (not signed in)
Posted on: Sunday, November 27, 2011 at 11:28 PM
I want a help,

I wish all the steps necessary to ensure that each employee receives and / or print a pay slip from its e-mail.
How to send their pay slip in their mailbox?
How they will receive, view and print their pay bullet by using SSRS or other tools? My interface is in C#.Net 2010.


Subject: Rodney P. Eady
Posted by: Feodor (not signed in)
Posted on: Sunday, November 27, 2011 at 11:54 PM
Message: @Rodney P. Eady: Thank you for the compliment to my article and to the site.

Have fun with SSRS!


Subject: SQL Server R2 2008 reporting...
Posted by: Feodor (not signed in)
Posted on: Sunday, November 27, 2011 at 11:57 PM
Message: @Anonymous: This sounds like a very general problem description, and it sounds like a request for a fundamental decision of what technology to use and how to implement it.
Since this article is focused on the inner workings of the SSRS and not so much on programming and implementation of single projects, I would recommend that you turn to one of the many specialized SQL Server or BI forums.


Posted by: Daniel Cohen - Guardian DBA (not signed in)
Posted on: Thursday, December 1, 2011 at 1:03 AM
Message: perfactly & good summarized article, making RS issues clear. Thank's

Posted by: sibir1us (view profile)
Posted on: Thursday, December 1, 2011 at 2:10 AM
Message: @Daniel Cohen: Thanks for reading and for the feedback!

Subject: datasource as a variable
Posted by: DG_Valero (view profile)
Posted on: Tuesday, January 24, 2012 at 1:55 PM
Message: Is there a way using 2008 to first select the datasource and after that the rest of the selection criteria? I have several reports for HP (aka Mercury) Quality Center. The projects (ie databases) are the same but I have different test data in each. I have several reports duplicated and would like to have one of each report but go after a different DB.

Nice job on all the info btw. Just found this site today. Thank you...

Subject: datasource as a variable
Posted by: DG_Valero (view profile)
Posted on: Tuesday, January 24, 2012 at 2:22 PM
Message: Is there a way using 2008 to first select the datasource and after that the rest of the selection criteria? I have several reports for HP (aka Mercury) Quality Center. The projects (ie databases) are the same but I have different test data in each. I have several reports duplicated and would like to have one of each report but go after a different DB.

Nice job on all the info btw. Just found this site today. Thank you...

Subject: Encryption Keys
Posted by: jvkondapalli (view profile)
Posted on: Sunday, March 18, 2012 at 9:24 PM
Message: I know this is really old article.

I found this article very informative on superficial level. I was wondering, if anyone can route me to a good encryption keys article. This is something which we don't do in our environment.
your help is much appreciated.


Subject: Informative
Posted by: Mxaza (view profile)
Posted on: Thursday, October 18, 2012 at 8:21 AM
Message: I have for sometime ignored SQL Server Reporting Services, but this article and others here have sparked interest in me to go deeper into this.

Thank you

Simple-Talk Database Delivery

Patterns & Practices Library

Visit our patterns and practices library to learn more about database lifecycle management.

Find out how to automate the process of building, testing and deploying your database changes to reduce risk and make rapid releases possible.

Get started

Phil Factor
How to Build and Deploy a Database from Object-Level Source in a VCS

It is easy for someone who is developing a database to shrug and say 'if only my budget would extend to buying fancy... Read more...

 View the blog

Top Rated

Clone, Sweet Clone: Database Provisioning Made Easy?
 One of the difficulties of designing a completely different type of development tool such as SQL Clone... Read more...

Database Lifecycle Management: Deployment and Release
 So often, the unexpected delays in delivering database code are more likely to happen after the... Read more...

The PoSh DBA: Assigning Data to Variables Via PowerShell Common Parameters
 Sometimes, it is the small improvements in a language that can make a real difference. PowerShell is... Read more...

Issue Tracking for Databases
 Any database development project will be hard to manage without a system for reporting bugs in the... Read more...

Releasing Databases in VSTS with Redgate SQL CI and Octopus Deploy
 You can still do Database Lifecycle Management (DLM) workflows in the hosted version of Team foundation... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

Temporary Tables in SQL Server
 Temporary tables are used by every DB developer, but they're not likely to be too adventurous with... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.