InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 20:42:28 GMT20http://www.sqlservercentral.com/Forums/Topic410789-8-1.aspxDo you have the script please to use sysjobactivity? I would like to bring all the jobs with their names, steps, and the status, last run time, even if there is no history, or the jobs is disabled, not scheduledFri, 01 Feb 2013 16:53:23 GMTGolovahttp://www.sqlservercentral.com/Forums/Topic410789-8-1.aspxWord of warning : if the history has been purged/deleted then sysjobhistory will not display the same data as "Job Activity Monitor" i.e. "Job Activity Monitor" will display a last run date but this is not obtainable from running queries against sysjobhistory where the history has been purged because it just won't exist. Use sysjobactivity to obtain the "real" last run date and to be in agreement with "Job Activity Monitor".Try it; create a test job, run it a few times, manually delete the latest history records then run your query using sysjobhistory. You will find that your queries report an earlier last run time than SQL's "Job Activity Monitor".Does it matter ? Well it may do, I noticed this problem when deciding which jobs could be deleted. It seemed that some jobs were never run but this was not the case as the history had been deleted.Fri, 12 Aug 2011 10:39:09 GMTPreet_Shttp://www.sqlservercentral.com/Forums/Topic410789-8-1.aspxSELECT j.[name],MAX(CAST(STUFF(STUFF(CAST(jh.run_date as varchar),7,0,'-'),5,0,'-') + ' ' + STUFF(STUFF(REPLACE(STR(jh.run_time,6,0),' ','0'),5,0,':'),3,0,':') as datetime)) AS [LastRun],MAX(CAST(STUFF(STUFF(CAST(sjs.next_run_date as varchar),7,0,'-'),5,0,'-') + ' ' + STUFF(STUFF(REPLACE(STR(sjs.next_run_time,6,0),' ','0'),5,0,':'),3,0,':') as datetime)) AS [NextRun],CASE jh.run_status WHEN 0 THEN 'Failed'WHEN 1 THEN 'Success'WHEN 2 THEN 'Retry'WHEN 3 THEN 'Canceled'WHEN 4 THEN 'In progress'END AS StatusFROM msdb.dbo.sysjobs j INNER JOIN msdb.dbo.sysjobhistory jh ON jh.job_id = j.job_id AND jh.step_id = 0 inner join msdb.dbo.syscategories scon j.category_id = sc.category_idINNER JOIN MSDB.dbo.sysjobschedules sjson j.job_id = sjs.Job_idGROUP BY j.[name], jh.run_statusWed, 01 Sep 2010 05:17:56 GMTramarkishna http://www.sqlservercentral.com/Forums/Topic410789-8-1.aspxThank you for the script Wildcat, it's very useful for me!Mon, 08 Jun 2009 07:19:30 GMTlucazavhttp://www.sqlservercentral.com/Forums/Topic410789-8-1.aspxThank you. That's exactly what I was looking for.Mon, 12 Jan 2009 09:44:16 GMTJames B-487162http://www.sqlservercentral.com/Forums/Topic410789-8-1.aspx[code]SELECT j.[name], CAST(STUFF(STUFF(CAST(jh.run_date as varchar),7,0,'-'),5,0,'-') + ' ' + STUFF(STUFF(REPLACE(STR(jh.run_time,6,0),' ','0'),5,0,':'),3,0,':') as datetime) AS [LastRun], CASE jh.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Success' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Canceled' WHEN 4 THEN 'In progress' END AS [Status] FROM (SELECT a.job_id,MAX(a.instance_id) As [instance_id] FROM msdb.dbo.sysjobhistory a WHERE a.step_id = 0 GROUP BY a.job_id) b INNER JOIN msdb.dbo.sysjobhistory jh ON jh.instance_id=b.instance_id INNER JOIN msdb.dbo.sysjobs j ON j.job_id = jh.job_id [/code]Mon, 12 Jan 2009 09:35:26 GMTDavid Burrowshttp://www.sqlservercentral.com/Forums/Topic410789-8-1.aspxThanks for posting this script. I'm having trouble modifying it to my use though. I'm trying to create a script that I can have dbmail run to email me the last run status of every job. The script given, if I remove the where section works, except it gives the last run for each type of status. If there's been a failure and a success within it's history it shows both with dates.Any ideas on how I can just have it show the late run status and time?Mon, 12 Jan 2009 08:54:55 GMTJames B-487162http://www.sqlservercentral.com/Forums/Topic410789-8-1.aspxfor a lot of examples on how to query dts schedule fields see http://raoulteeuwen.blogspot.com/2008/09/query-job-last-run-status-in-ms-sql.html and the link in that blog to http://doc.ddart.net/mssql/sql70/sp_help_27.htm ... it shows you how you can check last run date, last run status etc.Fri, 28 Nov 2008 04:36:21 GMTraoul.teeuwenhttp://www.sqlservercentral.com/Forums/Topic410789-8-1.aspxCan you please give me an example SQL Statement to use to get the last datetime of the scheduled replication? ThanksThu, 30 Oct 2008 16:19:00 GMTCoop-546061http://www.sqlservercentral.com/Forums/Topic410789-8-1.aspx[quote][b]Coop (10/28/2008)[/b][hr]Do you know how to get the last datetime of SQL replication? Thank you![/quote]Not sure what you asked.There are several _history tables in distribution db, like, MSdistribution_history, MSlogreader_history, etc... You can get some info from there.Thu, 30 Oct 2008 12:35:33 GMTWildcathttp://www.sqlservercentral.com/Forums/Topic410789-8-1.aspxDo you know how to get the last datetime of SQL replication? Thank you!Tue, 28 Oct 2008 20:27:42 GMTCoop-546061http://www.sqlservercentral.com/Forums/Topic410789-8-1.aspxThank you very much for the script.Mon, 16 Jun 2008 13:04:12 GMTazeem.anchamparuthihttp://www.sqlservercentral.com/Forums/Topic410789-8-1.aspxBased on David's script:SELECT j.[name],MAX(CAST(STUFF(STUFF(CAST(jh.run_date as varchar),7,0,'-'),5,0,'-') + ' ' + STUFF(STUFF(REPLACE(STR(jh.run_time,6,0),' ','0'),5,0,':'),3,0,':') as datetime)) AS [LastRun],CASE jh.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Success' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Canceled' WHEN 4 THEN 'In progress'END AS StatusFROM msdb.dbo.sysjobs j INNER JOIN msdb.dbo.sysjobhistory jh ON jh.job_id = j.job_id AND jh.step_id = 0 inner join msdb.dbo.syscategories scon j.category_id = sc.category_idWHERE sc.[name] like '%DTS%'GROUP BY j.[name], jh.run_statusThu, 12 Jun 2008 13:53:38 GMTWildcathttp://www.sqlservercentral.com/Forums/Topic410789-8-1.aspxDavid, thank you for the post. I ran the query and it returned the date of last DTS run, do you know how to get the last "successfull" DTS runThanks,AThu, 12 Jun 2008 11:32:09 GMTazeem.anchamparuthihttp://www.sqlservercentral.com/Forums/Topic410789-8-1.aspxThank you David.Mon, 15 Oct 2007 06:57:43 GMTPrasad Bhogadihttp://www.sqlservercentral.com/Forums/Topic410789-8-1.aspxSELECT j.[name],MAX(CAST(STUFF(STUFF(CAST(jh.run_date as varchar),7,0,'-'),5,0,'-') + ' ' + STUFF(STUFF(REPLACE(STR(jh.run_time,6,0),' ','0'),5,0,':'),3,0,':') as datetime)) AS [LastRun]FROM msdb.dbo.sysjobs j INNER JOIN msdb.dbo.sysjobhistory jh ON jh.job_id = j.job_id AND jh.step_id = 0 WHERE j.[name] = 'jobname' GROUP BY j.[name] Mon, 15 Oct 2007 06:52:13 GMTDavid Burrowshttp://www.sqlservercentral.com/Forums/Topic410789-8-1.aspxHi,Is there anyway that we can retrieve the Last Run date of a SQL Job using T-SQL.ThanksMon, 15 Oct 2007 06:28:29 GMTPrasad Bhogadi