October 10, 2008 at 12:14 pm
Trying 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:
ddfg
October 10, 2008 at 12:22 pm
No easy way, you'd need to decode the information and look for patterns.
October 10, 2008 at 3:14 pm
I 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%5D
**Date:09.05.2008
*******************************************************************************
**Change History
*******************************************************************************
**Date:Author:Description:
**-------------------------------------------------------
**
********************************************************************************************************/
CREATE PROCEDURE [dbo].[JobActivityMonitor]
AS
SET NOCOUNT ON
-- Setup temp table to hold job status
IF OBJECT_ID('tempdb.dbo.#JobStatus') IS NOT NULL
DROP TABLE #JobStatus
CREATE 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 runs
INSERT INTO #JobStatus
EXEC [master].[dbo].[xp_sqlagent_enum_jobs] 1,sa
SELECT
@@SERVERNAMEAS [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'
ENDAS [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'
ENDAS [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 '
ENDAS [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)
ENDAS [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'
ENDAS [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'
ENDAS [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]
) a
LEFT 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]
October 10, 2008 at 4:00 pm
That helps, only minor change will fit my need , thanks a bundle.:)
ddfg
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply