1

I need to get the runtime log for subscription.

I need to know in a period of time how many times the subscription was invoked and how long took to run, From Pending to Successful or Failed Status.

I have tried to get the information from msdb.dbo all jobs tables but the runtime there is something different.

Any help will be more than appreciated.

Dale K
  • 25,246
  • 15
  • 42
  • 71

2 Answers2

0

Here is a start.. it doesn't give you everything you want.. but certainly enough to get you started

SELECT
sj.[name] AS [Job Name],
rs.SubscriptionID,
c.[Name] AS [Report Name],
c.[Path],
su.Description,
su.EventType,
su.LastStatus,
su.LastRunTime


FROM msdb..sysjobs AS sj 

INNER JOIN ReportServer..ReportSchedule AS rs
ON sj.[name] = CAST(rs.ScheduleID AS NVARCHAR(128)) 

INNER JOIN ReportServer..Subscriptions AS su
ON rs.SubscriptionID = su.SubscriptionID

INNER JOIN ReportServer..[Catalog] c
ON su.Report_OID = c.ItemID
Harry
  • 2,636
  • 1
  • 17
  • 29
0

SSRS ReportServer help us to find details about the reports executions and other details. So we can use the following query to find out answers of the following;

-How many times the subscription was invoked?

-How long took to run?

SELECT TMP_TBL.*,sc.LastStatus,sc.LastRunTime FROM (
select  ROW_NUMBER() OVER(ORDER BY TimeEnd DESC) AS Rw_Nr,CASE(RequestType) 
        WHEN 0 THEN 'Interactive'
        WHEN 1 THEN 'Subscription'
        WHEN 2 THEN 'Refresh Cache'
        ELSE 'Unknown'
        END AS RequestType ,DATEDIFF(second,TimeStart,TimeEnd) AS Execution_Time ,Status ,UserName ,Format ,c.ItemID
FROM ExecutionLogStorage EL WITH(NOLOCK)
LEFT  JOIN Catalog C WITH(NOLOCK) ON (EL.ReportID = C.ItemID)
where RequestType=1
-- AND el.TimeEnd BETWEEN @BegTime AND @EndTime
) AS TMP_TBL LEFT JOIN Subscriptions sc ON sc.Report_OID = TMP_TBL.ItemID and TMP_TBL.Rw_Nr=1
Esat Erkec
  • 1,575
  • 7
  • 13
  • Thanks Esat. The only problem with ExecutionLogStorage table is u cant identify which subscription is consuming more execution time by ReportID. Any other idea? –  Nov 12 '19 at 14:31
  • The Execution_Time column may help you. If don t help you, please let me know. – Esat Erkec Nov 12 '19 at 14:58
  • Sorry if my previous comment was not clear enough, I need to link somehow the report id on ExecutionLogStorage (in case to be a subscription) with Subscription Table –  Nov 13 '19 at 15:29