SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Script to calculate when a job ended.


Script to calculate when a job ended.

Author
Message
Welsh Corgi
Welsh Corgi
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10079 Visits: 4894
I need to alter the script listed below to calculate the DateTime that the Job completed and sort on the derived column in descending order.


select job_name, MAX(run_datetime) AS run_datetime, MAX(run_duration) AS run_duration

from
(
select job_name, run_datetime,
SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
SUBSTRING(run_duration, 5, 2) AS run_duration
from
(
select DISTINCT
j.name as job_name,
run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +
(run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)
from msdb..sysjobhistory h
inner join msdb..sysjobs j
on h.job_id = j.job_id
WHERE j.name NOT LIKE 'Backup%'
AND j.name NOT LIKE 'Cleanup%'
AND j.name NOT LIKE 'Shrink%'
AND j.name <> 'WMI_Disk_Space_Notification'
AND j.name <> 'syspolicy_purge_history'
) t
) t
WHERE run_dateTime >= '2013-03-09' AND run_dateTime < '2013-03-10'
GROUP BY job_name
order by job_name, run_datetime




In addition to displaying the job_name, run_datetime and duration I want to deplay and sort by the Job_ENd_DateTime.

Any help would be greatly appreciated.

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/
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38893 Visits: 38508
Since some of us do not necessarily have a similar environment as you, it may be helpful if you created temporary tables and populated it with data from your msdb database for us to work with.

A little hard to work on your code with no data to test against. Won't know if what we did was what you are actually trying to accomplish.

From your own signature block:


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



Cool
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)
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38893 Visits: 38508
Here, I will even help you start. Here is a script to create working copies of the two msdb tables used in your query. All you need to do is add the appropriate INSERT statements to populate the tables with sample data.



/****** Object: Table [dbo].[wrksysjobhistory] Script Date: 3/10/2013 1:19:43 PM ******/
if object_id('dbo.wrksysjobhistory') is not null
DROP TABLE [dbo].[wrksysjobhistory]
GO

/****** Object: Table [dbo].[wrksysjobhistory] Script Date: 3/10/2013 1:19:43 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[wrksysjobhistory](
[instance_id] [int] IDENTITY(1,1) NOT NULL,
[job_id] [uniqueidentifier] NOT NULL,
[step_id] [int] NOT NULL,
[step_name] [sysname] NOT NULL,
[sql_message_id] [int] NOT NULL,
[sql_severity] [int] NOT NULL,
[message] [nvarchar](4000) NULL,
[run_status] [int] NOT NULL,
[run_date] [int] NOT NULL,
[run_time] [int] NOT NULL,
[run_duration] [int] NOT NULL,
[operator_id_emailed] [int] NOT NULL,
[operator_id_netsent] [int] NOT NULL,
[operator_id_paged] [int] NOT NULL,
[retries_attempted] [int] NOT NULL,
[server] [sysname] NOT NULL
)

GO

/****** Object: Table [dbo].[wrksysjobs] Script Date: 3/10/2013 1:19:57 PM ******/
if object_id('dbo.wrksysjobs') is not null
DROP TABLE [dbo].[wrksysjobs]
GO

/****** Object: Table [dbo].[wrksysjobs] Script Date: 3/10/2013 1:19:57 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[wrksysjobs](
[job_id] [uniqueidentifier] NOT NULL,
[originating_server_id] [int] NOT NULL,
[name] [sysname] NOT NULL,
[enabled] [tinyint] NOT NULL,
[description] [nvarchar](512) NULL,
[start_step_id] [int] NOT NULL,
[category_id] [int] NOT NULL,
[owner_sid] [varbinary](85) NOT NULL,
[notify_level_eventlog] [int] NOT NULL,
[notify_level_email] [int] NOT NULL,
[notify_level_netsend] [int] NOT NULL,
[notify_level_page] [int] NOT NULL,
[notify_email_operator_id] [int] NOT NULL,
[notify_netsend_operator_id] [int] NOT NULL,
[notify_page_operator_id] [int] NOT NULL,
[delete_level] [int] NOT NULL,
[date_created] [datetime] NOT NULL,
[date_modified] [datetime] NOT NULL,
[version_number] [int] NOT NULL
)

GO

SET ANSI_PADDING OFF
GO




Cool
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)
Welsh Corgi
Welsh Corgi
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10079 Visits: 4894
Lynn Pettis (3/10/2013)
Since some of us do not necessarily have a similar environment as you, it may be helpful if you created temporary tables and populated it with data from your msdb database for us to work with.

A little hard to work on your code with no data to test against. Won't know if what we did was what you are actually trying to accomplish.

From your own signature block:


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



You know it is a system table why do I need to create a system table?w00t

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/
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38893 Visits: 38508
Welsh Corgi (3/10/2013)
Lynn Pettis (3/10/2013)
Since some of us do not necessarily have a similar environment as you, it may be helpful if you created temporary tables and populated it with data from your msdb database for us to work with.

A little hard to work on your code with no data to test against. Won't know if what we did was what you are actually trying to accomplish.

From your own signature block:


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



You know it is a system table why do I need to create a system table?w00t

Thank you.






How about for the fact some of us don't necessarily have data in those tables? Most of my msdb database tables are empty, I have no scheduled jobs running, nor do I have any jobs that may even look like yours. My VM at work is the same, no jobs, no data. As for the production systems, on secure networks in theater so no access.

I also don't feel compelled to develop test data to help you do your work. I am more than happy to help, but give us something to work with.

Cool
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)
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31830 Visits: 18550
Try this

If not exactly what you need, it should get you some ideas at least.

select job_name, MAX(run_datetime) AS run_datetime, MAX(run_duration) AS run_duration,t.stop_execution_date

from
(
select job_name, run_datetime,t.stop_execution_date,
SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
SUBSTRING(run_duration, 5, 2) AS run_duration
from
(
select DISTINCT
j.name as job_name,ja.stop_execution_date,ja.start_execution_date AS run_datetime,
--run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +
-- (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)
from msdb.dbo.sysjobhistory h
inner join msdb..sysjobs j
on h.job_id = j.job_id
CROSS APPLY (SELECT MAX(stop_execution_date) AS stop_execution_date,MAX(start_execution_date) AS Start_execution_date
FROM msdb.dbo.sysjobactivity ja
WHERE ja.job_id = j.job_id) ja
WHERE j.name NOT LIKE 'Backup%'
AND j.name NOT LIKE 'Cleanup%'
AND j.name NOT LIKE 'Shrink%'
AND j.name <> 'WMI_Disk_Space_Notification'
AND j.name <> 'syspolicy_purge_history'
) t
) t
WHERE run_dateTime >= '2013-03-08' AND run_dateTime <= '2013-03-11'
GROUP BY job_name,t.stop_execution_date
order by stop_execution_date DESC,job_name, run_datetime





Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

David Burrows
David Burrows
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9501 Visits: 9740

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]
FROM msdb..sysjobhistory h
INNER JOIN msdb..sysjobs j ON h.job_id = j.job_id
WHERE j.name NOT LIKE 'Backup%'
AND j.name NOT LIKE 'Cleanup%'
AND j.name NOT LIKE 'Shrink%'
AND j.name <> 'WMI_Disk_Space_Notification'
AND j.name <> 'syspolicy_purge_history'
ORDER BY run_endtime DESC



Note that this uses an undocumented system function


Far away is close at hand in the images of elsewhere.

Anon.


Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84555 Visits: 41064
David Burrows (3/11/2013)

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]
FROM msdb..sysjobhistory h
INNER JOIN msdb..sysjobs j ON h.job_id = j.job_id
WHERE j.name NOT LIKE 'Backup%'
AND j.name NOT LIKE 'Cleanup%'
AND j.name NOT LIKE 'Shrink%'
AND j.name <> 'WMI_Disk_Space_Notification'
AND j.name <> 'syspolicy_purge_history'
ORDER BY run_endtime DESC



Note that this uses an undocumented system function


Thank you Mr. Burrows. I learned something new. In the past, I've written just such a function that apparently works the same way. I don't need to do that anymore.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
David Burrows
David Burrows
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9501 Visits: 9740
Jeff Moden (3/11/2013)
Thank you Mr. Burrows. I learned something new. In the past, I've written just such a function that apparently works the same way. I don't need to do that anymore.


Thanks Jeff, coming from you that is quite a compliment Blush, I feel humbled, you made my day :-)


Far away is close at hand in the images of elsewhere.

Anon.


TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14170 Visits: 12197
David Burrows (3/11/2013)

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]
FROM msdb..sysjobhistory h
INNER JOIN msdb..sysjobs j ON h.job_id = j.job_id
WHERE j.name NOT LIKE 'Backup%'
AND j.name NOT LIKE 'Cleanup%'
AND j.name NOT LIKE 'Shrink%'
AND j.name <> 'WMI_Disk_Space_Notification'
AND j.name <> 'syspolicy_purge_history'
ORDER BY run_endtime DESC



Note that this uses an undocumented system function

That looks nice - I could have done with that undocumented function way back when, it would have saved me writing it.
Isn't there still an issue that this gives data for individual jobs steps, as well as for whole jobs, and doesn't identify which is which? If time for the job is required, shouldn't the where clause include the condition step_id = 0, and if the time for individual steps as well as for the whole job is required shouldn't step_id be included in both the group by and select clauses? And if only steps and not whole jobs are reuired, those additions to the group by and select clauses plus a step_id > 0 condition in the where clause?

Tom

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search