The script itself explores SQL Agent Job metadata to get job statuses — when the job last ran, when it will run again, an aggregate count of the number of successful and failed executions in the queried time period, T-SQL code to disable the job, etc. I’ve only tested this in a handful of DEV and PROD environments, so please tell me if you experience any issues in your environment when running the script. As with most of my scripts, this will only work on SQL Server 2005 and newer.
I was planning to send the SQL Server development team a case of #bacon had they finally fixed the sysjob% date and time columns in SQL Server 2012, but alas, it seems they shall sadly remain pork-free. Credit for the logic that handles the time conversion goes to some awesome community member who posted it on the MSDN forum. I wish I had the URL, but it was a long time ago.
DECLARE @jobHistory TABLE ( job_id UNIQUEIDENTIFIER , success INT , cancel INT , fail INT , retry INT , last_execution_id INT , last_duration CHAR(8) , last_execution_start DATETIME ); WITH lastExecution AS ( SELECT job_id , MAX(instance_id) AS last_instance_id FROM msdb.dbo.sysjobhistory WHERE step_id = 0 GROUP BY job_id ) INSERT INTO @jobHistory SELECT sjh.job_id , SUM(CASE WHEN sjh.run_status = 1 AND step_id = 0 THEN 1 ELSE 0 END) AS success , SUM(CASE WHEN sjh.run_status = 3 AND step_id = 0 THEN 1 ELSE 0 END) AS cancel , SUM(CASE WHEN sjh.run_status = 0 AND step_id = 0 THEN 1 ELSE 0 END) AS fail , SUM(CASE WHEN sjh.run_status = 2 THEN 1 ELSE 0 END) AS retry , MAX(CASE WHEN sjh.step_id = 0 THEN instance_id ELSE NULL END) last_execution_id , SUBSTRING(CAST(MAX(CASE WHEN le.job_id IS NOT NULL THEN sjh.run_duration ELSE NULL END) + 1000000 AS VARCHAR(7)),2,2) + ':' + SUBSTRING(CAST(MAX(CASE WHEN le.job_id IS NOT NULL THEN sjh.run_duration ELSE NULL END) + 1000000 AS VARCHAR(7)),4,2) + ':' + SUBSTRING(CAST(MAX(CASE WHEN le.job_id IS NOT NULL THEN sjh.run_duration ELSE NULL END) + 1000000 AS VARCHAR(7)),6,2) AS last_duration , MAX(CASE WHEN le.last_instance_id IS NOT NULL THEN CONVERT(datetime, RTRIM(run_date)) + ((run_time / 10000 * 3600) + ((run_time % 10000) / 100 * 60) + (run_time % 10000) % 100) / (86399.9964) ELSE '1900-01-01' END) AS last_execution_start FROM msdb.dbo.sysjobhistory AS sjh LEFT JOIN lastExecution AS le ON sjh.job_id = le.job_id AND sjh.instance_id = le.last_instance_id GROUP BY sjh.job_id; /* We need to parse the schedule into something we can understand */ DECLARE @weekDay TABLE ( mask INT , maskValue VARCHAR(32) ); INSERT INTO @weekDay SELECT 1, 'Sunday' UNION ALL SELECT 2, 'Monday' UNION ALL SELECT 4, 'Tuesday' UNION ALL SELECT 8, 'Wednesday' UNION ALL SELECT 16, 'Thursday' UNION ALL SELECT 32, 'Friday' UNION ALL SELECT 64, 'Saturday'; /* Now let's get our schedule information */ WITH myCTE AS( SELECT sched.name AS 'scheduleName' , sched.schedule_id , jobsched.job_id , CASE WHEN sched.freq_type = 1 THEN 'Once' WHEN sched.freq_type = 4 AND sched.freq_interval = 1 THEN 'Daily' WHEN sched.freq_type = 4 THEN 'Every ' + CAST(sched.freq_interval AS VARCHAR(5)) + ' days' WHEN sched.freq_type = 8 THEN REPLACE( REPLACE( REPLACE(( SELECT maskValue FROM @weekDay AS x WHERE sched.freq_interval & x.mask <> 0 ORDER BY mask FOR XML RAW) , '"/><row maskValue="', ', '), '<row maskValue="', ''), '"/>', '') + CASE WHEN sched.freq_recurrence_factor <> 0 AND sched.freq_recurrence_factor = 1 THEN '; weekly' WHEN sched.freq_recurrence_factor <> 0 THEN '; every ' + CAST(sched.freq_recurrence_factor AS VARCHAR(10)) + ' weeks' END WHEN sched.freq_type = 16 THEN 'On day ' + CAST(sched.freq_interval AS VARCHAR(10)) + ' of every ' + CAST(sched.freq_recurrence_factor AS VARCHAR(10)) + ' months' WHEN sched.freq_type = 32 THEN CASE WHEN sched.freq_relative_interval = 1 THEN 'First' WHEN sched.freq_relative_interval = 2 THEN 'Second' WHEN sched.freq_relative_interval = 4 THEN 'Third' WHEN sched.freq_relative_interval = 8 THEN 'Fourth' WHEN sched.freq_relative_interval = 16 THEN 'Last' END + CASE WHEN sched.freq_interval = 1 THEN ' Sunday' WHEN sched.freq_interval = 2 THEN ' Monday' WHEN sched.freq_interval = 3 THEN ' Tuesday' WHEN sched.freq_interval = 4 THEN ' Wednesday' WHEN sched.freq_interval = 5 THEN ' Thursday' WHEN sched.freq_interval = 6 THEN ' Friday' WHEN sched.freq_interval = 7 THEN ' Saturday' WHEN sched.freq_interval = 8 THEN ' Day' WHEN sched.freq_interval = 9 THEN ' Weekday' WHEN sched.freq_interval = 10 THEN ' Weekend' END + CASE WHEN sched.freq_recurrence_factor <> 0 AND sched.freq_recurrence_factor = 1 THEN '; monthly' WHEN sched.freq_recurrence_factor <> 0 THEN '; every ' + CAST(sched.freq_recurrence_factor AS VARCHAR(10)) + ' months' END WHEN sched.freq_type = 64 THEN 'StartUp' WHEN sched.freq_type = 128 THEN 'Idle' END AS 'frequency' , ISNULL('Every ' + CAST(sched.freq_subday_interval AS VARCHAR(10)) + CASE WHEN sched.freq_subday_type = 2 THEN ' seconds' WHEN sched.freq_subday_type = 4 THEN ' minutes' WHEN sched.freq_subday_type = 8 THEN ' hours' END, 'Once') AS 'subFrequency' , REPLICATE('0', 6 - LEN(sched.active_start_time)) + CAST(sched.active_start_time AS VARCHAR(6)) AS 'startTime' , REPLICATE('0', 6 - LEN(sched.active_end_time)) + CAST(sched.active_end_time AS VARCHAR(6)) AS 'endTime' , REPLICATE('0', 6 - LEN(jobsched.next_run_time)) + CAST(jobsched.next_run_time AS VARCHAR(6)) AS 'nextRunTime' , CAST(jobsched.next_run_date AS CHAR(8)) AS 'nextRunDate' FROM msdb.dbo.sysschedules AS sched JOIN msdb.dbo.sysjobschedules AS jobsched ON sched.schedule_id = jobsched.schedule_id WHERE sched.enabled = 1 ) /* Finally, let's look at our actual jobs and tie it all together */ SELECT CONVERT(NVARCHAR(128), SERVERPROPERTY('Servername')) AS [serverName] , job.job_id AS [jobID] , job.name AS [jobName] , CASE WHEN job.enabled = 1 THEN 'Enabled' ELSE 'Disabled' END AS [jobStatus] , COALESCE(sched.scheduleName, '(unscheduled)') AS [scheduleName] , COALESCE(sched.frequency, '') AS [frequency] , COALESCE(sched.subFrequency, '') AS [subFrequency] , COALESCE(SUBSTRING(sched.startTime, 1, 2) + ':' + SUBSTRING(sched.startTime, 3, 2) + ' - ' + SUBSTRING(sched.endTime, 1, 2) + ':' + SUBSTRING(sched.endTime, 3, 2), '') AS [scheduleTime] -- HH:MM , COALESCE(SUBSTRING(sched.nextRunDate, 1, 4) + '/' + SUBSTRING(sched.nextRunDate, 5, 2) + '/' + SUBSTRING(sched.nextRunDate, 7, 2) + ' ' + SUBSTRING(sched.nextRunTime, 1, 2) + ':' + SUBSTRING(sched.nextRunTime, 3, 2), '') AS [nextRunDate] /* Note: the sysjobschedules table refreshes every 20 min, so nextRunDate may be out of date */ , COALESCE(jh.success, 0) AS [success] , COALESCE(jh.cancel, 0) AS [cancel] , COALESCE(jh.fail, 0) AS [fail] , COALESCE(jh.retry, 0) AS [retry] , COALESCE(jh.last_execution_id, 0) AS [lastExecutionID] , jh.last_execution_start AS [lastExecutionStart] , COALESCE(jh.last_duration, '00:00:01') AS [lastDuration] , 'EXECUTE msdb.dbo.sp_update_job @job_id = ''' + CAST(job.job_id AS CHAR(36)) + ''', @enabled = 0;' AS [disableSQLScript] FROM msdb.dbo.sysjobs AS job LEFT JOIN myCTE AS sched ON job.job_id = sched.job_id LEFT JOIN @jobHistory AS jh ON job.job_id = jh.job_id WHERE job.enabled = 1 -- do not display disabled jobs --AND jh.last_execution_start >= DATEADD(day, -1, GETDATE()) /* Pull just the last 24 hours */ ORDER BY nextRunDate; |
Example Output:
serverName jobID jobName jobStatus scheduleName frequency subFrequency scheduleTime nextRunDate success cancel fail retry lastExecutionID lastExecutionStart lastDuration disableSQLScript ---------------- ------------------------------------- ---------------------------- --------- --------------------------------- ---------- -------------- -------------- ----------------- -------- ------- ----- ------ ---------------- ------------------------ ------------- ---------------------------------------------------------------------------------------------- SQLFOOL\SQL2012 180BFD8E-AE0C-44F8-992E-27522611992A DW FactOrders Update Enabled Every 4 Hours Daily Every 4 hours 00:00 - 23:59 2012/09/19 20:00 12 0 0 0 84 2012-09-17 14:00:01.000 00:00:04 EXECUTE msdb.dbo.sp_update_job @job_id = '180BFD8E-AE0C-44F8-992E-27522611992A', @enabled = 0; SQLFOOL\SQL2012 3470C9E5-A2CD-454A-89A1-DEF55FF186D3 SSIS Server Maintenance Job Enabled SSISDB Scheduler Daily Once 00:00 - 12:00 2012/09/20 00:00 3 0 0 0 68 2012-09-17 11:50:51.000 00:00:01 EXECUTE msdb.dbo.sp_update_job @job_id = '3470C9E5-A2CD-454A-89A1-DEF55FF186D3', @enabled = 0; SQLFOOL\SQL2012 F965B24D-60EB-4B95-91B6-C7D66057A883 syspolicy_purge_history Enabled syspolicy_purge_history_schedule Daily Once 02:00 - 23:59 2012/09/20 02:00 3 0 1 1 70 2012-09-17 11:50:51.000 00:01:24 EXECUTE msdb.dbo.sp_update_job @job_id = 'F965B24D-60EB-4B95-91B6-C7D66057A883', @enabled = 0; |
No comments:
Post a Comment