InstantForum.NET v4.1.4SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 08 Nov 2009 05:41:13 GMT20http://www.sqlservercentral.com/Forums/Topic584177-146-1.aspxThat helps, only minor change will fit my need , thanks a bundle.:)Fri, 10 Oct 2008 16:00:48 GMTawuhttp://www.sqlservercentral.com/Forums/Topic584177-146-1.aspxI just recently found this:[font="Courier New"]IF OBJECT_ID('JobActivityMonitor','P') IS NOT NULL DROP PROCEDURE [JobActivityMonitor]GO/********************************************************************************************************* Name: dbo.JobActivityMonitor ** Desc: Job Activity Monitor (SQL2005)** Auth: Adam Bean (SQLSlayer.com) [original interval logic from William McEvoy - http://cookingwithsql.com/source/sp_ShowJobSchedules.sql]** Date: 09.05.2008********************************************************************************* Change History********************************************************************************* Date: Author: Description:** -------- -------- ---------------------------------------** ********************************************************************************************************/CREATE PROCEDURE [dbo].[JobActivityMonitor]ASSET NOCOUNT ON-- Setup temp table to hold job status IF OBJECT_ID('tempdb.dbo.#JobStatus') IS NOT NULLDROP TABLE #JobStatusCREATE TABLE #JobStatus ( [Job_ID] UNIQUEIDENTIFIER ,[Last_Run_Date] INT ,[Last_Run_Time] INT ,[Next_Run_Date] INT ,[Next_Run_Time] INT ,[Next_Run_Schedule_ID] INT ,[Requested_To_Run] INT ,[Request_Source] INT ,[Request_Source_ID] VARCHAR(100) ,[Running] INT ,[Current_Step] INT ,[Current_Retry_Attempt] INT ,[State] INT )-- Retrieve results of last job runsINSERT INTO #JobStatus EXEC [master].[dbo].[xp_sqlagent_enum_jobs] 1,saSELECT @@SERVERNAME AS [ServerName] ,a.[Name] ,a.[Enabled] ,a.[Status] ,CASE b.[run_status] WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Canceled' WHEN 4 THEN 'In progress' END AS [Last Run Outcome] ,a.[Last Run Date] ,a.[Next Run Date] ,a.[Schedule Name] ,a.[Frequency] ,a.[Interval] ,a.[Category] ,a.[Description] ,a.[Owner] ,b.[Last Run Time] ,a.[Min Run Time] ,a.[Avg Run Time] ,a.[Max Run Time] ,a.[Successful] ,a.[Failed] ,a.[Cancelled]FROM ( -- Get all of our job information SELECT sj.job_id ,sj.[name] AS [Name] ,CASE sj.[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END AS [Enabled] ,CASE js.[State] WHEN 0 THEN 'Not Idle Or Suspended' WHEN 1 THEN 'Executing' WHEN 2 THEN 'Waiting For Thread' WHEN 3 THEN 'Between Retries' WHEN 4 THEN 'Idle' WHEN 5 THEN 'Suspended' WHEN 6 THEN 'Waiting For Step To Finish' WHEN 7 THEN 'Performing Completion Actions ' END AS [Status] ,CONVERT(VARCHAR(20), 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)),100) AS [Last Run Date] ,CASE sjs.[next_run_date] WHEN 0 THEN 'n/a' ELSE CONVERT(VARCHAR(20), CAST(LTRIM(STR(sjs.[next_run_date])) + ' ' + STUFF(STUFF(RIGHT('000000'+LTRIM(STR(sjs.[next_run_time])), 6) , 3, 0, ':'), 6, 0, ':') AS DATETIME), 100) END AS [Next Run Date] ,ss.[name] AS [Schedule Name] ,CASE ss.[freq_type] WHEN 1 THEN 'One-Time Only' WHEN 4 THEN 'Daily' WHEN 8 THEN 'Weekly' WHEN 16 THEN 'Monthly' WHEN 32 THEN 'Monthly' WHEN 64 THEN 'SQL Server Agent Startup' WHEN 128 THEN 'When Processor Idle' END AS [Frequency] ,CASE WHEN ss.[freq_type] = 1 THEN 'One time only' WHEN ss.[freq_type] = 4 AND ss.[freq_interval] = 1 AND ss.[freq_subday_type] = 0 THEN 'Every Day' WHEN ss.[freq_type] = 4 AND ss.[freq_interval] = 1 AND ss.[freq_subday_type] = 1 THEN 'Every ' + CONVERT(VARCHAR(10),ss.[freq_interval]) + ' Day(s)' WHEN ss.[freq_type] = 4 AND ss.[freq_interval] = 1 AND ss.[freq_subday_type] = 2 THEN 'Every ' + CONVERT(VARCHAR(10),ss.[freq_interval]) + ' Second(s)' WHEN ss.[freq_type] = 4 AND ss.[freq_interval] = 1 AND ss.[freq_subday_type] = 4 THEN 'Every ' + CONVERT(VARCHAR(10),ss.[freq_interval]) + ' Minute(s)' WHEN ss.[freq_type] = 4 AND ss.[freq_interval] = 1 AND ss.[freq_subday_type] = 8 THEN 'Every ' + CONVERT(VARCHAR(10),ss.[freq_interval]) + ' Hour(s)' WHEN ss.[freq_type] = 8 THEN ( SELECT D1+ D2+D3+D4+D5+D6+D7 AS [Weekly Schedule] FROM ( SELECT ss.[schedule_id] ,ss.[freq_interval] ,CASE WHEN ss.[freq_interval] & 1 <> 0 THEN 'Sun ' ELSE '' END AS [D1] ,CASE WHEN ss.[freq_interval] & 2 <> 0 THEN 'Mon ' ELSE '' END AS [D2] ,CASE WHEN ss.[freq_interval] & 4 <> 0 THEN 'Tue ' ELSE '' END AS [D3] ,CASE WHEN ss.[freq_interval] & 8 <> 0 THEN 'Wed ' ELSE '' END AS [D4] ,CASE WHEN ss.[freq_interval] & 16 <> 0 THEN 'Thu ' ELSE '' END AS [D5] ,CASE WHEN ss.[freq_interval] & 32 <> 0 THEN 'Fri ' ELSE '' END AS [D6] ,CASE WHEN ss.[freq_interval] & 64 <> 0 THEN 'Sat ' ELSE '' END AS [D7] FROM [msdb].[dbo].[sysschedules] ss WHERE ss.[freq_type] = 8 ) AS F WHERE [schedule_id] = ss.[schedule_id] ) WHEN ss.[freq_type] = 16 THEN 'Day ' + CAST(ss.[freq_interval] AS VARCHAR(2)) WHEN ss.[freq_type] = 32 THEN ( SELECT [freq_rel] + WDAY FROM ( SELECT [schedule_id] ,CASE ss.[freq_relative_interval] WHEN 1 THEN 'First' WHEN 2 THEN 'Second' WHEN 4 THEN 'Third' WHEN 8 THEN 'Fourth' WHEN 16 THEN 'Last' ELSE '??' END AS [freq_rel] ,CASE ss.[freq_interval] WHEN 1 THEN ' Sun' WHEN 2 THEN ' Mon' WHEN 3 THEN ' Tue' WHEN 4 THEN ' Wed' WHEN 5 THEN ' Thu' WHEN 6 THEN ' Fri' WHEN 7 THEN ' Sat' WHEN 8 THEN ' Day' WHEN 9 THEN ' Weekday' WHEN 10 THEN ' Weekend' ELSE '??' END AS [WDAY] FROM [msdb].[dbo].[sysschedules] ss WHERE ss.[freq_type] = 32 ) AS WS WHERE WS.[schedule_id] = ss.[schedule_id] ) ELSE 'n/a' END AS [Interval] ,sc.[name] AS [Category] ,sj.[description] AS [Description] ,SUSER_SNAME(sj.[owner_sid]) AS [Owner] ,MIN((jh.[run_duration]/10000*3600 + (jh.[run_duration]/100)%100*60 + jh.[run_duration]%100 + 31 ) / 60) AS [Min Run Time] ,AVG((jh.[run_duration]/10000*3600 + (jh.[run_duration]/100)%100*60 + jh.[run_duration]%100 + 31 ) / 60) AS [Avg Run Time] ,MAX((jh.[run_duration]/10000*3600 + (jh.[run_duration]/100)%100*60 + jh.[run_duration]%100 + 31 ) / 60) AS [Max Run Time] ,SUM(CASE WHEN jh.[run_status] = 1 THEN 1 ELSE 0 END) AS [Successful] ,SUM(CASE WHEN jh.[run_status] = 0 THEN 1 ELSE 0 END) AS [Failed] ,SUM(CASE WHEN jh.[run_status] = 3 THEN 1 ELSE 0 END) AS [Cancelled] FROM [msdb].[dbo].[sysjobs] sj LEFT JOIN #JobStatus js ON js.[Job_ID] = sj.[job_id] LEFT JOIN [msdb].[dbo].[sysjobhistory] jh ON jh.[job_id] = sj.[job_id] LEFT JOIN [msdb].[dbo].[sysjobschedules] sjs ON sjs.[job_id] = sj.[job_id] INNER JOIN [msdb].[dbo].[syscategories] sc ON sc.[category_id] = sj.[category_id] INNER JOIN [msdb].[dbo].[sysschedules] ss ON ss.[schedule_id] = sjs.[schedule_id] WHERE jh.[step_id] = 0 GROUP BY sj.[job_id], sj.[name], sj.[enabled], js.[State], ss.[name], sjs.[next_run_date], sjs.[next_run_time], ss.[freq_type], ss.[freq_subday_type], sc.[name], ss.[schedule_id], ss.[freq_interval], sj.[description], sj.[owner_sid]) aLEFT JOIN ( SELECT [job_id] ,[run_status] ,([run_duration]/10000*3600 + ([run_duration]/100)%100*60 + [run_duration]%100 + 31 / 60)/60 AS [Last Run Time] ,CONVERT(VARCHAR(20), MAX(CAST(STUFF(STUFF(CAST([run_date] AS VARCHAR),7,0,'-'),5,0,'-') + ' ' + STUFF(STUFF(REPLACE(STR([run_time],6,0),' ','0'),5,0,':'),3,0,':') AS DATETIME)),100) AS [Last Run Date] FROM [msdb].[dbo].[sysjobhistory] WHERE [step_id] = 0 GROUP BY [job_id], [run_status], [run_duration] ) AS b ON a.[job_id] = b.[job_id] AND a.[Last Run Date] = b.[Last Run Date]ORDER BY a.[Name]SET NOCOUNT OFF[/font]Fri, 10 Oct 2008 15:14:24 GMThomebrew01http://www.sqlservercentral.com/Forums/Topic584177-146-1.aspxNo easy way, you'd need to decode the information and look for patterns.Fri, 10 Oct 2008 12:22:46 GMTSteve Jones - Editorhttp://www.sqlservercentral.com/Forums/Topic584177-146-1.aspxTrying to get a report about all jobs including their schedules running on DB boxes , and found that the sysjobschedules format hard to read, wondering how to pull out those job schedules somthing like "Occurs every day every 4 hour(s)" when you create them ? Thx. :unsure:Fri, 10 Oct 2008 12:14:55 GMTawu