Click here to Skip to main content
15,944,737 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
Hi,

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!
Posted
Updated 9-Dec-19 19:56pm
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900