Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

Script to calculate when a job ended. Expand / Collapse
Author
Message
Posted Sunday, March 31, 2013 1:59 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:09 PM
Points: 20,744, Visits: 32,560
Welsh Corgi (3/31/2013)
Lynn Pettis (3/31/2013)
Welsh Corgi (3/31/2013)
Welsh Corgi (3/31/2013)
Lynn Pettis (3/31/2013)
Perhaps something more like this:


SELECT
j.job_id,
s.srvname,
j.name,
js.step_id,
js.command,
j.enabled ,
js.last_run_date,
js.last_Run_Time,
js.last_run_duration,
msdb.dbo.Agent_datetime(js.last_Run_Date, js.last_Run_Time) AS Run_Datetime,
dateadd(second, ((js.last_run_duration / 10000) * 3600) + (((js.last_run_duration / 100) % 60) * 60) + (js.last_run_duration % 60), msdb.dbo.Agent_datetime(js.last_Run_Date, js.last_Run_Time)) as EndRunDate
FROM
dbo.sysjobs j
JOIN dbo.sysjobsteps js
ON js.job_id = j.job_id
JOIN master.dbo.sysservers s
ON s.srvid = j.originating_server_id
WHERE
j.name = 'TestJob';




If you execute the code you will get the following error:


Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.


Thanks for you reply.



What job did you create?

What was in the execute of the step(s)?


You can't answer my questions or provide the information I request and you expect me to answer yours?

It doesn't matter what job I created or what I did in the job step(s). The job ran and put data in the tables that when accessed by your query with my added code to compute the End Date ran without problems.

This means there is more going on on your system and that we need to see some data from you in order to help answer your questions. I am more than willing to help, but you really need to help in that regard.



I asked you for that information so that I could replicate what you did .

Disregard my post

Thank you.


And I had asked you for information so I could mirror your environment in an effort to help you. Not so different, is it?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1437329
Posted Sunday, March 31, 2013 2:22 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:57 PM
Points: 4,242, Visits: 4,290
Lynn Pettis (3/31/2013)
I can run the code all day long, it won't tell me what the problem is if you don't show me. I have no idea what the expected results of the query are to compare the actual results.



Please feel free to not repond to any of my post.


For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1437330
Posted Sunday, March 31, 2013 2:43 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:09 PM
Points: 20,744, Visits: 32,560

USE [msdb]
GO

/****** Object: Job [TestJob] Script Date: 3/31/2013 2:42:38 PM ******/
EXEC msdb.dbo.sp_delete_job @job_id=N'775a2b1e-52c1-4d6f-b661-1bba00e0af47', @delete_unused_schedule=1
GO

/****** Object: Job [TestJob] Script Date: 3/31/2013 2:42:38 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 3/31/2013 2:42:38 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'TestJob',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'Sparta\lapettis', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [TestStep] Script Date: 3/31/2013 2:42:39 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestStep',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'select * from sys.databases',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO






Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1437331
Posted Sunday, March 31, 2013 2:49 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:09 PM
Points: 20,744, Visits: 32,560
Interesting. I drop and recreate my job then run the script I provided (sans the WHERE clause) without running the job first and I get the error message you posted. I run the job then run the script again (and again without the WHERE clause) and it works. Hmmm. I wonder what is going on.


Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1437332
Posted Sunday, March 31, 2013 2:52 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:09 PM
Points: 20,744, Visits: 32,560
Check your msdb data for last_run_date and last_run_time values of 0.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1437333
Posted Sunday, March 31, 2013 2:54 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:09 PM
Points: 20,744, Visits: 32,560
Try this:


SELECT
j.job_id,
s.srvname,
j.name,
js.step_id,
js.command,
j.enabled ,
js.last_run_date,
js.last_Run_Time,
js.last_run_duration,
msdb.dbo.Agent_datetime(js.last_Run_Date, js.last_Run_Time) AS Run_Datetime,
dateadd(second, ((js.last_run_duration / 10000) * 3600) + (((js.last_run_duration / 100) % 60) * 60) + (js.last_run_duration % 60), msdb.dbo.Agent_datetime(js.last_Run_Date, js.last_Run_Time)) as EndRunDate
FROM
dbo.sysjobs j
JOIN dbo.sysjobsteps js
ON js.job_id = j.job_id
JOIN master.dbo.sysservers s
ON s.srvid = j.originating_server_id
WHERE
js.last_run_date <> 0 and js.last_Run_Time <> 0
--j.name = 'TestJob';


Edit: Needed to uncomment the last two lines of the SELECT list.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1437334
Posted Monday, April 1, 2013 6:25 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:57 PM
Points: 4,242, Visits: 4,290
I came up with something that works.

SELECT DISTINCT j.Name                                                                                                                             AS Job_Name,
msdb.dbo.Agent_datetime(Run_Date, Run_Time) AS Run_Datetime,
Dateadd(second, Datediff(second, 0, msdb.dbo.Agent_datetime(19000101, Run_Duration)), msdb.dbo.Agent_datetime(Run_Date, Run_Time)) AS Run_Endtime,
Stuff(Stuff(RIGHT('000000'
+ Cast(Run_Duration AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':') AS Run_Duration,
CASE h.Run_Status
WHEN 0 THEN 'failed'
WHEN 1 THEN 'Succeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress'
END AS ExecutionStatus,
h.Message ,
h.Step_ID AS Step_ID
FROM msdb..Sysjobhistory h
INNER JOIN msdb..Sysjobs j ON h.Job_id = j.Job_id
INNER JOIN dbo.sysjobsteps js ON js.job_id = j.job_id
WHERE
j.name = 'Any Job Name';



For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1437417
Posted Monday, April 1, 2013 7:09 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:09 PM
Points: 20,744, Visits: 32,560
Welsh Corgi (4/1/2013)
I came up with something that works.

SELECT DISTINCT j.Name                                                                                                                             AS Job_Name,
msdb.dbo.Agent_datetime(Run_Date, Run_Time) AS Run_Datetime,
Dateadd(second, Datediff(second, 0, msdb.dbo.Agent_datetime(19000101, Run_Duration)), msdb.dbo.Agent_datetime(Run_Date, Run_Time)) AS Run_Endtime,
Stuff(Stuff(RIGHT('000000'
+ Cast(Run_Duration AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':') AS Run_Duration,
CASE h.Run_Status
WHEN 0 THEN 'failed'
WHEN 1 THEN 'Succeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress'
END AS ExecutionStatus,
h.Message ,
h.Step_ID AS Step_ID
FROM msdb..Sysjobhistory h
INNER JOIN msdb..Sysjobs j ON h.Job_id = j.Job_id
INNER JOIN dbo.sysjobsteps js ON js.job_id = j.job_id
WHERE
j.name = 'Any Job Name';



I hope you don't have any jobs that run 24 hours or longer.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1437427
Posted Monday, April 1, 2013 10:29 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:09 PM
Points: 20,744, Visits: 32,560
I know you don't want to see or hear from me but here is an alternative solution to yours:


SELECT
j.name,
msdb.dbo.Agent_datetime(h.run_date, h.run_time) AS Run_Datetime,
dateadd(second, ((h.run_duration / 10000) * 3600) + (((h.run_duration / 100) % 60) * 60) + (h.run_duration % 60), msdb.dbo.Agent_datetime(h.run_date, h.run_time)) as Run_EndDate,
Stuff(Stuff(RIGHT('000000' + Cast(h.run_duration AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':') as RunDuration,
CASE h.run_status
WHEN 0 THEN 'failed'
WHEN 1 THEN 'Succeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress'
END AS ExecutionStatus,
h.message,
h.step_id
FROM
msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobhistory h
ON h.job_id = j.job_id
order by
j.name,
h.run_date,
h.step_id



Test Harness used:


set nocount on;
GO
Print '------------ Welsh Corgi version ------------';
GO
--===== Create the timer and bit-bucket variables and start the timer.
DECLARE @BitBucket sysname; --< change or add variables needed here to eliminate display distortion

Declare @cpu_ int,
@lreads_ bigint,
@eMsec_ bigint,
@Reads_ bigint,
@Writes_ bigint;
declare @CpuMs int,
@LogRds bigint,
@Elapsed bigint,
@Reads bigint,
@Writes bigint;

dbcc freeproccache with no_infomsgs;
dbcc freesystemcache('ALL') with no_infomsgs;
dbcc dropcleanbuffers with no_infomsgs;


Select
@cpu_ = cpu_time
, @lreads_ = logical_reads
, @eMsec_ = total_elapsed_time
, @Reads_ = reads
, @Writes_ = writes
From
sys.dm_exec_requests
Where
session_id = @@spid;

---
SELECT DISTINCT
j.Name AS Job_Name,
msdb.dbo.Agent_datetime(Run_Date, Run_Time) AS Run_Datetime,
Dateadd(second, Datediff(second, 0, msdb.dbo.Agent_datetime(19000101, Run_Duration)), msdb.dbo.Agent_datetime(Run_Date, Run_Time)) AS Run_Endtime,
Stuff(Stuff(RIGHT('000000' + Cast(Run_Duration AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':') AS Run_Duration,
CASE h.Run_Status
WHEN 0 THEN 'failed'
WHEN 1 THEN 'Succeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress'
END AS ExecutionStatus,
h.Message ,
h.Step_ID AS Step_ID
FROM
msdb.dbo.Sysjobhistory h
INNER JOIN msdb.dbo.Sysjobs j
ON h.Job_id = j.Job_id
INNER JOIN msdb.dbo.sysjobsteps js
ON js.job_id = j.job_id
option (maxdop 1)
---

Select
@CpuMs = cpu_time - @cpu_
, @LogRds = logical_reads - @lreads_
, @Elapsed = total_elapsed_time - @eMsec_
, @Reads = reads - @Reads_
, @Writes = writes - @Writes_
From
sys.dm_exec_requests
Where
session_id = @@spid;
--===== Display the duration
PRINT 'Cold cache';
PRINT 'CPU(ms): ' + right(' ' + cast(@CpuMs as varchar(10)),10) +
' Logical Reads: ' + right(' ' + cast(@LogRds as varchar(10)),10) +
' Elapsed(ms): ' + right(' ' + cast(@Elapsed as varchar(10)),10) +
' Reads: ' + right(' ' + cast(@Reads as varchar(10)),10) +
' Writes: ' + right(' ' + cast(@Writes as varchar(10)),10);
go
PRINT 'Warm cache';
GO
Declare @cpu_ int,
@lreads_ bigint,
@eMsec_ bigint,
@Reads_ bigint,
@Writes_ bigint;
declare @CpuMs int,
@LogRds bigint,
@Elapsed bigint,
@Reads bigint,
@Writes bigint;

Select
@cpu_ = cpu_time
, @lreads_ = logical_reads
, @eMsec_ = total_elapsed_time
, @Reads_ = reads
, @Writes_ = writes
From
sys.dm_exec_requests
Where
session_id = @@spid;

---
SELECT DISTINCT
j.Name AS Job_Name,
msdb.dbo.Agent_datetime(Run_Date, Run_Time) AS Run_Datetime,
Dateadd(second, Datediff(second, 0, msdb.dbo.Agent_datetime(19000101, Run_Duration)), msdb.dbo.Agent_datetime(Run_Date, Run_Time)) AS Run_Endtime,
Stuff(Stuff(RIGHT('000000' + Cast(Run_Duration AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':') AS Run_Duration,
CASE h.Run_Status
WHEN 0 THEN 'failed'
WHEN 1 THEN 'Succeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress'
END AS ExecutionStatus,
h.Message ,
h.Step_ID AS Step_ID
FROM
msdb.dbo.Sysjobhistory h
INNER JOIN msdb.dbo.Sysjobs j
ON h.Job_id = j.Job_id
INNER JOIN msdb.dbo.sysjobsteps js
ON js.job_id = j.job_id
option (maxdop 1)
---

Select
@CpuMs = cpu_time - @cpu_
, @LogRds = logical_reads - @lreads_
, @Elapsed = total_elapsed_time - @eMsec_
, @Reads = reads - @Reads_
, @Writes = writes - @Writes_
From
sys.dm_exec_requests
Where
session_id = @@spid;
--===== Display the duration
PRINT 'CPU(ms): ' + right(' ' + cast(@CpuMs as varchar(10)),10) +
' Logical Reads: ' + right(' ' + cast(@LogRds as varchar(10)),10) +
' Elapsed(ms): ' + right(' ' + cast(@Elapsed as varchar(10)),10) +
' Reads: ' + right(' ' + cast(@Reads as varchar(10)),10) +
' Writes: ' + right(' ' + cast(@Writes as varchar(10)),10);

go 10

set nocount off;
GO

set nocount on;
GO
Print '------------ Alternative version ------------';
GO

--===== Create the timer and bit-bucket variables and start the timer.
DECLARE @BitBucket sysname; --< change or add variables needed here to eliminate display distortion

Declare @cpu_ int,
@lreads_ bigint,
@eMsec_ bigint,
@Reads_ bigint,
@Writes_ bigint;
declare @CpuMs int,
@LogRds bigint,
@Elapsed bigint,
@Reads bigint,
@Writes bigint;

dbcc freeproccache with no_infomsgs;
dbcc freesystemcache('ALL') with no_infomsgs;
dbcc dropcleanbuffers with no_infomsgs;


Select
@cpu_ = cpu_time
, @lreads_ = logical_reads
, @eMsec_ = total_elapsed_time
, @Reads_ = reads
, @Writes_ = writes
From
sys.dm_exec_requests
Where
session_id = @@spid;

---
SELECT
j.name,
msdb.dbo.Agent_datetime(h.run_date, h.run_time) AS Run_Datetime,
dateadd(second, ((h.run_duration / 10000) * 3600) + (((h.run_duration / 100) % 60) * 60) + (h.run_duration % 60), msdb.dbo.Agent_datetime(h.run_date, h.run_time)) as Run_EndDate,
Stuff(Stuff(RIGHT('000000' + Cast(h.run_duration AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':') as RunDuration,
CASE h.run_status
WHEN 0 THEN 'failed'
WHEN 1 THEN 'Succeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress'
END AS ExecutionStatus,
h.message,
h.step_id
FROM
msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobhistory h
ON h.job_id = j.job_id
order by
j.name,
h.run_date,
h.step_id
option (maxdop 1)
---

Select
@CpuMs = cpu_time - @cpu_
, @LogRds = logical_reads - @lreads_
, @Elapsed = total_elapsed_time - @eMsec_
, @Reads = reads - @Reads_
, @Writes = writes - @Writes_
From
sys.dm_exec_requests
Where
session_id = @@spid;
--===== Display the duration
PRINT 'Cold cache';
PRINT 'CPU(ms): ' + right(' ' + cast(@CpuMs as varchar(10)),10) +
' Logical Reads: ' + right(' ' + cast(@LogRds as varchar(10)),10) +
' Elapsed(ms): ' + right(' ' + cast(@Elapsed as varchar(10)),10) +
' Reads: ' + right(' ' + cast(@Reads as varchar(10)),10) +
' Writes: ' + right(' ' + cast(@Writes as varchar(10)),10);
go
PRINT 'Warm cache';
go

Declare @cpu_ int,
@lreads_ bigint,
@eMsec_ bigint,
@Reads_ bigint,
@Writes_ bigint;
declare @CpuMs int,
@LogRds bigint,
@Elapsed bigint,
@Reads bigint,
@Writes bigint;

Select
@cpu_ = cpu_time
, @lreads_ = logical_reads
, @eMsec_ = total_elapsed_time
, @Reads_ = reads
, @Writes_ = writes
From
sys.dm_exec_requests
Where
session_id = @@spid;

---
SELECT
j.name,
msdb.dbo.Agent_datetime(h.run_date, h.run_time) AS Run_Datetime,
dateadd(second, ((h.run_duration / 10000) * 3600) + (((h.run_duration / 100) % 60) * 60) + (h.run_duration % 60), msdb.dbo.Agent_datetime(h.run_date, h.run_time)) as Run_EndDate,
Stuff(Stuff(RIGHT('000000' + Cast(h.run_duration AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':') as RunDuration,
CASE h.run_status
WHEN 0 THEN 'failed'
WHEN 1 THEN 'Succeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress'
END AS ExecutionStatus,
h.message,
h.step_id
FROM
msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobhistory h
ON h.job_id = j.job_id
order by
j.name,
h.run_date,
h.step_id
option (maxdop 1)
---

Select
@CpuMs = cpu_time - @cpu_
, @LogRds = logical_reads - @lreads_
, @Elapsed = total_elapsed_time - @eMsec_
, @Reads = reads - @Reads_
, @Writes = writes - @Writes_
From
sys.dm_exec_requests
Where
session_id = @@spid;
--===== Display the duration
PRINT 'CPU(ms): ' + right(' ' + cast(@CpuMs as varchar(10)),10) +
' Logical Reads: ' + right(' ' + cast(@LogRds as varchar(10)),10) +
' Elapsed(ms): ' + right(' ' + cast(@Elapsed as varchar(10)),10) +
' Reads: ' + right(' ' + cast(@Reads as varchar(10)),10) +
' Writes: ' + right(' ' + cast(@Writes as varchar(10)),10);
go 10

set nocount off;
GO


Results on my system:


------------ Welsh Corgi version ------------
Cold cache
CPU(ms): 16 Logical Reads: 646 Elapsed(ms): 162 Reads: 247 Writes: 0
Warm cache
Beginning execution loop
CPU(ms): 0 Logical Reads: 441 Elapsed(ms): 8 Reads: 0 Writes: 0
CPU(ms): 0 Logical Reads: 441 Elapsed(ms): 7 Reads: 0 Writes: 0
CPU(ms): 15 Logical Reads: 441 Elapsed(ms): 7 Reads: 0 Writes: 0
CPU(ms): 16 Logical Reads: 441 Elapsed(ms): 7 Reads: 0 Writes: 0
CPU(ms): 16 Logical Reads: 441 Elapsed(ms): 8 Reads: 0 Writes: 0
CPU(ms): 0 Logical Reads: 441 Elapsed(ms): 8 Reads: 0 Writes: 0
CPU(ms): 0 Logical Reads: 441 Elapsed(ms): 7 Reads: 0 Writes: 0
CPU(ms): 0 Logical Reads: 441 Elapsed(ms): 8 Reads: 0 Writes: 0
CPU(ms): 15 Logical Reads: 441 Elapsed(ms): 8 Reads: 0 Writes: 0
CPU(ms): 0 Logical Reads: 441 Elapsed(ms): 7 Reads: 0 Writes: 0
Batch execution completed 10 times.
------------ Alternative version ------------
Cold cache
CPU(ms): 0 Logical Reads: 351 Elapsed(ms): 15 Reads: 207 Writes: 0
Warm cache
Beginning execution loop
CPU(ms): 0 Logical Reads: 225 Elapsed(ms): 2 Reads: 0 Writes: 0
CPU(ms): 0 Logical Reads: 225 Elapsed(ms): 2 Reads: 0 Writes: 0
CPU(ms): 0 Logical Reads: 225 Elapsed(ms): 2 Reads: 0 Writes: 0
CPU(ms): 0 Logical Reads: 225 Elapsed(ms): 2 Reads: 0 Writes: 0
CPU(ms): 0 Logical Reads: 225 Elapsed(ms): 1 Reads: 0 Writes: 0
CPU(ms): 0 Logical Reads: 225 Elapsed(ms): 1 Reads: 0 Writes: 0
CPU(ms): 0 Logical Reads: 225 Elapsed(ms): 2 Reads: 0 Writes: 0
CPU(ms): 0 Logical Reads: 225 Elapsed(ms): 1 Reads: 0 Writes: 0
CPU(ms): 0 Logical Reads: 225 Elapsed(ms): 2 Reads: 0 Writes: 0
CPU(ms): 0 Logical Reads: 225 Elapsed(ms): 2 Reads: 0 Writes: 0
Batch execution completed 10 times.





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1437520
Posted Monday, April 1, 2013 11:07 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:57 PM
Points: 4,242, Visits: 4,290
Well thank you. That is nice but I'm looking for is the Start Datetime, End Datetime and elapsed time for each step for a single job.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1437538
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse