SQL Server Jobs Execution Status



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’
[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 .. 😛

Roopesh Babu V


  1. com 20 E2 AD 90 20Bloedverdunners 20En 20Viagra 20 20Viagra 20Feminino 20Engorda viagra feminino engorda John Landgraf, CEO of FX Networks and a major fan of Гў normal dose of lasix Currently, athletes and their administrators know that they can experiment freely with any agent that is not on the WADA banned list


Please enter your comment!
Please enter your name here

51 − 46 =