Reporting Services Meta-data scripts

From SQLServerPedia

Jump to: navigation, search

See Also: Main_Page - Transact SQL Code Library

Contents

Introduction

Every installation of Reporting Services in SQL Server uses 2 databases namely ReportServer and ReportServerTempdb. ReportServer is mainly used to store meta-data about the objects and thier object definitions and ReportServerTempdb stores the transient session, execution data apart from the internal worktables created by the Report Server.

What's in the ReportServer database

ReportServer stores lot of meta-data about the reports and its characteristics, thier subscriptions and the schedule definitions, data sources, different users and roles, security and execution log data. Below is a list of all the tables that are available in the ReportServer database.

List of tables in ReportServer datbase
ConfigurationInfo: Stores Reporting instance level configuration data.
Catalog: One of the key tables that store the Reports and its characteristics.
UpgradeInfo:
ModelDrill:
ModelPerspective:
CachePolicy: Stores caching information for reports.
Users: List of all users accessing the Reporting site.
DataSource: Relationship between data source and the reports.
Policies:
ModelItemPolicy:
SecData: Security data.
Roles: List of roles in the Reporting Server.
PolicyUserRole:
Event:
ExecutionLog: One of the key tables to look for performance and execution metrics.
Subscriptions: Lists the subscriptions and whom to send, last execution status etc.
ActiveSubscriptions:
SnapshotData:
ChunkData:
Notifications:
Batch:
Schedule: Schedule information and its key characteristics.
ReportSchedule: Mapping between schedules, reports and subscriptions.
RunningJobs: Actively current running jobs.
ServerParametersInstance:
Keys: Important key information regarding the Reporting instance.
History:

The following images will show the relationships between the above tables. It will help in understanding the relationships better.

What's in the ReportServerTempdb database

ReportServerTempdb acts like a scratchpad work area for the ReportServer database and stores transient work tables and execution cache data. Some of the tables in the ReportServerTempdb are listed below.

List of tables in ReportServer datbase
SnapshotData:
ChunkData:
PersistedStream:
SessionLock:
SessionData:
ExecutionCache:

Example Scripts

Related Reading About Reporting Services

Author Credits

Sankar Reddy

This wiki article is created by Sankar Reddy. Sankar is a Database Engineer and an involuntary DBA working for Funmobility, a mobile media and entertainment company where he works with high traffic OLTP systems and over 1 TB data warehouse. Sankar strongly believes in 'Every day is a school day!' and he's thankful for the vibrant SQL Server community. His online presences include:

Retrieved from "http://sqlserverpedia.com/wiki/Reporting_Services_Meta-data_scripts"