|
|
|
SSC-Addicted
 
Group: General Forum Members
Last Login: Monday, June 08, 2009 3:36 PM
Points: 453,
Visits: 95
|
|
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. 
|
|
|
|
|
SSChampion
Group: Administrators
Last Login: Today @ 11:41 AM
Points: 19,451,
Visits: 5,034
|
|
| No easy way, you'd need to decode the information and look for patterns.
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Thursday, July 02, 2009 2:42 PM
Points: 1,260,
Visits: 1,872
|
|
I just recently found this:
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]
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
@@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]
) 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
|
|
|
|
|
SSC-Addicted
Group: General Forum Members
Last Login: Monday, June 08, 2009 3:36 PM
Points: 453,
Visits: 95
|
|
That helps, only minor change will fit my need , thanks a bundle.:)
|
|
|
|
