Friends,
When ever you are working with sql server agent and dealing with lots of jobs then you need the below given query. The query given below will give the status of each and every job. This also c an be provided as VALUE ADD(Which will impress client) to client.
Generally we configure to E-Mail notification which says whether a JOB is successful or failed but the problem is “You have to check thousands of mails to get status of all jobs that run in a day”. Using the below query we can get the info about all the jobs and if you can provide a GUI for the same then it will become a VALUE ADD to client ..
SELECT [JobName] = JOB.name,
[Step] = HISTORY.step_id,
[StepName] = HISTORY.step_name,
[Message] = HISTORY.message,
[Status] = CASE WHEN HISTORY.run_status = 0 THEN ‘Failed’
WHEN HISTORY.run_status = 1 THEN ‘Succeeded’
WHEN HISTORY.run_status = 2 THEN ‘Retry’
WHEN HISTORY.run_status = 3 THEN ‘Canceled’
END,
[RunDate] = HISTORY.run_date,
[RunTime] = HISTORY.run_time,
[Duration] = HISTORY.run_duration
FROM msdb..sysjobs JOB
INNER JOIN msdb..sysjobhistory HISTORY ON HISTORY.job_id = JOB.job_id
WHERE HISTORY.run_date=convert(varchar,getdate(),112)
ORDER BY HISTORY.run_date, HISTORY.run_time
Try running this query and see the result. Remember that you need to run the query in MSDB database.
This is it .. As simple as this .. Hope this works .. 😛
Regards,
Roopesh Babu V
