Search code examples
reporting-servicesssrs-2008

Why ssrs report subscriptions are Pending and deliver very late

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.

  1. I have restarted the subscriptions manually in "SQL Server Agent".
  2. 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.
  3. Restarted the "SQL Server Reporting Service" from Services.
  4. Restarted the Server where this subscriptions are hosted.
  5. Someone suggested to check the & character in "ExtensionSettings" column in Subscription table, but there is no & character.

  6. 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!

Solution

  • I found answer myself after analyzing deeply. In order to solve the issue i have followed two steps. Step 1: I have removed/cleared all the subscriptions which are returning with error like "library!WindowsService_0!152c!09/09/2010-13:47:42:: e ERROR:". Step 2: "DatabaseQueryTimeout" - Increased the limit (default is 120)