March 10, 2013 at 8:31 am
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/
March 10, 2013 at 12:56 pm
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/
March 10, 2013 at 1:25 pm
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
March 10, 2013 at 2:26 pm
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/
March 10, 2013 at 3:09 pm
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.
March 10, 2013 at 11:06 pm
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[/url]
Learn Extended Events
March 11, 2013 at 4:00 am
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.
March 11, 2013 at 6:23 am
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
Change is inevitable... Change for the better is not.
March 11, 2013 at 6:27 am
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.
March 11, 2013 at 8:44 am
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
March 11, 2013 at 8:56 am
Jeff Moden (3/11/2013)
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.
Very nice indeed. I learned something new as well.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 11, 2013 at 9:00 am
SQLRNNR (3/11/2013)
Jeff Moden (3/11/2013)
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.
Very nice indeed. I learned something new as well.
Very nice. 😎 I tried adding the formatted duration but I can't quite get it.
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/
March 11, 2013 at 9:09 am
Welsh Corgi (3/11/2013)
SQLRNNR (3/11/2013)
Jeff Moden (3/11/2013)
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.
Very nice indeed. I learned something new as well.
Very nice. 😎 I tried adding the formatted duration but I can't quite get it.
Thank you.
What are you getting?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 11, 2013 at 9:24 am
I have the following code:
SELECT DISTINCT j.name as job_name, run_duration,
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]
,SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
SUBSTRING(run_duration, 5, 2) AS run_duration
FROM msdb..sysjobhistory h
INNER JOIN msdb..sysjobs j ON h.job_id = j.job_id
WHERE j.name = 'WMI_Disk_Space_Notification'
ORDER BY run_endtime DESC
:blush:
I get the error listed below:
Msg 8116, Level 16, State 1, Line 1
Argument data type int is invalid for argument 1 of substring function.
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 11, 2013 at 9:38 am
You have to cast run_duration to varchar first.
Try
STUFF(STUFF(RIGHT('000000'+CAST(run_duration as varchar(6)),6),5,0,':'),3,0,':')
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 15 posts - 1 through 15 (of 41 total)
You must be logged in to reply to this topic. Login to reply