Check last time each job has been executed on DB servers

Dear all,

we want to check when is the last time a SQL job runs and see if any job hasn't been run for a while, then we disable that to clean up the mess.

we use this script to list all job ran:

;WITH jobListCTE as
(
  SELECT j.name as job_name, 
         msdb.dbo.agent_datetime(run_date, run_time) AS run_datetime,
         RIGHT('000000' + CONVERT(varchar(6), run_duration), 6) AS run_duration,
         message
    FROM msdb..sysjobhistory h
   INNER JOIN msdb..sysjobs j ON h.job_id = j.job_id
   WHERE h.step_name = '(Job outcome)'
) 
SELECT job_name as [JobStep], 
       run_datetime as [StartDateTime], 
       SUBSTRING(run_duration, 1, 2) + ':' + 
       SUBSTRING(run_duration, 3, 2) + ':' + 
       SUBSTRING(run_duration, 5, 2) as [Duration], 
       message
  FROM jobListCTE
 ORDER BY run_datetime DESC, job_name;

Open in new window


but this script seems return the same job a lot of times (it returns all history of a job if it still in the history log?), what we want is to only list the last run time and result of a job, any idea?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargBusiness Intelligence DeveloperCommented:
HI,

Hope this query will help you

;WITH jobListCTE as
(
  SELECT j.name as job_name, 
         msdb.dbo.agent_datetime(run_date, run_time) AS run_datetime,
         RIGHT('000000' + CONVERT(varchar(6), run_duration), 6) AS run_duration,
         message
    FROM msdb..sysjobhistory h
   INNER JOIN msdb..sysjobs j ON h.job_id = j.job_id
   WHERE h.step_name = '(Job outcome)'
) 
,CTE2 as
(
SELECT job_name as [JobStep], 
       run_datetime as [StartDateTime], 
       SUBSTRING(run_duration, 1, 2) + ':' + 
       SUBSTRING(run_duration, 3, 2) + ':' + 
       SUBSTRING(run_duration, 5, 2) as [Duration], 
       message
  FROM jobListCTE
)

SELECT [JobStep],MAX(StartDateTime)LastRun, 
(SELECT message FROM CTE2 T WHERE T.StartDateTime = MAX(T1.StartDateTime)) FROM CTE2 T1
GROUP BY [JobStep]

Open in new window

0
marrowyungSenior Technical architecture (Data)Author Commented:
it gave this error:

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
0
Vikas GargBusiness Intelligence DeveloperCommented:
Running perfect in my environment,

However you can try this

;WITH jobListCTE as
(
  SELECT j.name as job_name, 
         msdb.dbo.agent_datetime(run_date, run_time) AS run_datetime,
         RIGHT('000000' + CONVERT(varchar(6), run_duration), 6) AS run_duration,
         message
    FROM msdb..sysjobhistory h
   INNER JOIN msdb..sysjobs j ON h.job_id = j.job_id
   WHERE h.step_name = '(Job outcome)'
) 
,CTE2 as
(
SELECT job_name as [JobStep], 
       run_datetime as [StartDateTime], 
       SUBSTRING(run_duration, 1, 2) + ':' + 
       SUBSTRING(run_duration, 3, 2) + ':' + 
       SUBSTRING(run_duration, 5, 2) as [Duration], 
       message
	   ,ROW_NUMBER()over(partition by job_name order by run_datetime desc) RN
  FROM jobListCTE
)

SELECT * FROM CTE2 T1
WHERE RN = 1

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

marrowyungSenior Technical architecture (Data)Author Commented:
"Running perfect in my environment,"

what SQL server you are using ? 2012
?

I am running SQL 2008 with SP4/3

it result is ok this time,
1) So it will only list the jobs which is not disabled or all or has schedule ?
2) RN means ran ?
3) it don't return the error of the job which is failed in detail.
4) can we modify it so that it shows failed step if it failed?
0
Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

You gave the specific requirement which is the result of above query.

RN is just for the reference and like a filter condition.

And if you want the failed one it might be the reason that it is not the last one which you wanted in your question.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"RN is just for the reference and like a filter condition."

this RN means filter out all job so that all jobs only exists once?

I added this one:

and h.run_status = 1 

Open in new window


so I just change this bit and then I can list out all failed OR all success job in single screen.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
This gives you a list of all enabled jobs and when they last ran:
SELECT h.server, j.name, MAX(h.run_date) AS LastTimeRun
FROM sysjobs j
	INNER JOIN sysjobhistory h ON j.job_id=h.job_id
WHERE j.enabled=1 -- only check enabled jobs
GROUP BY h.server, j.name
ORDER BY 3

Open in new window

0
Vikas GargBusiness Intelligence DeveloperCommented:
HI,

I have added this RN to return the last record for the Job Log.

It will return the Last Log from the History of the Job Log
0
marrowyungSenior Technical architecture (Data)Author Commented:
ok, only the latest information you mean ? last run ?
0
Vikas GargBusiness Intelligence DeveloperCommented:
Yes,

Since you mentioned this

"what we want is to only list the last run time and result of a job"
0
marrowyungSenior Technical architecture (Data)Author Commented:
ok, that RN number mean this, I think filter by last date and time will be enough.
0
Vikas GargBusiness Intelligence DeveloperCommented:
So,
is it ok or anything else to be clarified ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
seems good, I think the most appropriate way to show out the result is last 3 times of runs, is it possible?

one more parameter for that?
0
Vikas GargBusiness Intelligence DeveloperCommented:
Hello,

If you want to see last three runs of job then

Just replace the part of the solution I gave

SELECT * FROM CTE2 T1
WHERE RN = 1

with this

SELECT * FROM CTE2 T1
WHERE RN <= 3
0
marrowyungSenior Technical architecture (Data)Author Commented:
it should show the last 3 times of runs of the SAME JOB, right?
0
Vikas GargBusiness Intelligence DeveloperCommented:
Yes,

All the jobs (Last three runs)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
Vitor Montalvão,

the script you gave me only show last run date, right?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Indeed. Only the last time for each job.
0
marrowyungSenior Technical architecture (Data)Author Commented:
I want to give you all mark first as you all helping me well.
0
marrowyungSenior Technical architecture (Data)Author Commented:
Vikas,

can the output of the script save to a table for analysis later?
0
Vikas GargBusiness Intelligence DeveloperCommented:
Hello,

You can create table as per the result set

INSERT INTO JobTrack
SELECT * FROM CTE2 T1 WHERE RN = 1

or can create view for the same
0
marrowyungSenior Technical architecture (Data)Author Commented:
but as the CTE get result from msdb, we can just simply enlarge the SQL job history log size and day it can store and then make use of the msdb table, is better than create one more table for these information:

log history size
right ?

the log setting can allow more job history retain in msdb ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
I recall one last thing (not from steve jobs,:)), anyway to include in the script so that we know who execute the script ? the message field should show this but in the final result it don't .

but how can only parse the user name show in the "executed as users: xxx" to a separate column ?

the start data and time can only report the DB's windows system's time ?
 
 can we display the job description too  ?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.