SQL Server Reporting Services: The Complete DBA Guide

SQL Server Reporting Services is the on-premises, server-hosted reporting platform that has delivered paginated, pixel-perfect reports — invoices, regulatory extracts, operational dashboards, and scheduled email deliveries — for over two decades. Tens of thousands of organizations still run SSRS in production today. Understanding the architecture, the ReportServer database internals, subscription management, execution log monitoring, performance diagnosis, and the SQL Server 2025 transition is core DBA knowledge.

SSRS runs as a Windows service (SQL Server Reporting Services) separate from the SQL Server Database Engine. The two communicate over HTTP/HTTPS — the report server exposes two URL endpoints configured in Report Server Configuration Manager.

The Report Server Configuration Manager (not SSMS) handles all SSRS configuration: service account, URL reservations, database connection, email settings, encryption keys, and scale-out deployment. It is a separate tool launched from the Windows Start menu or RSConfigTool.exe.

The ReportServer database is the operational heart of SSRS. Microsoft does not officially support direct modification of its tables, but querying them for monitoring and reporting is standard DBA practice and is widely documented.

SSRS operates in one of two modes set at installation time and not changeable without a reinstall.

Every SSRS report connects to data through a data source. Data sources can be embedded within a report (report-specific) or shared at the server level (reusable across many reports). Shared data sources are the correct choice for any environment with multiple reports connecting to the same database, because a single credential or connection string change propagates to all reports using that shared source.

Stored credentials in SSRS are encrypted using the symmetric encryption key managed by the Report Server Configuration Manager. The encryption key must be backed up after any significant SSRS configuration change and is required to restore SSRS to a new server. Without the key, stored credentials cannot be decrypted and all subscriptions using stored credentials stop working.

Subscriptions deliver reports automatically on a schedule, without user interaction. SSRS supports two types with a meaningful difference in capability and edition requirement.

Subscription delivery methods include email (requires SMTP configuration in Report Server Configuration Manager), Windows file share (UNC path, requires credentials with write access to the share), and report server (saves a snapshot to the report server itself). Each delivery method stores configuration in the dbo.Subscriptions table as an XML blob in the ExtensionSettings column.

Every SSRS subscription schedule creates a SQL Server Agent job in the ReportServer job category. The Agent job name is a GUID that matches the schedule ID in ReportServer.dbo.ReportSchedule. SQL Server Agent must be running for any SSRS subscription to fire. If Agent is stopped, all subscriptions silently stop running without error in the SSRS logs.

-- Find all SSRS subscription Agent jobs with their associated report names
-- Links ReportServer.dbo.ReportSchedule and msdb.dbo.sysjobs
SELECT
    j.name                          AS agent_job_name,
    j.enabled                       AS job_enabled,
    c.Name                          AS report_name,
    c.Path                          AS report_path,
    s.Description                   AS subscription_description,
    s.LastStatus                    AS last_status,
    s.LastRunTime                   AS last_run_time,
    s.DeliveryExtension             AS delivery_method
FROM ReportServer.dbo.ReportSchedule    rs
JOIN ReportServer.dbo.Subscriptions     s  ON rs.SubscriptionID   = s.SubscriptionID
JOIN ReportServer.dbo.Catalog           c  ON s.Report_OID        = c.ItemID
JOIN msdb.dbo.sysjobs                   j  ON j.name = CAST(rs.ScheduleID AS NVARCHAR(128))
ORDER BY s.LastRunTime DESC;
GO

SSRS uses its own role-based security model independent of SQL Server database permissions. Roles are assigned to users and groups at the folder or item level through the web portal. SSRS security does not inherit from Windows file system permissions or SQL Server logins.

System-level roles (applied at the server level rather than a specific folder) include System Administrator (full server management) and System User (view server properties, execute shared schedules).

The execution log records every report execution on the server. It is stored in ReportServer.dbo.ExecutionLogStorage and surfaced through three views: ExecutionLog, ExecutionLog2, and ExecutionLog3. Always use ExecutionLog3 — it is the most recent and contains the most complete set of columns including time breakdowns by processing phase.

By default, log entries are retained for 60 days. The retention period is configurable in the SSRS web portal under Site Settings, or via the ConfigurationInfo table. Entries older than the retention period are deleted at 2:00 AM daily by a cleanup job.

-- All executions in the last 24 hours with phase timing
USE ReportServer;
GO

SELECT
    e.UserName,
    e.ItemPath                                      AS report_path,
    e.TimeStart,
    e.TimeEnd,
    DATEDIFF(SECOND, e.TimeStart, e.TimeEnd)        AS total_seconds,
    e.TimeDataRetrieval                             AS data_retrieval_ms,
    e.TimeProcessing                                AS processing_ms,
    e.TimeRendering                                 AS rendering_ms,
    e.Format                                        AS output_format,
    e.Source,
    e.Status,
    CASE e.RequestType
        WHEN 0 THEN 'Interactive'
        WHEN 1 THEN 'Subscription'
        WHEN 2 THEN 'RefreshCache'
        ELSE 'Unknown'
    END                                             AS request_type
FROM dbo.ExecutionLog3 e
WHERE e.TimeStart >= DATEADD(HOUR, -24, GETDATE())
ORDER BY e.TimeStart DESC;
GO

-- Most-run reports in the last 30 days with average execution time
USE ReportServer;
GO

SELECT
    c.Name                                          AS report_name,
    c.Path                                          AS report_path,
    COUNT(*)                                        AS execution_count,
    AVG(DATEDIFF(MILLISECOND, e.TimeStart, e.TimeEnd)) AS avg_duration_ms,
    MAX(DATEDIFF(MILLISECOND, e.TimeStart, e.TimeEnd)) AS max_duration_ms,
    COUNT(DISTINCT e.UserName)                      AS distinct_users,
    SUM(CASE WHEN e.Status <> 'rsSuccess'
             THEN 1 ELSE 0 END)                    AS error_count
FROM dbo.ExecutionLog3 e
JOIN dbo.Catalog       c ON e.ItemPath = c.Path
WHERE e.TimeStart >= DATEADD(DAY, -30, GETDATE())
  AND c.Type = 2   -- 2 = report (excludes folders, data sources)
GROUP BY c.Name, c.Path
ORDER BY execution_count DESC;
GO

When a report is slow, the execution log phase timing tells the DBA exactly where time is being spent. Each case points to a different owner and resolution.

-- Find slowest reports by average data retrieval time (last 14 days)
-- High TimeDataRetrieval = tune the query on the source database
USE ReportServer;
GO

SELECT TOP 20
    c.Name                                          AS report_name,
    COUNT(*)                                        AS execution_count,
    AVG(e.TimeDataRetrieval)                        AS avg_data_retrieval_ms,
    AVG(e.TimeProcessing)                           AS avg_processing_ms,
    AVG(e.TimeRendering)                            AS avg_rendering_ms,
    AVG(DATEDIFF(MILLISECOND, e.TimeStart, e.TimeEnd)) AS avg_total_ms
FROM dbo.ExecutionLog3 e
JOIN dbo.Catalog       c ON e.ItemPath = c.Path
WHERE e.TimeStart >= DATEADD(DAY, -14, GETDATE())
  AND e.Status    = 'rsSuccess'
  AND c.Type      = 2
GROUP BY c.Name
HAVING COUNT(*) >= 5   -- only reports with meaningful sample size
ORDER BY avg_data_retrieval_ms DESC;
GO

-- Reports where rendering is the bottleneck (rendering > 50% of total time)
-- These are report design problems, not database problems
USE ReportServer;
GO

SELECT TOP 20
    c.Name                                          AS report_name,
    COUNT(*)                                        AS execution_count,
    AVG(e.TimeRendering)                            AS avg_rendering_ms,
    AVG(e.TimeDataRetrieval)                        AS avg_data_retrieval_ms,
    AVG(DATEDIFF(MILLISECOND, e.TimeStart, e.TimeEnd)) AS avg_total_ms,
    CAST(AVG(e.TimeRendering) * 100.0 /
         NULLIF(AVG(DATEDIFF(MILLISECOND, e.TimeStart, e.TimeEnd)), 0)
         AS DECIMAL(5,1))                           AS rendering_pct_of_total
FROM dbo.ExecutionLog3 e
JOIN dbo.Catalog       c ON e.ItemPath = c.Path
WHERE e.TimeStart >= DATEADD(DAY, -14, GETDATE())
  AND e.Status    = 'rsSuccess'
  AND c.Type      = 2
GROUP BY c.Name
HAVING COUNT(*) >= 5
   AND AVG(e.TimeRendering) > AVG(e.TimeDataRetrieval)
ORDER BY rendering_pct_of_total DESC;
GO

SSRS subscription failures are silent by default. The Agent job that fires the subscription completes with a success exit code even when delivery fails. The only reliable source of subscription failure status is ReportServer.dbo.Subscriptions.LastStatus. Building a monitoring job that queries this column and alerts on non-success status is the correct approach.

-- Find all subscriptions that failed on their last run
USE ReportServer;
GO

SELECT
    c.Name                          AS report_name,
    c.Path                          AS report_path,
    s.Description                   AS subscription_description,
    s.DeliveryExtension             AS delivery_method,
    s.LastRunTime,
    s.LastStatus,
    s.EventType,
    u.UserName                      AS subscription_owner
FROM dbo.Subscriptions  s
JOIN dbo.Catalog        c ON s.Report_OID = c.ItemID
JOIN dbo.Users          u ON s.OwnerID    = u.UserID
WHERE s.LastStatus NOT LIKE 'Mail sent%'
  AND s.LastStatus NOT LIKE 'New Subscription%'
  AND s.LastStatus <> 'rsSuccess'
  AND s.LastRunTime IS NOT NULL
ORDER BY s.LastRunTime DESC;
GO

-- SQL Server Agent job to alert on failed subscriptions
-- Create this as a daily monitoring job in SQL Server Agent

USE msdb;
GO

-- Step script for the Agent job:
DECLARE @FailedCount INT;
DECLARE @EmailBody   NVARCHAR(MAX);

SELECT @FailedCount = COUNT(*)
FROM ReportServer.dbo.Subscriptions
WHERE LastStatus NOT LIKE 'Mail sent%'
  AND LastStatus NOT LIKE 'New Subscription%'
  AND LastStatus <> 'rsSuccess'
  AND LastRunTime >= DATEADD(DAY, -1, GETDATE());

IF @FailedCount > 0
BEGIN
    SET @EmailBody =
        CAST(@FailedCount AS NVARCHAR(10)) +
        ' SSRS subscription(s) failed in the last 24 hours. ' +
        'Query ReportServer.dbo.Subscriptions for details.';

    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'DBA Alerts',
        @recipients   = 'dba-team@yourcompany.com',
        @subject      = 'SSRS Subscription Failures Detected',
        @body         = @EmailBody;
END
GO

In mature SSRS environments, a significant proportion of deployed reports have not been run in months. Identifying and decommissioning unused reports reduces catalog clutter, simplifies maintenance, and frees up storage. The execution log provides the data; the challenge is that the log only covers the retention window (default 60 days).

-- Reports not run in the last 90 days (within the log retention window)
-- Extend the retention period first if 90 days exceeds the configured retention
USE ReportServer;
GO

SELECT
    c.Name                          AS report_name,
    c.Path                          AS report_path,
    c.CreationDate                  AS created_date,
    c.ModifiedDate                  AS last_modified,
    MAX(e.TimeStart)                AS last_execution,
    COUNT(e.TimeStart)              AS execution_count_in_log
FROM dbo.Catalog                    c
LEFT JOIN dbo.ExecutionLogStorage   e ON e.ReportID = c.ItemID
WHERE c.Type = 2   -- reports only, not folders or data sources
GROUP BY c.Name, c.Path, c.CreationDate, c.ModifiedDate
HAVING MAX(e.TimeStart) < DATEADD(DAY, -90, GETDATE())
    OR MAX(e.TimeStart) IS NULL   -- never run at all
ORDER BY last_execution ASC, c.Name;
GO

-- Increase execution log retention from 60 to 180 days
-- Run this in the ReportServer database (or use Site Settings in the web portal)
USE ReportServer;
GO

UPDATE dbo.ConfigurationInfo
SET Value = '180'
WHERE Name = 'ExecutionLogDaysKept';
GO
-- Note: This takes effect at the next 2:00 AM cleanup run
-- Verify: SELECT Name, Value FROM dbo.ConfigurationInfo WHERE Name = 'ExecutionLogDaysKept'

Power BI Report Server is a superset of SSRS. Everything SSRS does — paginated RDL reports, subscriptions, data sources, security model, ReportServer database — PBIRS also does. PBIRS additionally supports interactive Power BI reports (.pbix files) hosted on-premises. For organizations running only paginated reports, the migration from SSRS to PBIRS is primarily an installation and configuration exercise, not a report rebuild.

There is no in-place upgrade from SSRS to PBIRS. The migration involves installing PBIRS on a new server, migrating the ReportServer database, transferring the encryption key, and validating every component before cutover. Run SSRS and PBIRS in parallel until validation is complete.

-- Pre-migration inventory: full report and subscription count
-- Run on the existing SSRS ReportServer database before migration
USE ReportServer;
GO

SELECT
    'Reports'           AS item_type,
    COUNT(*)            AS total_count
FROM dbo.Catalog WHERE Type = 2
UNION ALL
SELECT 'Data Sources', COUNT(*) FROM dbo.Catalog WHERE Type = 5
UNION ALL
SELECT 'Standard Subscriptions',
       COUNT(*) FROM dbo.Subscriptions WHERE DataSettings IS NULL
UNION ALL
SELECT 'Data-Driven Subscriptions',
       COUNT(*) FROM dbo.Subscriptions WHERE DataSettings IS NOT NULL
UNION ALL
SELECT 'Shared Schedules',
       COUNT(*) FROM dbo.Schedule
UNION ALL
SELECT 'Users with permissions',
       COUNT(DISTINCT UserID) FROM dbo.Users;
GO