How to get job failure log from sql server management studio 2014

Question

Post reply

How to get job failure log from sql server management studio 2014

jayabo

SSC Rookie

Points: 32
More actions
September 13, 2019 at 1:47 am

#3679688

Hi,
Currently, we are getting job failure alerts from sqlserver agent properties where we have specified the email address
to where notification comes whenever job fails, but we wanted the "job failure log" along with job failure to the
email.
We do not have access to joblog.
Open Sqlserver management studio 2014 under it, expand a serverlog node, expand Management, and click SQL Server Logs of failed job.
My question is how to get job log by writing a script.
Thanks in advance,
Regards,
Ms Radha

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

depending on what mean by "job log" you're going to have to get this from msdb database

dbo.sysjobhistory , maybe a few more tables, but I can't see a way to link this into sql agent automatically

you would have to add an addition bit of code to your stored procedure using a try catch block

begin try

…..do something

end try

begin catch

sp_send_dbmail …..

end catch

MVDBA

Lowell SSC Guru Points: 323504 More actions · Answer 2

here's a snippet i use:

this will return all failures since the given date(first of the year is my parameter)

you can tweak it from there, i think.

DECLARE @dt CHAR(8);
SET @dt=CONVERT(CHAR(8), DATEADD(yy, DATEDIFF(yy,0,getdate()), 0), 112)  --1/1/current year?
--SET @dt=CONVERT(CHAR(8), DATEADD(yy, DATEDIFF(yy,0,getdate()), 0), 112)  --midnight yesterday?

SELECT  
CONVERT(varchar(128),@@SERVERNAME) As Servername,
T1.step_name AS [Step Name],
SUBSTRING(T2.name,1,140) AS [SQL Job Name],
--msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
CAST(CONVERT(DATETIME,CAST(run_date AS CHAR(8)),101) AS CHAR(11)) AS [Failure Date],
msdb.dbo.agent_datetime(T1.run_date, T1.run_time) AS 'RunDateTime',
T1.run_duration StepDuration,
CASE T1.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress'
END AS ExecutionStatus,
T1.message AS [Error Message]
FROM
msdb..sysjobhistory T1 INNER JOIN msdb..sysjobs T2 ON T1.job_id = T2.job_id
WHERE
T1.run_status NOT IN (1, 4)
AND T1.step_id != 0
AND run_date >= @dt

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!