Job categories

Last Post 10 Mar 2010 01:27 PM by gunneyk. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
PaulMcKibben
New Member
New Member

--
10 Mar 2010 12:41 PM
What table does the job category reside in?

I am looking to create a script that will show me the job history for jobs of a particular category, and I need to know where that is maintained.

Thank you.
gunneyk
New Member
New Member

--
10 Mar 2010 01:27 PM
These queries should get u started. You can customize as needed. There is a function at the end that is needed to run these queries though.



-- List all the Jobs and their last run times
DECLARE @JobHistory TABLE ([Name] nvarchar(128), [Description] nvarchar(100), [Last Run Date] varchar(10),
[Last Run Time] varchar(8), [Last Run Dur] varchar(10), [Last Run Status] varchar(10),
[Job_ID] uniqueidentifier, [Min Dur] varchar(10), [Max Dur] varchar(10),[Avg Dur] varchar(10))

INSERT INTO @JobHistory ([Name], [Description], [Last Run Date], [Last Run Time], [Last Run Dur], [Last Run Status],[Job_ID])
SELECT a.[Name], LEFT(a.[Description],40) AS [Description]
--,LEFT(b.step_name,30) AS [Step Name],
,CONVERT(VARCHAR(10),CONVERT(DATETIME,CONVERT(VARCHAR(8),b.run_date)),110) AS [Last Run Date]
,SUBSTRING(RIGHT('00000' + cast(b.run_time as VARCHAR(6)),6),1,2)
+ ':' + SUBSTRING(RIGHT('00000' + cast(b.run_time as VARCHAR(6)),6),3,2) + ':'
+ RIGHT('0' + cast(b.run_time as VARCHAR(6)),2) AS [Last Run Time]
,SUBSTRING(RIGHT('00000' + cast(b.run_duration as VARCHAR(6)),6),1,2)
+ ':' + SUBSTRING(RIGHT('00000' + cast(b.run_duration as VARCHAR(6)),6),3,2) + ':'
+ RIGHT('0' + cast(b.run_duration as VARCHAR(6)),2) AS [Last Run Dur]
,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 Status]
,a.[Job_ID]
FROM [msdb].[dbo].[sysJobs] AS a INNER JOIN [Msdb].[dbo].[sysJobHistory] AS b
ON a.[Job_ID] = b.[Job_ID]
WHERE b.[Instance_ID] = (SELECT TOP 1 c.[Instance_ID] FROM Msdb.dbo.sysJobHistory AS c WHERE c.Job_ID = b.Job_ID AND c.[Step_ID] = 0 ORDER BY c.[Run_Date] DESC, c.[Run_Time] DESC)
AND b.[Step_ID] = 0
--ORDER BY [Last Run Date], [Last Run Time]

UPDATE a
SET [Min Dur] = (SELECT [dbo].[fn_run_duration](MIN(b.[run_duration])) FROM [msdb].[dbo].[sysjobhistory] AS b WHERE b.[job_id] = a.[job_id]),
[Max Dur] = (SELECT [dbo].[fn_run_duration](MAX(b.[run_duration])) FROM [msdb].[dbo].[sysjobhistory] AS b WHERE b.[job_id] = a.[job_id]),
[Avg Dur] = (SELECT [dbo].[fn_run_duration](AVG(b.[run_duration])) FROM [msdb].[dbo].[sysjobhistory] AS b WHERE b.[job_id] = a.[job_id])
FROM @JobHistory AS a

SELECT SERVERPROPERTY('servername') AS [Server Name], [Name], [Description], [Last Run Date], [Last Run Time], [Last Run Dur], [Last Run Status], [Min Dur], [Max Dur], [Avg Dur]
FROM @JobHistory
ORDER BY [Last Run Date], [Last Run Time]


-- Job Schedules
SELECT SERVERPROPERTY('servername') AS [Server Name], [a].[Name],[a].[Enabled] AS [J_E],
[c].[Enabled] AS [S_E],
CASE [c].[freq_type]
WHEN 1 THEN 'Run Once'
WHEN 4 THEN 'Runs Daily'
WHEN 8 THEN 'Every Week'
+ CASE freq_interval & 2 WHEN 2 THEN ' on Mondays' ELSE '' END
+ CASE freq_interval & 4 WHEN 4 THEN ' on Tuesday' ELSE '' END
+ CASE freq_interval & 8 WHEN 8 THEN ' on Wednesday' ELSE '' END
+ CASE freq_interval & 16 WHEN 16 THEN ' on Thursday' ELSE '' END
+ CASE freq_interval & 32 WHEN 32 THEN ' on Friday' ELSE '' END
+ CASE freq_interval & 64 WHEN 64 THEN ' on Saturday' ELSE '' END
+ CASE freq_interval & 1 WHEN 1 THEN ' on Sunday' ELSE '' END
WHEN 16 THEN 'Mthly on day ' + CONVERT(VARCHAR(2), freq_interval)
WHEN 32 THEN 'Mthly ' + CASE freq_relative_interval
WHEN 1 THEN 'Every First '
WHEN 2 THEN 'Every Second '
WHEN 4 THEN 'Every Third '
WHEN 8 THEN 'Every Fourth '
WHEN 16 THEN 'Every Last '
END
+ CASE [c].[freq_interval]
WHEN 1 THEN ' on Monday'
WHEN 2 THEN ' on Tuesdsay'
WHEN 3 THEN ' on Wednesday'
WHEN 4 THEN ' on Thursday'
WHEN 5 THEN ' on Friday'
WHEN 6 THEN ' on Saturday'
WHEN 7 THEN ' on Sunday'
WHEN 8 THEN 'Day'
WHEN 9 THEN 'Week day'
WHEN 10 THEN 'Weekend day'
END
WHEN 64 THEN 'Startup'
WHEN 128 THEN 'Idle'
ELSE 'Err'
END AS schedule,

CASE [c].[freq_subday_type]
WHEN 1 THEN 'Runs at:'
WHEN 2 THEN 'every ' + CONVERT(VARCHAR(3), freq_subday_interval) + ' seconds'
WHEN 4 THEN 'every ' + CONVERT(VARCHAR(3), freq_subday_interval) + ' minutes'
WHEN 8 THEN 'every ' + CONVERT(VARCHAR(3), freq_subday_interval) + ' hours'

END AS frequency,
SUBSTRING(RIGHT(STUFF(' ', 1, 1, '000000') +
CONVERT(VARCHAR(6),active_start_time), 6), 1, 2) + ':' +
SUBSTRING(RIGHT(STUFF(' ', 1, 1, '000000') +
CONVERT(VARCHAR(6),active_start_time), 6) ,3 ,2) + ':'+
SUBSTRING(RIGHT(STUFF(' ', 1, 1, '000000') +
CONVERT(VARCHAR(6),active_start_time),6) ,5 ,2)
AS start_at,

CASE freq_subday_type
WHEN 1 THEN NULL
ELSE
SUBSTRING(RIGHT(STUFF(' ', 1, 1, '000000') +
CONVERT(VARCHAR(6),active_end_time), 6), 1, 2) + ':' +
SUBSTRING(RIGHT(STUFF(' ', 1, 1, '000000') +
CONVERT(VARCHAR(6), active_end_time),6) ,3 ,2) + ':' +
SUBSTRING(RIGHT(STUFF(' ', 1, 1, '000000') +
CONVERT(VARCHAR(6), active_end_time),6) ,5 ,2)
END AS end_at,
[a].[Description]
FROM [msdb].[dbo].[sysjobs] AS a INNER JOIN [msdb].[dbo].[sysjobschedules] AS b
ON [a].[Job_ID] = .[Job_ID]
INNER JOIN [msdb].[dbo].[sysschedules] AS c
ON .[schedule_id] = [c].[schedule_id]
ORDER BY [a].[Name]
GO


CREATE FUNCTION [dbo].[fn_run_duration]
(@Time int)

RETURNS VARCHAR(10)

AS

BEGIN

RETURN SUBSTRING(RIGHT('00000' + cast(@Time as VARCHAR(6)),6),1,2)
+ ':' + SUBSTRING(RIGHT('00000' + cast(@Time as VARCHAR(6)),6),3,2) + ':'
+ RIGHT('0' + cast(@Time as VARCHAR(6)),2)

END
GO
You are not authorized to post a reply.

Acceptable Use Policy