Hi All,

 In SQL Server Agent (2012 Version )There are many(100) JOBS created. The Jobs are related all the the SQL and MSBI Stuffs. 

Like some jobs are calling SPs, Some are calling SSIS packages and Some are calling XMAL script to process the SSAS Cubes.

Lets say , in one of the JOB there are many Steps available like Step-1, Step-2 .... Step-n.

Some times All the JOBs gets succeed and some times , some of the JOBS gets failed.

Here , THE QUESTION IS:

IF any Job gets failed , how to get JOB name ,Step name and Error Description(Error Message).

As of now am using JOB Activity Monitor and if any jobs failed, then i will be going to respective JOB and right Click--> View History.

Note: if job gets failed some times am not getting complete Error Description. It simple say JOb has failed like that.

Please suggest what is the best method to get the exact Error Message.

Thanks and Regards,

Bala Kris

You may try and check if the below help you...

https://gallery.technet.microsoft.com/T-SQL-Script-Get-Job-231229b4

Hello,

Create Job notification if the job fails; see Notify an Operator of Job Status

You should query msdb.dbo.sysjobs and JObhistory. Refer to below query I easily found out with little search from here

SELECT
    ja.job_id,
    j.name AS job_name,
    ja.start_execution_date,      
    ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,
    Js.step_name
FROM msdb.dbo.sysjobactivity ja 
LEFT JOIN msdb.dbo.sysjobhistory jh 
    ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j 
ON ja.job_id = j.job_id
JOIN msdb.dbo.sysjobsteps js
    ON ja.job_id = js.job_id
    AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
AND start_execution_date is not null
AND stop_execution_date is null;