August 20, 2015 at 10:55 am
Hi ,
below output I am getting jobs for last 14 days (Server,Job_Name,run_status,Step_Name,output_file_name ) .I am planning get job failed in last 24 hours and also I am planning to FailedStepJob to my script.can any one please suggest
here is the script
SELECT Server,Job_Name,run_status,Step_Name,output_file_name FROM
(
SELECT Job.instance_id
,SysJobs.job_id
,SysJobs.name as 'JOB_NAME'
,SysJobSteps.step_name as 'STEP_NAME'
,Job.run_status
,Job.sql_message_id
,Job.sql_severity
,Job.message
,Job.exec_date
,Job.run_duration
,Job.server
,SysJobSteps.output_file_name
FROM (SELECT Instance.instance_id
,DBSysJobHistory.job_id
,DBSysJobHistory.step_id
,DBSysJobHistory.sql_message_id
,DBSysJobHistory.sql_severity
,DBSysJobHistory.message
,(CASE DBSysJobHistory.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'In progress'
END) as run_status
,((SUBSTRING(CAST(DBSysJobHistory.run_date AS VARCHAR(8)), 5, 2) + '/'
+ SUBSTRING(CAST(DBSysJobHistory.run_date AS VARCHAR(8)), 7, 2) + '/'
+ SUBSTRING(CAST(DBSysJobHistory.run_date AS VARCHAR(8)), 1, 4) + ' '
+ SUBSTRING((REPLICATE('0',6-LEN(CAST(DBSysJobHistory.run_time AS varchar)))
+ CAST(DBSysJobHistory.run_time AS VARCHAR)), 1, 2) + ':'
+ SUBSTRING((REPLICATE('0',6-LEN(CAST(DBSysJobHistory.run_time AS VARCHAR)))
+ CAST(DBSysJobHistory.run_time AS VARCHAR)), 3, 2) + ':'
+ SUBSTRING((REPLICATE('0',6-LEN(CAST(DBSysJobHistory.run_time as varchar)))
+ CAST(DBSysJobHistory.run_time AS VARCHAR)), 5, 2))) AS 'exec_date'
,DBSysJobHistory.run_duration
,DBSysJobHistory.retries_attempted
,DBSysJobHistory.server
FROM msdb.dbo.sysjobhistory DBSysJobHistory
JOIN (SELECT DBSysJobHistory.job_id
,DBSysJobHistory.step_id
,MAX(DBSysJobHistory.instance_id) as instance_id
FROM msdb.dbo.sysjobhistory DBSysJobHistory
GROUP BY DBSysJobHistory.job_id
,DBSysJobHistory.step_id
) AS Instance ON DBSysJobHistory.instance_id = Instance.instance_id
WHERE DBSysJobHistory.run_status <> 1
) AS Job
JOIN msdb.dbo.sysjobs SysJobs
ON (Job.job_id = SysJobs.job_id)
JOIN msdb.dbo.sysjobsteps SysJobSteps
ON (Job.job_id = SysJobSteps.job_id AND Job.step_id = SysJobSteps.step_id)
)A
WHERE exec_date >= dateadd( day, -14, getdate() )
August 20, 2015 at 11:14 am
Are you seriously asking how to change the query to return 1 day instead of 14? Do you even know how does your query work?
Also, I'm not sure what you mean by "I am planning to FailedStepJob to my script".
August 20, 2015 at 12:15 pm
It's filtering 14 days here:
WHERE exec_date >= dateadd( day, -14, getdate() )
So, change the -14 to -1 and you're pretty much finished if everything else is working for you.
I don't know what that other thing you're referring to is.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 20, 2015 at 7:07 pm
Thanks
I want to show the failed jobs step I'd in the result set.
How to add this to my script
August 20, 2015 at 11:28 pm
Maybe start here:
then incorporate it.
August 21, 2015 at 4:13 am
Arjun SreeVastsva (8/20/2015)
ThanksI want to show the failed jobs step I'd in the result set.
How to add this to my script
Did you create this script or did someone else? EDIT: I ask because this code is kind of complicated. I don't think you need so many subqueries for the data you end up with in your result set.
You might make your code easier to understand if you used a different table alias in your subqueries than you do in your main table calls. I might be misunderstanding your needs here because you already have the step name in your results. Is there something else about the step data you want in the results?
August 21, 2015 at 6:03 am
As others have pointed out, if you're working on this query, you need to understand how it works. After all, you're going to be the one supporting it. I doubt that "someone on the internet told me to do it like this" will fly with your boss is you "fix it" and it suddenly stops working.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply