March 31, 2013 at 1:59 pm
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
ONjs.job_id = j.job_id
JOIN master.dbo.sysservers s
ONs.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?
March 31, 2013 at 2:22 pm
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/
March 31, 2013 at 2:43 pm
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
March 31, 2013 at 2:49 pm
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.
March 31, 2013 at 2:52 pm
Check your msdb data for last_run_date and last_run_time values of 0.
March 31, 2013 at 2:54 pm
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
ONjs.job_id = j.job_id
JOIN master.dbo.sysservers s
ONs.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.
April 1, 2013 at 6:25 am
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 JOINdbo.sysjobsteps jsONjs.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/
April 1, 2013 at 7:09 am
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 JOINdbo.sysjobsteps jsONjs.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.
April 1, 2013 at 10:29 am
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
ONh.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
ONh.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
ONh.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.
April 1, 2013 at 11:07 am
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/
April 1, 2013 at 11:29 am
Welsh Corgi (4/1/2013)
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.
Yes, and the code I posted does that. I have compared the output for your code to the output from mine and it is identical on my system.
The table msdb.dbo.sysjobhistory contains the history for each job step.
And adding a WHERE clause to the query should not be a problem.
April 1, 2013 at 11:34 am
You know I just realized that and I was going to edit my post.
Thank you!
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/
Viewing 12 posts - 31 through 41 (of 41 total)
You must be logged in to reply to this topic. Login to reply