none
Why SSRS report subscriptions are in pending state and deliver (Email/Fileshare) very late RRS feed

  • Question

  • Hi,
    I'm completely new to SSRS report subscriptions and I have 700+ ssrs report subscriptions. All the subscriptions were working till last month and now subscriptions will be in "Pending" state and after 2~3 hours of time all the subscriptions will execute and deliver to the user.

    I have tried below ways but none these helped.

    I have restarted the subscriptions manually in "SQL Server Agent".
    Created a new subscription on my email ID but even new subscription also will be in pending state and after 2~3 hours it will trigger.
    Restarted the "SQL Server Reporting Service" from Services.
    Restarted the Server where this subscriptions are hosted.
    Someone suggested to check the & character in "ExtensionSettings" column in Subscription table, but there is no & character.
    Below queries i have tried but could not get anything.

    SELECT s.[SubscriptionID] -- Subscription ID ,s.[OwnerID] -- Report Owner ,s.[Report_OID] -- Report ID , c.Path -- Report Path ,rs.ScheduleID as SQLJobName -- Name of Job on SQL Server ,s.[Description] -- Description of the report subscription ,s.[LastStatus] -- Status of last subscription execution. ,s.[EventType] -- Subscription type ,s.[LastRunTime] -- Last time subscription executed ,s.[Parameters] -- Parameters used for subscription ,s.[DeliveryExtension] -- How to deliver the subscription FROM [ReportServer].[dbo].[Subscriptions] as s left join dbo.Catalog as c on c.ItemID = s.Report_OID left join dbo.ReportSchedule as rs on rs.ReportID = s.Report_OID order by c.Path

    SELECT Top 10 * FROM dbo.ExecutionLog WHERE CAST(TimeStart AS DATE) BETWEEN '21/10/2019' AND '21/10/2019' ORDER BY TimeStart DESC

    Select * FROM dbo.ExecutionLog2 WHERE CAST(TimeStart AS DATE) BETWEEN '7/1/2014' AND '7/2/2014' ORDER BY TimeStart DESC

    SELECT Top 5000 * FROM dbo.ExecutionLog3 where Status <> 'rsSuccess' and CAST (timestart as date) =cast('2019-10-24 10:00:29.120' as date) ORDER BY TimeStart DESC

    What could be the possible cause for pending state and delay in subscriptions? how to solve this issue?

    Thanks in advance!

    Tuesday, December 3, 2019 10:13 AM

All replies

  • Hi Steve,

    As I once saw the same issue, have you tried "changing the owner of the subscriptions and then a reboot." this approach? It seems worked for once.

    Also, when you hit the run now button on Web Portal, will the subscription also be pending? 

    You could check this article see if it helps: SQL Server Reporting Services Subscriptions - Stuck with Pending Status

    Could you share other the error log and execution log after one testing of hitting the "Run Now" button? The logs are located in Reporting Services Log Files and Sources.

    Regards,

    Lukas


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, December 4, 2019 2:01 AM