SSRS ReportServer Database Overview and Queries

By:   |   Comments (10)   |   Related: > Reporting Services Administration


The SQL Server Reporting Services (SSRS) ReportServer database created during a native SSRS installation is undocumented by Microsoft, but I want to query it.


This tip will explain common tables and joins as found in a default ReportServer database and some example scenarios where such queries could be helpful.

Querying the SSRS Catalog

The first useful table within ReportServer is dbo.Catalog.  This table contains 1 row for every object found on the SSRS site.  This includes a row for each folder, report, data source, image, and linked report.  The primary key and clustered index for the table is ItemID and it is a GUID.  The user columns are also a GUID and can be converted to a name by joining to the dbo.Users table.

Depending on the version of SSRS not all type values may be available.

  ItemID -- Unique Identifier
, [Path] --Path including object name
, [Name] --Just the objectd name
, ParentID --The ItemID of the folder in which it resides
, CASE [Type] --Type, an int which can be converted using this case statement.
    WHEN 1 THEN 'Folder'
    WHEN 2 THEN 'Report'
    WHEN 3 THEN 'File'
    WHEN 4 THEN 'Linked Report'
    WHEN 5 THEN 'Data Source'
    WHEN 6 THEN 'Report Model - Rare'
    WHEN 7 THEN 'Report Part - Rare'
    WHEN 8 THEN 'Shared Data Set - Rare'
    WHEN 9 THEN 'Image'
    ELSE CAST(Type as varchar(100))
  END AS TypeName
--, content
, LinkSourceID --If a linked report then this is the ItemID of the actual report.
, [Description] --This is the same information as can be found in the GUI
, [Hidden] --Is the object hidden on the screen or not
, CreatedBy.UserName CreatedBy
, CreationDate
, ModifiedBy.UserName ModifiedBy
, ModifiedDate
  ReportServer.dbo.[Catalog] CTG
  ReportServer.dbo.Users CreatedBy ON CTG.CreatedByID = CreatedBy.UserID
  ReportServer.dbo.Users ModifiedBy ON CTG.ModifiedByID = ModifiedBy.UserID;

Consider this instance of SSRS with one folder, one report, one linked report, and an image at the root.

The root folder contains one folder, one report, one linked report, and an image.

Within the folder is a single data source.

The contents of the folder from the root contains a data source object.

The output of the above query would contain a row for each item in the screenshots.  The row at the top with no ParentID is the root folder.  Notice that the root ItemID is the ParentID for each item stored at the root.  The one data source that lives in a folder outside the root has a ParentID value belonging to the source older.  Finally, the linked report is the only object with a LinkSourceID value and that that value is the ItemID of the report.

This query output shows how the ItemIDs are distributed as explained in the prior paragraph.

There is one additional column to cover within the catalog.  The column is called content.  This column is a binary value and contains the actual XML definition of the object where appropriate.  To view the actual XML rather than the binary value use this query.

This previous tip covers the content column in much more detail.

, CASE [Type]
    WHEN 2 THEN 'Report'
    WHEN 5 THEN 'Data Source'    
  END AS TypeName
, CAST(CAST(content AS varbinary(max)) AS xml)
, [Description]
FROM ReportServer.dbo.[Catalog] CTG
  [Type] IN (2, 5);

Continuing with the example from above, this is the expected output.  The blue text can be clicked and will open the XML in a new SSMS window.

This output looks similar to the previous result set except that there is a new column with blue hypertext that contains the definition of the object.

The Data Source object looks like this when expanded into its own window.

<DataSourceDefinition xmlns="">
  <ConnectString>Server=.;Initial Catalog=ReportServer</ConnectString>

Can one change these values, perhaps to move a report to a different folder or to hide/unhide it?  Technically, yes.  But that is not advised and would not be a supported operation.

Querying SSRS Report Execution Statistics

Every time an SSRS report executes a row is entered into the table dbo.ExecutionLog.  In modern versions of ReportServer the table is called dbo.ExecutionLogStorage and dbo.ExecutionLog is a view. 

SELECT * FROM dbo.ExecutionLog

The data returned by dbo.ExecutionLog can be difficult to understand so Microsoft did everyone a favor and started adding more views to make it even simpler to query.  They started with dbo.ExecutionLog2 and later dbo.ExecutionLog3.  Querying dbo.ExecutionLog3 shows many of the columns have been converted to human readable values.

  [ItemPath] --Path of the report
, [UserName]  --Username that executed the report
, [RequestType] --Usually Interactive (user on the scree) or Subscription
, [Format] --RPL is the screen, could also indicate Excel, PDF, etc
, [TimeStart]--Start time of report request
, [TimeEnd] --Completion time of report request
, [TimeDataRetrieval] --Time spent running queries to obtain results
, [TimeProcessing] --Time spent preparing data in SSRS. Usually sorting and grouping.
, [TimeRendering] --Time rendering to screen
, [Source] --Live = query, Session = refreshed without rerunning the query, Cache = report snapshot
, [Status] --Self explanatory
, [RowCount] --Row count returned by a query
, [Parameters]
FROM ReportServer.dbo.ExecutionLog3

Here is some sample output from dbo.ExecutionLog3 after each report from the prior demo was executed once on the screen and then exported to Excel and PDF respectively.

The output shows 4 report executions, 2 each for 2 reports.  Each execution was successful.

One of the most useful parts of this table is the parameters column.  This column is part of the execution log and records the parameters used when a report was executed.  When a user says that they ran a report and received unexpected results it can be hard to replicate without knowing exactly what they typed in.  With this column the process becomes a matter of fact rather than a matter of guessing.  Did the user type in something unexpected?  Perhaps an obvious typo like the wrong year on their date range?  Bingo.  There it is in black and white.

The sample catalog report has been modified to accept 2 parameters.  They aren’t used by the report, but they will still be captured by the execution log.  Consider this execution of the catalog report.

The screenshot shows the value "MyTextEntry" for "MyParameter" and "3/13/2019" for StartDate.

The execution log captures these values like so.

The query results show "MyParameter=MyTextEntry&StartDate=3%2F13%2F2019%2012%3A00%3A00%20AM" for the Parameters column.

Querying SSRS Subscriptions

Anyone that manages an SSRS box has noticed that each subscription creates a SQL Server Agent Job with a GUID as its name.  These might seem like they don’t have meaning, but they actually do.  There are 2 new tables that will help understand how subscriptions are stored in the ReportServer database.

The table dbo.ReportSchedule is a list of schedules with which one might place a subscription.  Another table, dbo.Subscriptions marries a report from dbo.Catalog to a schedule in dbo.ReportSchedule creating a report subscription.  It is the unique identifier of dbo.ReportSchedule that becomes the name of the SQL Server Agent Job.  Once joined to msdb.dbo.sysjobs the subscription can be followed for history like any other job.

Continuing with the prior demo database a single subscription was created against My Catalog Report.

The SSRS GUI shows a single subscription created against the report "My Catalog Report"

As expected, a single new SQL Server Agent Job appeared on the instance.

A screenshot of the SQL Server Agent job list in SSMS shows a job with a GUID for a name.

This query will join from the catalog through to the agent job in MSDB.

, s.[Description] SubScriptionDesc
, sj.[description] AgentJobDesc
, s.LastStatus
, s.DeliveryExtension
, s.[Parameters]
  ReportServer.dbo.[Catalog] ctg 
  ReportServer.dbo.Subscriptions s on s.Report_OID = ctg.ItemID
  ReportServer.dbo.ReportSchedule rs on rs.SubscriptionID = s.SubscriptionID
  msdb.dbo.sysjobs sj ON CAST(rs.ScheduleID AS sysname) =

For this example, the results of this query show that the inner join has matched the subscription to the agent job.

There is one row in the result set with values from both ReportServer and MSDB indicating that the inner join found a match in both databases.

Using this Data in Real Life Scenarios

This information may be quite interesting to someone that enjoys SQL Server trivia, but its usefulness may seem limited.  This section of the tip includes a few real-life scenarios where this data was used to solve a business problem and may serve as an example of how this information can be applied to the day-to-day workings of a SQL Server professional.

This author remembers a time when a report was running slowly for the end users.  He was asked to help clean up the query to make the report run faster.  Upon reviewing the execution log, it was determined that the query (TimeDataRetrieval) was finishing in a sub-second time frame.  The rendering time (TimeRendering), however, was many seconds long.  This pushed the onus back to the report developer and away from the DBA who would have otherwise wasted time trying to tune a well-tuned query.

Another real-world scenario is a time that many users were complaining about application performance.  The SQL Server was running with higher than normal resource usage.  The SSRS application was consistently showing up with a busy, active SPID.  Reviewing dbo.ExecutionLog3 showed a query running repeatedly with very long TimeDataRetieval.  Reviewing history for days prior showed that execution was considerably longer today than yesterday or prior days.  Reviewing dbo.Catalog showed the report had been modified that very morning along with the user that modified it. It was clear which report writer needed to be consulted at that time to rollback a change and review it before resubmitting.

A customer was getting ready to migrate to a new SQL Server and wanted to clean up older reports that weren’t being used any longer.  Each business unit manager was presented with a report showing each report in their respective libraries along with the execution count and most recent execution time.  They were able to remove many more reports from the library than would have been had they not started with such concrete evidence that many reports were idle.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Eric Blinn Eric Blinn is the Sr. Data Architect for Squire Patton Boggs. He is also a SQL author and PASS Local Group leader.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Friday, May 17, 2024 - 11:31:32 AM - Eric Blinn Back To Top (92248)
Hi Sonny,

The table is called favorites. It has 2 columns. One joins to catalog on itemid and the other joins to users on userid.

Friday, May 17, 2024 - 9:32:33 AM - Sonny Reyes Back To Top (92247)
Hi Eric.

Great article. I have a question. Where can I find the "Favorites" data? I need it for migration.

Friday, January 5, 2024 - 12:46:37 PM - Mary Opdahl Back To Top (91833)
Hi-I need help with an SSRS report. I can't find the dataset. I am trying to look into why it is not working. Please help! Thanks! Mary

Wednesday, March 10, 2021 - 8:50:08 AM - Michael Richard Edwards Back To Top (88372)
Thanks a lot Eric

Tuesday, March 9, 2021 - 4:00:10 PM - Eric Blinn Back To Top (88368)
Hi Michael,

I think your approach makes a ton of sense. If you look at the procedure you mention at the bottom of your comment it does basically the same thing as your update statement. I don't think you'll go wrong either way.


Tuesday, March 9, 2021 - 6:38:22 AM - Michael Richard Edwards Back To Top (88364)

We have many legacy reports that have not been executed in a long time. I can find these from a persisted version of the ExecutionLogStorage table (or the fact that they are not in there at all).

I have tested then renaming these with a TOBEDELETED_YYYYMMDD suffix, as simply as this:

SET name = name + '_TOBEDELETED_20210309' , path = Path +'_TOBEDELETED_20210309'
--- where ......

This appears to work completely fine. The reports appear in the SSRS web app with the new names and still run fine. The idea is that in 6 months time I will use a PowerShell script to permanently delete these reports.

Can anyone see any potential problems with this approach? Thanks!

PS. Have more recently spotted (using SQL Profiler when renaming a report from the front end) this proc: ReportServer..MoveObject:

EXECUTE @RC = [dbo].[MoveObject]

Any thoughts on using that instead?

Wednesday, May 15, 2019 - 10:08:28 AM - Eric Blinn Back To Top (80087)

My first thought is that there is some miscommunication between what is being clicked in the UI and what is being queried in the database.  Can you do a trace of the ReportServer database while someone clicks the tile in the UI to confirm which row they are reading?

Tuesday, May 14, 2019 - 7:18:31 PM - Vin Back To Top (80076)

Thank you for this post Eric. In our 2016 environment, the Content column ( XML value) does not match the UI. Any thougths on whats causing issue?

Thursday, April 11, 2019 - 4:44:05 PM - Eric Blinn Back To Top (79536)


Are you asking about the data set returned by a prior report execution?

Thursday, April 11, 2019 - 4:04:13 PM - Sureindran Nadesan Back To Top (79535)

Do you have a script that shows what's in the dataset?

get free sql tips
agree to terms