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 10, 2013 8:31 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:03 AM
Points: 4,052, Visits: 4,192
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/

Post #1428985
Posted Sunday, March 10, 2013 12:56 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 6:31 PM
Points: 22,504, Visits: 30,217
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/




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 #1429007
Posted Sunday, March 10, 2013 1:25 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 6:31 PM
Points: 22,504, Visits: 30,217
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





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 #1429012
Posted Sunday, March 10, 2013 2:26 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:03 AM
Points: 4,052, Visits: 4,192
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?

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/

Post #1429020
Posted Sunday, March 10, 2013 3:09 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 6:31 PM
Points: 22,504, Visits: 30,217
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?

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.



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 #1429023
Posted Sunday, March 10, 2013 11:06 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 1:16 PM
Points: 20,462, Visits: 14,091
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1429073
Posted Monday, March 11, 2013 4:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:49 AM
Points: 6,798, Visits: 6,272
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.

Post #1429131
Posted Monday, March 11, 2013 6:23 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:00 PM
Points: 35,967, Visits: 30,258
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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1429220
Posted Monday, March 11, 2013 6:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:49 AM
Points: 6,798, Visits: 6,272
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 , I feel humbled, you made my day



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

Anon.

Post #1429223
Posted Monday, March 11, 2013 8:44 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 8,287, Visits: 8,738
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
Post #1429309
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse