Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kiltannen
Advocate I
Advocate I

List of current Sheduled Refresh jobs

Hi all,

 

I would like to see a list of all the scheduled refresh jobs.

 

Where is that visible?

 

Or do I have to run some kind of report to see it?

 

I want to be sure I do not have 50 reports all set to run at the same moment every day (or even 2 or 3) - I would have to imagine that would cause some kind of problem...

 

I am running the March (2018) update of On Premises Report Server

1 ACCEPTED SOLUTION
Anonymous
Not applicable

It's not entirely straight forward or obvious how you query this. There are a number of tables in the ReportServer DB that need to be linked together but you also have to hop onto MSDB to get the actual schedule details. The actual refresh jobs get built as SQL agent jobs with GUIDs for names. These GUIDS track back to the schedules you create in SSRS in the ReportServer DB.

 

This query gives you a good chunk of the kind of data you might need. Other stuff is available in the various tables but this gives you a decent starting point for how it all hooks together to generate somehting useful

 

SELECT
	e.name AS ReportName
	,e.path AS ReportPath
	,d.description as SubscriptionName
	, a.SubscriptionID
	, d.laststatus
	, d.eventtype
	, d.LastRunTime
	,j.date_created AS ScheduleCreatedDate
	,j.date_modified AS ScheduleModifiedDate


	,CASE j.[enabled]
		WHEN 1 THEN 'Yes'
		WHEN 0 THEN 'No'
	END AS Job_Enabled
	,CASE sch.[enabled]
		WHEN 1 THEN 'Yes'
		WHEN 0 THEN 'No'
	END AS Schedule_enabled,

	CASE sch.freq_type
		WHEN 1 THEN 'Once'
		WHEN 4 THEN 'Daily'
		WHEN 8 THEN 'Weekly'
		WHEN 16 THEN 'Monthly'
		WHEN 32 THEN 'Monthly relative'
		WHEN 64 THEN 'When SQLServer Agent starts'
	END AS Frequency, 
	CASE sch.active_start_date
		WHEN 0 THEN null
		ELSE
		substring(convert(varchar(15),sch.active_start_date),1,4) + '-' + 
		substring(convert(varchar(15),sch.active_start_date),5,2) + '-' + 
		substring(convert(varchar(15),sch.active_start_date),7,2)
	END AS ScheduleStartDate,
	CASE len(sch.active_start_time)
		WHEN 3 THEN cast('00:0' 
				+ Left(right(sch.active_start_time,3),1)  
				+':' + right(sch.active_start_time,2) as char (8))
		WHEN 4 THEN cast('00:' 
				+ Left(right(sch.active_start_time,4),2)  
				+':' + right(sch.active_start_time,2) as char (8))
		WHEN 5 THEN cast('0' 
				+ Left(right(sch.active_start_time,5),1) 
				+':' + Left(right(sch.active_start_time,4),2)  
				+':' + right(sch.active_start_time,2) as char (8))
		WHEN 6 THEN cast(Left(right(sch.active_start_time,6),2) 
				+':' + Left(right(sch.active_start_time,4),2)  
				+':' + right(sch.active_start_time,2) as char (8))
	END AS ScheduleStartTime

	
	,CASE jsch.next_run_date
		WHEN 0 THEN null
		ELSE
		substring(convert(varchar(15),jsch.next_run_date),1,4) + '-' + 
		substring(convert(varchar(15),jsch.next_run_date),5,2) + '-' + 
		substring(convert(varchar(15),jsch.next_run_date),7,2)
	END AS nextRunDate,
	CASE len(jsch.next_run_time)
		WHEN 3 THEN cast('00:0' 
				+ Left(right(jsch.next_run_time,3),1)  
				+':' + right(jsch.next_run_time,2) as char (8))
		WHEN 4 THEN cast('00:' 
				+ Left(right(jsch.next_run_time,4),2)  
				+':' + right(jsch.next_run_time,2) as char (8))
		WHEN 5 THEN cast('0' 
				+ Left(right(jsch.next_run_time,5),1) 
				+':' + Left(right(jsch.next_run_time,4),2)  
				+':' + right(jsch.next_run_time,2) as char (8))
		WHEN 6 THEN cast(Left(right(jsch.next_run_time,6),2) 
				+':' + Left(right(jsch.next_run_time,4),2)  
				+':' + right(jsch.next_run_time,2) as char (8))
	END AS NextRunTime
FROM ReportServer.dbo.ReportSchedule a 
        LEFT OUTER JOIN ReportServer.dbo.ReportSchedule c
            ON a.ScheduleID = c.ScheduleID
        LEFT OUTER JOIN ReportServer.dbo.Subscriptions d
            ON c.SubscriptionID = d.SubscriptionID
        LEFT OUTER JOIN ReportServer.dbo.[Catalog] e
            ON d.report_oid = e.itemid
		LEFT OUTER JOIN msdb.dbo.sysjobs j
			ON CAST(a.ScheduleID AS VARCHAR(100)) = j.name
		LEFT OUTER JOIN msdb.dbo.sysjobschedules jsch
			ON j.job_id = jsch.job_id
		LEFT OUTER JOIN msdb.dbo.sysschedules sch
			ON sch.schedule_id = jsch.schedule_id
-- to find specific report last status -- WHERE e.name = 'Usage Stats'
-- to find failed status --WHERE -- LastStatus <> 'Completed Data Refresh'
-- or for a specific SQL Agent Job --WHERE
--b.name = '9B7BED26-A81C-479C-A23B-016C9E05E760' -- NOTE you have to look in subscription history for any error messages/details and decode the JSON response (good luck with that!)

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

SSMS-->Databases-->ReportServer-->Tables-->Schedule

Maybe it's not the "Schedule" table but it's in a table somewhere.

Anonymous
Not applicable

It's not entirely straight forward or obvious how you query this. There are a number of tables in the ReportServer DB that need to be linked together but you also have to hop onto MSDB to get the actual schedule details. The actual refresh jobs get built as SQL agent jobs with GUIDs for names. These GUIDS track back to the schedules you create in SSRS in the ReportServer DB.

 

This query gives you a good chunk of the kind of data you might need. Other stuff is available in the various tables but this gives you a decent starting point for how it all hooks together to generate somehting useful

 

SELECT
	e.name AS ReportName
	,e.path AS ReportPath
	,d.description as SubscriptionName
	, a.SubscriptionID
	, d.laststatus
	, d.eventtype
	, d.LastRunTime
	,j.date_created AS ScheduleCreatedDate
	,j.date_modified AS ScheduleModifiedDate


	,CASE j.[enabled]
		WHEN 1 THEN 'Yes'
		WHEN 0 THEN 'No'
	END AS Job_Enabled
	,CASE sch.[enabled]
		WHEN 1 THEN 'Yes'
		WHEN 0 THEN 'No'
	END AS Schedule_enabled,

	CASE sch.freq_type
		WHEN 1 THEN 'Once'
		WHEN 4 THEN 'Daily'
		WHEN 8 THEN 'Weekly'
		WHEN 16 THEN 'Monthly'
		WHEN 32 THEN 'Monthly relative'
		WHEN 64 THEN 'When SQLServer Agent starts'
	END AS Frequency, 
	CASE sch.active_start_date
		WHEN 0 THEN null
		ELSE
		substring(convert(varchar(15),sch.active_start_date),1,4) + '-' + 
		substring(convert(varchar(15),sch.active_start_date),5,2) + '-' + 
		substring(convert(varchar(15),sch.active_start_date),7,2)
	END AS ScheduleStartDate,
	CASE len(sch.active_start_time)
		WHEN 3 THEN cast('00:0' 
				+ Left(right(sch.active_start_time,3),1)  
				+':' + right(sch.active_start_time,2) as char (8))
		WHEN 4 THEN cast('00:' 
				+ Left(right(sch.active_start_time,4),2)  
				+':' + right(sch.active_start_time,2) as char (8))
		WHEN 5 THEN cast('0' 
				+ Left(right(sch.active_start_time,5),1) 
				+':' + Left(right(sch.active_start_time,4),2)  
				+':' + right(sch.active_start_time,2) as char (8))
		WHEN 6 THEN cast(Left(right(sch.active_start_time,6),2) 
				+':' + Left(right(sch.active_start_time,4),2)  
				+':' + right(sch.active_start_time,2) as char (8))
	END AS ScheduleStartTime

	
	,CASE jsch.next_run_date
		WHEN 0 THEN null
		ELSE
		substring(convert(varchar(15),jsch.next_run_date),1,4) + '-' + 
		substring(convert(varchar(15),jsch.next_run_date),5,2) + '-' + 
		substring(convert(varchar(15),jsch.next_run_date),7,2)
	END AS nextRunDate,
	CASE len(jsch.next_run_time)
		WHEN 3 THEN cast('00:0' 
				+ Left(right(jsch.next_run_time,3),1)  
				+':' + right(jsch.next_run_time,2) as char (8))
		WHEN 4 THEN cast('00:' 
				+ Left(right(jsch.next_run_time,4),2)  
				+':' + right(jsch.next_run_time,2) as char (8))
		WHEN 5 THEN cast('0' 
				+ Left(right(jsch.next_run_time,5),1) 
				+':' + Left(right(jsch.next_run_time,4),2)  
				+':' + right(jsch.next_run_time,2) as char (8))
		WHEN 6 THEN cast(Left(right(jsch.next_run_time,6),2) 
				+':' + Left(right(jsch.next_run_time,4),2)  
				+':' + right(jsch.next_run_time,2) as char (8))
	END AS NextRunTime
FROM ReportServer.dbo.ReportSchedule a 
        LEFT OUTER JOIN ReportServer.dbo.ReportSchedule c
            ON a.ScheduleID = c.ScheduleID
        LEFT OUTER JOIN ReportServer.dbo.Subscriptions d
            ON c.SubscriptionID = d.SubscriptionID
        LEFT OUTER JOIN ReportServer.dbo.[Catalog] e
            ON d.report_oid = e.itemid
		LEFT OUTER JOIN msdb.dbo.sysjobs j
			ON CAST(a.ScheduleID AS VARCHAR(100)) = j.name
		LEFT OUTER JOIN msdb.dbo.sysjobschedules jsch
			ON j.job_id = jsch.job_id
		LEFT OUTER JOIN msdb.dbo.sysschedules sch
			ON sch.schedule_id = jsch.schedule_id
-- to find specific report last status -- WHERE e.name = 'Usage Stats'
-- to find failed status --WHERE -- LastStatus <> 'Completed Data Refresh'
-- or for a specific SQL Agent Job --WHERE
--b.name = '9B7BED26-A81C-479C-A23B-016C9E05E760' -- NOTE you have to look in subscription history for any error messages/details and decode the JSON response (good luck with that!)

Thnaks a lot, that's worked as a charm! Just rerplaes ReportServer with my PowerBI DB in ReportServer.dbo.ReportSchedule.

Gday, 

 

This is great. Not sure why there is a self join in there... it was giving me duplicate results:

 

FROM ReportServer.dbo.ReportSchedule a 
        LEFT OUTER JOIN ReportServer.dbo.ReportSchedule c
            ON a.ScheduleID = c.ScheduleID

This was brilliant thank you!

 

It took me such a long time to reply because I do not have database level access to this server, and so I had to wait for my IT team to reate a view on another server I do have databse access that presents this data.  And as it turned out, I ended up waiting until we hired a new DBA, and that ended up being one of his first jobs...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.