Using SQL to Query up Your Agent Job Details

If you are a SQL DB admin the odds are you have a bunch of SQL agent jobs that run throughout the day. If you haven’t yet, at some point you will want to audit the specifics of those jobs and when they are running. The below script will query up the schedule details of your SQL Agent jobs. The script will also suggest SQL to change the job owner and schedule owner of any of your jobs/schedules that are owned by user logins. If you’ve not been bit by that in the past it is important to note that if a user login owns a job and that login is removed or denied access to connect to the database engine the job will cease to run correctly. Thus, I recommend setting the owner for all your jobs to “sa” to make sure that your jobs continue to run in perpetuity.

use msdb

select
	J.job_id,
	J.name 'job_name',
	case 
		when J.notify_level_email = '0' then 'Never'
		when J.notify_level_email = '1' then 'When the job succeeds'
		when J.notify_level_email = '2' then 'When the job fails'
		when J.notify_level_email = '3' then 'Whenever the job completes (regardless of the job outcome)'
		else cast(J.notify_level_email as varchar)
	end notify_level_email,
	O.name 'notify_name',
	O.email_address 'notify_email_address',
	SUSER_SNAME(J.owner_sid) 'job_owner',
	case
		when SUSER_SNAME(J.owner_sid) <> 'sa' and SUSER_SNAME(J.owner_sid) not like '##%' then 'exec dbo.sp_update_job @job_id=''' + cast(J.job_id as varchar(255)) + ''', @owner_login_name = ''sa'''
	end as job_owner_fix,
	J.enabled 'job_enabled',
	S.schedule_id,
	S.name 'schedule_name',
	S.enabled 'schedule_enabled',
	SUSER_SNAME(S.owner_sid) 'schedule_owner',
	case
		when SUSER_SNAME(S.owner_sid) <> 'sa' and SUSER_SNAME(S.owner_sid) not like '##%' then 'exec dbo.sp_update_schedule @schedule_id=' + cast(S.schedule_id as varchar) + ', @owner_login_name = ''sa'''
	end as schedule_owner_fix,
	case
		when S.freq_type = 1 then 'Once'
		when S.freq_type = 4 then 'Daily'
		when S.freq_type = 8 then 'Weekly'
		when S.freq_type = 16 then 'Monthly'
		when S.freq_type = 32 then 'Monthly relative'
		when S.freq_type = 64 then 'When SQLServer Agent starts'
	end frequency,
	left(convert(varchar, dbo.agent_datetime('19000101', S.active_start_time), 114), 8) start_time,
	case
		when S.freq_subday_interval = 0 then null
		else left(convert(varchar, dbo.agent_datetime('19000101', S.active_end_time), 114), 8)
	end end_time,
	case
		when S.freq_subday_interval = 0 then 'Once'
		else cast('Every ' + right(S.freq_subday_interval, 2) + ' ' +
			case
				when S.freq_subday_type = 1 then 'Once'
				when S.freq_subday_type = 4 then 'Minutes'
				when S.freq_subday_type = 8 then 'Hours'
			end as char(16))
	end as 'sub_frequency',
	case
		when S.freq_type = 8 and S.freq_interval & 1 = 1 then 'x'
		else null
	end sunday,
	case
		when S.freq_type = 8 and S.freq_interval & 2 = 2 then 'x'
		else null
	end monday,
	case
		when S.freq_type = 8 and S.freq_interval & 4 = 4 then 'x'
		else null
	end tuesday,
	case
		when S.freq_type = 8 and S.freq_interval & 8 = 8 then 'x'
		else null
	end wednesday,
	case
		when S.freq_type = 8 and S.freq_interval & 16 = 16 then 'x'
		else null
	end thursday,
	case
		when S.freq_type = 8 and S.freq_interval & 32 = 32 then 'x'
		else null
	end friday,
	case
		when S.freq_type = 8 and S.freq_interval & 64 = 64 then 'x'
		else null
	end saturday,
	convert(varchar, dbo.agent_datetime(S.active_start_date, '000000000'), 101) duration_start,
	case
		when S.active_end_date = '99991231' then null
		else convert(varchar, dbo.agent_datetime(S.active_end_date, '000000000'), 101)
	end duration_end
from msdb.dbo.sysjobs J
left outer join msdb.dbo.sysoperators O on J.notify_email_operator_id = O.id
left outer join msdb.dbo.sysjobschedules JS on J.job_id = JS.job_id
left outer join msdb.dbo.sysschedules S on JS.schedule_id = S.schedule_id
order by
	J.name