Can you please help me out with the below issue? I have googled like anything but could not find a solution. Your ideas or suggestions will be great helpful for me to fix this issue.
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.
What I have tried:
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 '12/09/2019' AND '12/10/2019' ORDER BY TimeStart DESC
Select * FROM dbo.ExecutionLog2 WHERE CAST(TimeStart AS DATE) BETWEEN '12/09/2019' AND '12/10/2019' ORDER BY TimeStart DESC
SELECT Top 5000 * FROM dbo.ExecutionLog3 where Status <> 'rsSuccess' and CAST (timestart as date) =cast('2019-10-12 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!