And Now a Couple of Job Queries

By Lori Brown | Helpful Scripts

Apr 18

Hooray!!  We finally have our 70-764 and 70-765 certification exams done and can turn our attention back to blogging more regularly.  Those exams were pretty tough mostly because there was a lot of information that had to be memorized since there are always aspects of SQL that we don’t regularly use and there is no access to internet searches during the exam.  All of us (or maybe it’s just me) are by now so used to having access to SQL help and detail information on how to find things using the internet that it seems to me that exams that cover such a large subject like SQL will likely become either more general or even more specialized.  I just don’t know anyone who has managed to memorize all of the DMV’s with their columns and input parameters along with all of the SQL PowerShell commandlets with their input parameters that apply.  Okay….rant over and on to the fun stuff!

I was recently working with one of our developers on an upgrade project where we had to move a boatload of jobs to the new SQL instance, many of which call SSIS packages stored in msdb.  Since we are moving up to SQL 2016 in this particular project, we wanted to store packages in the SSIS Catalog to take advantage of it’s flexibility.  This meant that my developer needed a query that would give him some info on jobs that were actually being actively run and were candidates for move.  They also wanted to find the jobs that called SSIS packages so that they could focus their work in the right places.

Here is what I came up with.  Hey….since he liked it, I thought you might too.

USE msdb
GO

-- Just job info
SELECT j.name AS JobName, 
	(CASE WHEN j.[enabled] = 1 THEN 'Job Enabled' ELSE 'Job Disabled' END) AS JobEnabledDisabled, 
	(CASE WHEN ss.[enabled] = 1 THEN 'Schedule Enabled' WHEN ss.[enabled] = 0 THEN 'Schedule Disabled' ELSE 'Not Scheduled' END) AS JobScheduleEnabledDisabled, 
	s.last_run_date AS JobLastRunDate,
	s.last_run_time AS JobLastRunTime,
	(CASE WHEN s.last_run_outcome = 0 THEN 'Failed' WHEN s.last_run_outcome = 1 THEN 'Sucessful' WHEN s.last_run_outcome = 0 THEN 'Cancelled' END) AS JobLastOutcome,
	js.next_run_date AS JobNextRunDate, 
	js.next_run_time AS JobNextRunTime
FROM sysjobs j 
JOIN sysjobsteps s ON j.job_id = s.job_id
LEFT JOIN sysjobschedules js ON j.job_id = js.job_id
LEFT JOIN sysschedules ss ON js.schedule_id = ss.schedule_id
WHERE j.enabled = 1 -- 1= enabled, 0 = disabled
	--AND j.name LIKE 'Index%'  -- filter on job name
ORDER BY j.name


--  Job step with command
SELECT j.name AS JobName, 
	s.step_id AS JobStepID,
	s.step_name AS JobStepName, 
	(CASE WHEN j.[enabled] = 1 THEN 'Job Enabled' ELSE 'Job Disabled' END) AS JobEnabledDisabled, 
	(CASE WHEN ss.[enabled] = 1 THEN 'Schedule Enabled' WHEN ss.[enabled] = 0 THEN 'Schedule Disabled' ELSE 'Not Scheduled' END) AS JobScheduleEnabledDisabled, 
	--js.next_run_date AS JobNextRunDate, 
	--js.next_run_time AS JobNextRunTime,
	c.name AS JobCategory,
	s.subsystem AS JobSubsystem, 
	s.command AS JobStepCommand
FROM sysjobs j 
JOIN sysjobsteps s ON j.job_id = s.job_id
JOIN syscategories c ON j.category_id = c.category_id
LEFT JOIN sysjobschedules js ON j.job_id = js.job_id
LEFT JOIN sysschedules ss ON js.schedule_id = ss.schedule_id
WHERE j.enabled = 1 -- 1= enabled, 0 = disabled
	--AND j.name LIKE 'Index%'  -- filter on job name
	--AND c.name LIKE '%Maint%'  -- filter on category name
	--AND s.subsystem = 'CmdExec'  -- TSQL, CmdExec, Snapshot, LogReader, Distribution, Merge, QueueReader, ANALYSISQUERY, ANALYSISCOMMAND, SSIS, PowerShell
ORDER BY j.name, s.step_id
GO

I have provided a few ways to filter info if you need it.  The results are pretty straightforward…

You can run each query independently if you like.  As usual, feel free to add to these and make them your own.  But, most importantly, enjoy.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

Follow

About the Author

Lori is an avid runner, cyclist and SQL enthusiast. She has been working for SQLRX for 10 years and has been working with SQL in general for 20 years. Yup...she is an old hand at this stuff.

>