SSRS Subscriptions and Schedules: The Complete Inventory, Troubleshooting, and Health-Check Workshop

Report Manager shows subscriptions one report at a time. There is no single screen listing every subscription across the entire catalog, what it is scheduled to do, and when it last ran. This workshop builds that list directly from the ReportServer database, one step at a time, ending in a single script that returns every subscription with a plain-language schedule, its owner, its delivery method, and its last and next run times.

SQL Server Reporting Services reached its final release with SQL Server 2022. SQL Server 2025, released November 2025, ships without SSRS; Power BI Report Server (PBIRS) is now the default on-premises reporting platform going forward, and no future SSRS version will be released. Existing SSRS 2022 installations remain fully supported with security updates through January 11, 2033, and can continue to serve as the reporting catalog even alongside newer SQL Server database engine versions. That is a decade-long runway in which a very large existing SSRS install base keeps running exactly as it always has, which is precisely why the subscriptions and schedules already in that catalog are worth inventorying properly rather than assumed. Power BI Report Server is built on the same reporting engine, so the Subscriptions, Schedule, ReportSchedule, and ExecutionLog family of catalog tables used throughout this workshop apply there as well.

1Prerequisites and a Note on the Schema

Every query in this workshop runs against the ReportServer database (the default catalog database name; adjust if the environment uses a different name). db_datareader on that database is sufficient; no elevated server permissions are required.

The Subscriptions, Schedule, ReportSchedule, Catalog, and Users tables used here are not officially documented by Microsoft, though Microsoft’s own guidance confirms they can be queried safely (just not modified directly). Every column and value mapping used in this workshop was cross-checked across multiple independent sources before being included; where a mapping could not be confirmed with confidence, it was left out rather than guessed at.

2Step 1: List Every Subscription and Its Report

Start with the base chain: Subscriptions joined to Catalog for the report name and path, and to Users for the subscription owner.

SELECT
    cat.Path                AS ReportFolder,
    cat.Name                 AS ReportName,
    usr.UserName              AS SubscriptionOwner,
    sub.Description           AS SubscriptionDescription,
    sub.DeliveryExtension     AS DeliveryMethod,
    sub.LastStatus            AS LastRunStatus,
    sub.LastRunTime           AS LastRunTime
FROM      ReportServer.dbo.Subscriptions AS sub
JOIN      ReportServer.dbo.[Catalog]     AS cat ON sub.Report_OID = cat.ItemID
LEFT JOIN ReportServer.dbo.Users         AS usr ON sub.OwnerID    = usr.UserID
ORDER BY  cat.Path, cat.Name;

This alone answers “what subscriptions exist and who owns them.” DeliveryExtension shows the delivery method at a glance (email, file share, or a data-driven/null delivery provider). What it does not yet show is when each one runs, which is the next step.

3Step 2: Bring In the Schedule

Add ReportSchedule as the bridge table and Schedule for the timing detail. This introduces NextRunTime, the start and end dates, and the raw recurrence columns that Step 3 decodes.

SELECT
    cat.Path                AS ReportFolder,
    cat.Name                 AS ReportName,
    usr.UserName              AS SubscriptionOwner,
    sub.DeliveryExtension     AS DeliveryMethod,
    sub.LastStatus            AS LastRunStatus,
    sub.LastRunTime           AS LastRunTime,
    sch.NextRunTime           AS NextRunTime,
    sch.StartDate             AS ScheduleStartDate,
    sch.EndDate               AS ScheduleEndDate,
    sch.RecurrenceType,
    sch.MinutesInterval,
    sch.DaysInterval,
    sch.WeeksInterval,
    sch.DaysOfWeek,
    rs.ScheduleID              AS SqlAgentJobID
FROM      ReportServer.dbo.Subscriptions   AS sub
JOIN      ReportServer.dbo.[Catalog]       AS cat ON sub.Report_OID    = cat.ItemID
JOIN      ReportServer.dbo.ReportSchedule  AS rs  ON rs.ReportID       = cat.ItemID
                                                 AND rs.SubscriptionID = sub.SubscriptionID
JOIN      ReportServer.dbo.Schedule        AS sch ON rs.ScheduleID     = sch.ScheduleID
LEFT JOIN ReportServer.dbo.Users           AS usr ON sub.OwnerID       = usr.UserID
ORDER BY  cat.Path, cat.Name;

At this point every schedule is visible, but RecurrenceType is just an integer and DaysOfWeek is a bitmask. Neither is readable yet.

4Step 3: Decode the Schedule Into Plain English

RecurrenceType values were verified against Microsoft’s documented Reporting Services SOAP API, which exposes the same five recurrence patterns as distinct classes (MinuteRecurrence, DailyRecurrence, WeeklyRecurrence, MonthlyRecurrence, MonthlyDOWRecurrence):

DaysOfWeek decodes reliably as a standard bitmask: Sunday = 1, Monday = 2, Tuesday = 4, Wednesday = 8, Thursday = 16, Friday = 32, Saturday = 64, combined with bitwise OR. Add this CASE expression:

    CASE sch.RecurrenceType
        WHEN 1 THEN 'Runs once, not recurring'
        WHEN 2 THEN 'Every ' + CAST(sch.MinutesInterval AS VARCHAR(10)) + ' minute(s)'
        WHEN 3 THEN 'Every ' + CAST(sch.DaysInterval AS VARCHAR(10)) + ' day(s)'
        WHEN 4 THEN 'Every ' + CAST(sch.WeeksInterval AS VARCHAR(10)) + ' week(s) on '
            + STUFF(
                CASE WHEN sch.DaysOfWeek & 1  <> 0 THEN ', Sun' ELSE '' END +
                CASE WHEN sch.DaysOfWeek & 2  <> 0 THEN ', Mon' ELSE '' END +
                CASE WHEN sch.DaysOfWeek & 4  <> 0 THEN ', Tue' ELSE '' END +
                CASE WHEN sch.DaysOfWeek & 8  <> 0 THEN ', Wed' ELSE '' END +
                CASE WHEN sch.DaysOfWeek & 16 <> 0 THEN ', Thu' ELSE '' END +
                CASE WHEN sch.DaysOfWeek & 32 <> 0 THEN ', Fri' ELSE '' END +
                CASE WHEN sch.DaysOfWeek & 64 <> 0 THEN ', Sat' ELSE '' END
              , 1, 2, '')
        WHEN 5 THEN 'Monthly by day-of-month (see DaysOfMonth/Month columns, bitmask-encoded)'
        WHEN 6 THEN 'Monthly by relative weekday, e.g. "third Tuesday" (see MonthlyWeek/DaysOfWeek)'
        ELSE 'Unrecognized RecurrenceType ' + CAST(sch.RecurrenceType AS VARCHAR(10)) + ' -- verify in Report Portal'
    END AS ScheduleDescription

5Step 4: Reveal the Actual Delivery Target

Subscriptions.Description is a human-typed UI label and is not guaranteed to contain the real recipient. The actual email addresses or file share path live in Subscriptions.ExtensionSettings as XML. For email subscriptions:

SELECT
    cat.Name AS ReportName,
    sub.SubscriptionID,
    CONVERT(XML, sub.ExtensionSettings)
        .value('(//ParameterValue[Name="TO"]/Value)[1]', 'NVARCHAR(MAX)') AS EmailTo
FROM ReportServer.dbo.Subscriptions AS sub
JOIN ReportServer.dbo.[Catalog]     AS cat ON sub.Report_OID = cat.ItemID
WHERE sub.DeliveryExtension = 'Report Server Email';

File-share subscriptions store the target path under a different parameter name in the same XML structure (typically PATH). Data-driven subscriptions return NULL here by design, since the recipient list is generated per execution from a query rather than stored statically.

6Step 5: Cross-Reference the SQL Agent Job

Every SSRS schedule that actually fires is backed by a SQL Server Agent job on the same instance as the ReportServer database, named after the ScheduleID GUID captured in Step 2. This confirms a schedule is genuinely active at the Agent level, since a subscription can look correctly configured in SSRS while its underlying job is disabled.

SELECT
    j.name    AS AgentJobName,
    j.enabled AS JobEnabled,
    ja.run_requested_date,
    ja.stop_execution_date
FROM msdb.dbo.sysjobs AS j
LEFT JOIN msdb.dbo.sysjobactivity AS ja ON ja.job_id = j.job_id
WHERE j.name = '<SqlAgentJobID from Step 2>';

7Step 6: Flag Active vs. Inactive Schedules

A subscription can exist, look correctly configured, and still not be delivering anything, because its schedule has expired, been paused, or is failing. The Schedule.State column carries this status directly, and it is one of the few columns in this schema that Microsoft documents explicitly, via the ScheduleStateEnum used in the Reporting Services API:

Add this to surface it directly as a status label:

    CASE sch.State
        WHEN 0 THEN 'Ready'
        WHEN 1 THEN 'Running'
        WHEN 2 THEN 'Paused'
        WHEN 3 THEN 'Expired'
        WHEN 4 THEN 'Failing'
        ELSE 'Unknown state ' + CAST(sch.State AS VARCHAR(10))
    END AS ScheduleStatus

8Step 7: How Long Reports Run and Troubleshooting Failures

Whether a subscription is scheduled is one question. Whether it actually ran successfully, and how long it took, is answered by the report execution log, not by the Subscriptions or Schedule tables. SSRS exposes this through three views over the same underlying ExecutionLogStorage table:

All three remain queryable in current versions for backward compatibility, but Microsoft’s own guidance is direct: use ExecutionLog3 unless something already depends on one of the older two.

How long a report takes to run

SELECT
    el.ItemPath,
    COUNT(*)                                                            AS ExecutionCount,
    AVG(el.TimeDataRetrieval)                                           AS AvgDataRetrievalMs,
    AVG(el.TimeProcessing)                                              AS AvgProcessingMs,
    AVG(el.TimeRendering)                                               AS AvgRenderingMs,
    AVG(el.TimeDataRetrieval + el.TimeProcessing + el.TimeRendering)    AS AvgTotalMs,
    MAX(el.TimeDataRetrieval + el.TimeProcessing + el.TimeRendering)    AS MaxTotalMs
FROM ReportServer.dbo.ExecutionLog3 AS el
WHERE el.TimeStart >= DATEADD(DAY, -30, GETDATE())
  AND el.Status = 'rsSuccess'
GROUP BY el.ItemPath
ORDER BY AvgTotalMs DESC;

A high AvgDataRetrievalMs points at the underlying query or data source. A high AvgProcessingMs points at expressions or dataset size inside the report itself. A high AvgRenderingMs usually means too many visual elements, sub-reports, or embedded images.

Finding and reading failures

SELECT
    el.ItemPath,
    el.UserName,
    el.TimeStart,
    el.Status,
    el.TimeDataRetrieval,
    el.TimeProcessing,
    el.TimeRendering
FROM ReportServer.dbo.ExecutionLog3 AS el
WHERE el.Status <> 'rsSuccess'
  AND el.TimeStart >= DATEADD(DAY, -30, GETDATE())
ORDER BY el.TimeStart DESC;

A successful run shows Status = 'rsSuccess'. A failed run shows an error code instead, such as rsItemNotFound. If more than one error condition occurred during a single execution, only the first is recorded here; the AdditionalInfo XML column (present in ExecutionLog2 and ExecutionLog3) carries further diagnostic detail beyond the status code when it is available.

9The Complete Script

Everything from Steps 1 through 3 combined into one query. This is the version to save and run as the standing inventory.

SELECT
    cat.Path                                    AS ReportFolder,
    cat.Name                                     AS ReportName,
    usr.UserName                                 AS SubscriptionOwner,
    sub.Description                              AS SubscriptionDescription,
    sub.DeliveryExtension                        AS DeliveryMethod,
    sub.LastStatus                               AS LastRunStatus,
    sub.LastRunTime                              AS LastRunTime,
    sch.NextRunTime                              AS NextRunTime,
    sch.StartDate                                AS ScheduleStartDate,
    sch.EndDate                                  AS ScheduleEndDate,
    CASE sch.RecurrenceType
        WHEN 1 THEN 'Runs once, not recurring'
        WHEN 2 THEN 'Every ' + CAST(sch.MinutesInterval AS VARCHAR(10)) + ' minute(s)'
        WHEN 3 THEN 'Every ' + CAST(sch.DaysInterval AS VARCHAR(10)) + ' day(s)'
        WHEN 4 THEN 'Every ' + CAST(sch.WeeksInterval AS VARCHAR(10)) + ' week(s) on '
            + STUFF(
                CASE WHEN sch.DaysOfWeek & 1  <> 0 THEN ', Sun' ELSE '' END +
                CASE WHEN sch.DaysOfWeek & 2  <> 0 THEN ', Mon' ELSE '' END +
                CASE WHEN sch.DaysOfWeek & 4  <> 0 THEN ', Tue' ELSE '' END +
                CASE WHEN sch.DaysOfWeek & 8  <> 0 THEN ', Wed' ELSE '' END +
                CASE WHEN sch.DaysOfWeek & 16 <> 0 THEN ', Thu' ELSE '' END +
                CASE WHEN sch.DaysOfWeek & 32 <> 0 THEN ', Fri' ELSE '' END +
                CASE WHEN sch.DaysOfWeek & 64 <> 0 THEN ', Sat' ELSE '' END
              , 1, 2, '')
        WHEN 5 THEN 'Monthly by day-of-month (see DaysOfMonth/Month, bitmask-encoded)'
        WHEN 6 THEN 'Monthly by relative weekday, e.g. "third Tuesday" (see MonthlyWeek/DaysOfWeek)'
        ELSE 'Unrecognized RecurrenceType ' + CAST(sch.RecurrenceType AS VARCHAR(10)) + ' -- verify in Report Portal'
    END                                           AS ScheduleDescription,
    CASE sch.State
        WHEN 0 THEN 'Ready'
        WHEN 1 THEN 'Running'
        WHEN 2 THEN 'Paused'
        WHEN 3 THEN 'Expired'
        WHEN 4 THEN 'Failing'
        ELSE 'Unknown state ' + CAST(sch.State AS VARCHAR(10))
    END                                           AS ScheduleStatus,
    rs.ScheduleID                                 AS SqlAgentJobID
FROM      ReportServer.dbo.Subscriptions   AS sub
JOIN      ReportServer.dbo.[Catalog]       AS cat ON sub.Report_OID     = cat.ItemID
JOIN      ReportServer.dbo.ReportSchedule  AS rs  ON rs.ReportID        = cat.ItemID
                                               AND rs.SubscriptionID  = sub.SubscriptionID
JOIN      ReportServer.dbo.Schedule        AS sch ON rs.ScheduleID      = sch.ScheduleID
LEFT JOIN ReportServer.dbo.Users           AS usr ON sub.OwnerID        = usr.UserID
ORDER BY  cat.Path, cat.Name;

10Operationalizing This: From One-Time Script to Recurring Health Check

A single run of this script is a snapshot. Its real value shows up when it runs on a schedule and its results are compared over time, since a schedule that has been failing for one day is a different problem than one that has been failing for three months unnoticed. Running it once answers “what does this look like today.” Running it on a cadence, with history kept, answers “what changed, and when.”

1. Triage failures first. Pull every row where ScheduleStatus = 'Failing' (State 4) from Step 6. These are actively broken today, not routine housekeeping, and take priority over everything else on this list.
2. Sweep expired schedules for intent, not just status. A ScheduleStatus = 'Expired' row (State 3) needs a human answer, not an automatic fix. Confirm with the owner whether the expiration was deliberate (the report was retired) or accidental (an end date was set without meaning to) before re-enabling or deleting anything.
3. Run the complete script on a schedule, not on demand. Point it at a small history table, weekly or monthly, so ScheduleStatus and LastRunStatus become a trend rather than a one-time reading.
4. Flag orphaned ownership as its own finding. Cross-reference SubscriptionOwner against account status. An active subscription owned by a disabled or departed account is a governance gap, especially where the delivery target is an external recipient.
5. Track execution duration as a trend, using Step 7. A report drifting from three seconds to thirty seconds over six months is invisible in any single run and obvious the moment there is history to compare against.
6. Reuse the full inventory as migration proof. Before moving to Power BI Report Server or a newer SQL Server version, run the complete script and save the output. Run it again after. Row counts and ScheduleStatus values should match; anything that does not is exactly what needs investigating before calling the move complete.

11Key Takeaways

• A single query joining Subscriptions, Catalog, ReportSchedule, and Schedule produces one row per subscription with a fully decoded schedule.
• DaysOfWeek decodes reliably as a standard power-of-two bitmask; DaysOfMonth and Month follow the same pattern but were left flagged rather than fully decoded without stronger verification.
• Subscriptions.Description is a UI label, not the delivery target; the real recipient or file path lives in the ExtensionSettings XML.
• The ScheduleID maps directly to a SQL Server Agent job name, which is the fastest way to confirm a schedule is genuinely active versus just configured.
• Schedule.State gives a direct active/inactive signal (0–4: Ready, Running, Paused, Expired, Failing), officially documented via the Reporting Services ScheduleStateEnum. Expired and Failing are the two values worth monitoring for.
• Scheduling and execution are different tables. ExecutionLog3 (not the legacy ExecutionLog view) is Microsoft’s current recommendation for both run duration (TimeDataRetrieval / TimeProcessing / TimeRendering) and failure troubleshooting (Status <> 'rsSuccess'), within a 60-day retention window.
• SSRS 2022 is the final SSRS release; SQL Server 2025 ships with Power BI Report Server as the default on-premises reporting platform instead. SSRS 2022 remains supported through January 11, 2033, and PBIRS shares the same catalog schema, so this inventory applies to both the existing install base and whatever it eventually becomes.

The ReportServer catalog database schema referenced in this workshop is not officially documented by Microsoft. Column names, join logic, and value mappings were cross-verified across multiple independent sources and checked against Microsoft’s documented Reporting Services SOAP API for structural consistency. Always validate schedule and subscription output against Report Manager or Report Portal before using it for a change that affects report delivery.

References

• Microsoft Docs: RecurrencePattern Class (ReportService2010)
• Microsoft Docs: ScheduleStateEnum Enum (ReportService2006)
• Microsoft Docs: Use ExecutionLog and the ExecutionLog3 view in Reporting Services
• Microsoft Docs: Reporting Services Consolidation FAQ (SSRS to Power BI Report Server)
• SQLYARD: SQL Server Agent Jobs: The Complete Guide