April 20, 2015 at 10:24 am
I am trying to convert the @runtime to hh/mm/ss format as it's currently printing milliseconds. Please help
Declare @runtime varchar(100)
Select @runtime = sum(run_duration)
From msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
where j.name = 'Accounts_From_App03'
and run_date = CONVERT(varchar(8), getdate(), 112)
Declare @Body varchar(100)
Set @Body = 'The Job completed successfully. Duration:' + @runtime
exec master.dbo.sp_SQLNotify 'App02@email.com', 'team@email.com', 'Scheduled Job Success' ,@Body
April 20, 2015 at 10:51 am
run_duration is not in milliseconds.
This value 20457 translates to 02:04:57.
The code below should get you started.
isnull(CASE len(run_duration)
WHEN 1
THEN cast('00:00:0' + cast(run_duration AS CHAR) AS CHAR(8))
WHEN 2
THEN cast('00:00:' + cast(run_duration AS CHAR) AS CHAR(8))
WHEN 3
THEN cast('00:0' + Left(right(run_duration, 3), 1) + ':' + right(run_duration, 2) AS CHAR(8))
WHEN 4
THEN cast('00:' + Left(right(run_duration, 4), 2) + ':' + right(run_duration, 2) AS CHAR(8))
WHEN 5
THEN cast('0' + Left(right(run_duration, 5), 1) + ':' + Left(right(run_duration, 4), 2) + ':' + right(run_duration, 2) AS CHAR(8))
WHEN 6
THEN cast(Left(right(run_duration, 6), 2) + ':' + Left(right(run_duration, 4), 2) + ':' + right(run_duration, 2) AS CHAR(8))
END, 'NA'),
run_duration
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 20, 2015 at 2:57 pm
I see a couple of other issues, besides formatting, with your code.
1) Step 0 is a summary for the entire job, including duration. So if you add step 0 and the actual step durations, you'll double the job time.
2) You state the job completed "successfully", but you don't actually verify the status of the job.
Maybe something in this code can help you out:
Declare @body varchar(100)
Select @body =
'The Job completed ' + Case When jh.run_status = 1 Then '' Else '!UN' End +
'successfully. Duration:' +
isnull(nullif(Cast(jh.run_duration / 10000 / 24 As varchar(5)), 0) + 'd ', '') +
isnull(nullif(Cast(jh.run_duration / 10000 % 24 As varchar(2)), 0) + 'h ', '') +
Cast(jh.run_duration % 10000 / 100 As varchar(2)) + 'm ' +
Cast(jh.run_duration % 100 As varchar(2)) + 's.'
From msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory jh ON j.job_id = jh.job_id
Where j.name = 'Accounts_From_App03'
And jh.run_date = CONVERT(varchar(8), getdate(), 112)
Set @body = Isnull(@body, '!Job "Accounts_From_App03" did not run at all on ' + Convert(varchar(10), Getdate(), 101) + '!')
Select @body
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 23, 2015 at 9:48 am
Thanks SScrazy. I really appreciate your help with this.
Now, I've stumbled on another issue. Since i'm including this the last step of the job here. I don't think i'll be getting the current job's completion time(runtime), it's pulling the most recently completed runtime. Lets say, the job runs daily..if I run the job (which includes the email notification), I will get the runtime from yesterday's run. I want to get the job run time from today.
April 23, 2015 at 10:09 am
Or you could use the MS scalar function in the MSDB database:
select job_id, msdb.dbo.agent_datetime(run_date,run_time) from msdb.dbo.sysjobhistory;
April 23, 2015 at 10:11 am
DOUBLE DOH! I just noticed I left of the step# check in the code; please add this to the WHERE clause:
AND jh.step_id = 0
That code is checking for the current day's date, as returned by GETDATE().
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 23, 2015 at 10:12 am
eedgar 45478 (4/23/2015)
Thanks SScrazy. I really appreciate your help with this.Now, I've stumbled on another issue. Since i'm including this the last step of the job here. I don't think i'll be getting the current job's completion time(runtime), it's pulling the most recently completed runtime. Lets say, the job runs daily..if I run the job (which includes the email notification), I will get the runtime from yesterday's run. I want to get the job run time from today.
If the job is still running couldn't you just use getdate() in your query?
April 23, 2015 at 10:37 am
still not getting the current runtime after the changes
Declare @body varchar(100)
Select @body ='accounts_from_app03' + Case When jh.run_status = 1 Then '' Else '!UN' End +
'SUCCESSFUL. Duration:' +
isnull(nullif(Cast(jh.run_duration / 10000 / 24 As varchar(5)), 0) + 'd ', '') +
isnull(nullif(Cast(jh.run_duration / 10000 % 24 As varchar(2)), 0) + 'h ', '') +
Cast(jh.run_duration % 10000 / 100 As varchar(2)) + 'm ' +
Cast(jh.run_duration % 100 As varchar(2)) + 's.'
From msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory jh ON j.job_id = jh.job_id
Where j.name = 'Accounts_from_app03' AND jh.step_id =0
And jh.run_date = CONVERT(varchar(8), getdate(), 112)
Set @body = Isnull(@body, '!Job "Accounts_from_app03" did not run at all on ' + Convert(varchar(10), Getdate(), 101) + '!')
exec master.dbo.sp_SQLNotify 'noreply@email.com', 'earl@email.com', @body, @Body
April 23, 2015 at 10:40 am
i'm getting !Job "Accounts_from_app03" did not run at all on 04/23/2015!
April 23, 2015 at 10:40 am
The step is not posted in sysjobhistory until it completes. If the job is still running, you will get the previous job.
If you need to get only the last job a given day, we would need to add a ROWCOUNT() to order by run_time DESC to get only the last completed job.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 23, 2015 at 10:42 am
If you remove the "AND jh.step_id =0" you can see the steps that have completed.
But "step 0" can't be posted until the whole job completes, since that is the job summary.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 23, 2015 at 10:43 am
yes, that's what I thought too. I don't believe we can get the runtime without having the job completes first.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply