Sql-Server
如何檢查計劃在特定時間執行的作業？
如何檢查計劃在特定時間執行的作業？
我正在嘗試做某事，
use msdb go Select [job name] = jb.name , [schedule name] = sc.name , [next run date] = js.next_run_date , [next run time] = js.next_run_time From dbo.sysjobs as jb Inner Join dbo.sysjobschedules as js on js.job_id = jb.job_id Inner Join dbo.sysschedules as sc on js.schedule_id = sc.schedule_id但到目前為止我無法完成..
Ben 剛剛從 Pittston（美國）回來，並在每天晚上 11.30 pm（PST）和 4.30am（GMT）報告了 Bocss 的減速。您能否檢查一下這段時間是否啟動了備份或其他程序。
謝謝
結論：
使用下面Bob Klimes的腳本，我得到了以下工作列表，明天我將在這裡與其他 DBA 討論這些工作。
我在這裡複製腳本供我自己參考，如果 Kin 或任何人對此不滿意，只需在下面寫下評論，我會刪除它，沒問題。
SET NOCOUNT ON GO /*********************************************************************************************************** ************************************************************************************************************ Author : KIN SHAH - Sr.DBA (MS SQL Server and SybaseASE) Date : Feb 18th 2014 Purpose : Find the Last_Run_Duration with Avg, Max and Min for SQL Agent RUN Times. This script can be used to do a trend analysis using SSRS. RDBMS : Compatible with SQL Server 2005, 2008 R2, 2012, 2014 and 2016 :-) Compatible with servers running standard and binary Collations as well. Version : 1.0.0 Change History : ** NONE ** License : This script is free to download and use for personal, educational, and internal corporate purposes, provided that this header is preserved. Redistribution or sale of this script in whole or in part, is prohibited without the author's express written consent. ************************************************************************************************************ ************************************************************************************************************/ -- DROP all temp tables if existed IF OBJECT_ID('tempdb..#temp_runhistory') IS NOT NULL DROP TABLE #temp_runhistory; IF OBJECT_ID('tempdb..#temp_jobhistory') IS NOT NULL DROP TABLE #temp_jobhistory; IF OBJECT_ID('tempdb..#schedules') IS NOT NULL DROP TABLE #schedules; IF OBJECT_ID('tempdb..#finalReport') IS NOT NULL DROP TABLE #finalReport; -- Variable Declaration DECLARE @num_days INT DECLARE @first_day DATETIME DECLARE @last_day DATETIME DECLARE @first_num INT IF @num_days IS NULL SET @num_days = 30 -- this will be how much the job history will be utilize to analyze the average run times !! SET @last_day = getdate() SET @first_day = dateadd(dd, - @num_days, @last_day) SELECT @first_num = cast(year(@first_day) AS CHAR(4)) + replicate('0', 2 - len(month(@first_day))) + cast(month(@first_day) AS VARCHAR(2)) + replicate('0', 2 - len(day(@first_day))) + cast(day(@first_day) AS VARCHAR(2)) SELECT h.instance_id ,h.job_id ,j.name ,h.step_id ,h.step_name ,h.sql_message_id ,h.sql_severity ,h.run_status ,'run_date' = cast(h.run_date AS VARCHAR(8)) ,'run_time' = replicate('0', 6 - len(h.run_time)) + cast(h.run_time AS VARCHAR(6)) ,'run_datetime' = left(cast(h.run_date AS VARCHAR(8)), 4) + '/' + substring(cast(h.run_date AS VARCHAR(8)), 5, 2) + '/' + right(cast(h.run_date AS VARCHAR(8)), 2) + ' ' + left(replicate('0', 6 - len(h.run_time)) + cast(h.run_time AS VARCHAR(6)), 2) + ':' + substring(replicate('0', 6 - len(h.run_time)) + cast(h.run_time AS VARCHAR(6)), 3, 2) + ':' + right(replicate('0', 6 - len(h.run_time)) + cast(h.run_time AS VARCHAR(6)), 2) ,run_duration = cast(h.run_duration AS VARCHAR(20)) ,run_duration_conv = CASE WHEN (len(cast(h.run_duration AS VARCHAR(20))) < 3) THEN cast(h.run_duration AS VARCHAR(6)) WHEN (len(cast(h.run_duration AS VARCHAR(20))) = 3) THEN LEFT(cast(h.run_duration AS VARCHAR(6)), 1) * 60 --min + RIGHT(cast(h.run_duration AS VARCHAR(6)), 2) --sec WHEN (len(cast(h.run_duration AS VARCHAR(20))) = 4) THEN LEFT(cast(h.run_duration AS VARCHAR(6)), 2) * 60 --min + RIGHT(cast(h.run_duration AS VARCHAR(6)), 2) --sec WHEN (len(cast(h.run_duration AS VARCHAR(20))) >= 5) THEN (Left(cast(h.run_duration AS VARCHAR(20)), len(h.run_duration) - 4)) * 3600 --hour + (substring(cast(h.run_duration AS VARCHAR(20)), len(h.run_duration) - 3, 2)) * 60 --min + Right(cast(h.run_duration AS VARCHAR(20)), 2) --sec END ,h.retries_attempted ,h.server INTO #temp_jobhistory FROM msdb..sysjobhistory h ,msdb..sysjobs j WHERE h.job_id = j.job_id AND h.run_date >= @first_num AND h.step_id = 0 option (recompile) SELECT j.job_id ,j.name ,'Sampling' = ( SELECT count(*) FROM #temp_jobhistory h WHERE h.job_id = j.job_id ) ,'fromRunDate' = ( SELECT min(run_date) FROM #temp_jobhistory h WHERE h.job_id = j.job_id ) ,'run_duration_max' = ( SELECT max(run_duration_conv) FROM #temp_jobhistory h WHERE h.job_id = j.job_id ) ,'run_duration_min' = ( SELECT min(run_duration_conv) FROM #temp_jobhistory h WHERE h.job_id = j.job_id ) ,'run_duration_avg' = ( SELECT avg(run_duration_conv) FROM #temp_jobhistory h WHERE h.job_id = j.job_id ) ,'Last_RunDate' = ( SELECT max(run_datetime) FROM #temp_jobhistory h WHERE h.job_id = j.job_id ) ,'Last_RunStatus' = NULL --(select run_status from #temp_jobhistory h where h.job_id=j.job_id) ,'Last_RunDuration' = NULL INTO #temp_runhistory FROM msdb..sysjobs j WHERE j.enabled = 1 -- only look for enabled JOBS !! option (recompile) UPDATE #temp_runhistory SET Last_RunStatus = j.run_status ,Last_RunDuration = j.run_duration_conv FROM #temp_jobhistory j WHERE #temp_runhistory.job_id = j.job_id AND #temp_runhistory.Last_RunDate = j.run_datetime AND j.run_datetime = ( SELECT max(run_datetime) FROM #temp_jobhistory j1 WHERE j1.job_id = #temp_runhistory.job_id ) ---- GET schedules in HUMAN READABLE FORMAT SELECT SJ.job_id ,SJ.name AS job_name ,CASE freq_type WHEN 1 THEN 'Occurs on ' + STUFF(RIGHT(active_start_date, 4), 3, 0, '/') + '/' + LEFT(active_start_date, 4) + ' at ' + REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(convert(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME) /* hh:mm:ss 24H */, 9), 14), ':000', ' ') /* HH:mm:ss:000AM/PM then replace the :000 with space.*/ WHEN 4 THEN 'Occurs every ' + CAST(freq_interval AS VARCHAR(10)) + ' day(s) ' + CASE freq_subday_type WHEN 1 THEN 'at ' + LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(convert(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME), 9), 14), ':000', ' ')) WHEN 2 THEN 'every ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' second(s)' WHEN 4 THEN 'every ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' minute(s)' WHEN 8 THEN 'every ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' hour(s)' ELSE '' END + CASE WHEN freq_subday_type IN ( 2 ,4 ,8 ) /* repeat seconds/mins/hours */ THEN ' between ' + LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(convert(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME), 9), 14), ':000', ' ')) + ' and ' + LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(convert(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CAST(active_end_time AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME), 9), 14), ':000', ' ')) ELSE '' END WHEN 8 THEN 'Occurs every ' + CAST(freq_recurrence_factor AS VARCHAR(10)) + ' week(s) on ' + REPLACE(CASE WHEN freq_interval & 1 = 1 THEN 'Sunday, ' ELSE '' END + CASE WHEN freq_interval & 2 = 2 THEN 'Monday, ' ELSE '' END + CASE WHEN freq_interval & 4 = 4 THEN 'Tuesday, ' ELSE '' END + CASE WHEN freq_interval & 8 = 8 THEN 'Wednesday, ' ELSE '' END + CASE WHEN freq_interval & 16 = 16 THEN 'Thursday, ' ELSE '' END + CASE WHEN freq_interval & 32 = 32 THEN 'Friday, ' ELSE '' END + CASE WHEN freq_interval & 64 = 64 THEN 'Saturday, ' ELSE '' END + '|', ', |', ' ') /* get rid of trailing comma */ + CASE freq_subday_type WHEN 1 THEN 'at ' + LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(convert(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME), 9), 14), ':000', ' ')) WHEN 2 THEN 'every ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' second(s)' WHEN 4 THEN 'every ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' minute(s)' WHEN 8 THEN 'every ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' hour(s)' ELSE '' END + CASE WHEN freq_subday_type IN ( 2 ,4 ,8 ) /* repeat seconds/mins/hours */ THEN ' between ' + LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(convert(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME), 9), 14), ':000', ' ')) + ' and ' + LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(convert(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CAST(active_end_time AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME), 9), 14), ':000', ' ')) ELSE '' END WHEN 16 THEN 'Occurs every ' + CAST(freq_recurrence_factor AS VARCHAR(10)) + ' month(s) on ' + 'day ' + CAST(freq_interval AS VARCHAR(10)) + ' of that month ' + CASE freq_subday_type WHEN 1 THEN 'at ' + LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(convert(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME), 9), 14), ':000', ' ')) WHEN 2 THEN 'every ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' second(s)' WHEN 4 THEN 'every ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' minute(s)' WHEN 8 THEN 'every ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' hour(s)' ELSE '' END + CASE WHEN freq_subday_type IN ( 2 ,4 ,8 ) /* repeat seconds/mins/hours */ THEN ' between ' + LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(convert(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME), 9), 14), ':000', ' ')) + ' and ' + LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(convert(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CAST(active_end_time AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME), 9), 14), ':000', ' ')) ELSE '' END WHEN 32 THEN 'Occurs ' + 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 'on the last ' END + CASE freq_interval WHEN 1 THEN 'Sunday' WHEN 2 THEN 'Monday' WHEN 3 THEN 'Tuesday' WHEN 4 THEN 'Wednesday' WHEN 5 THEN 'Thursday' WHEN 6 THEN 'Friday' WHEN 7 THEN 'Saturday' WHEN 8 THEN 'day' WHEN 9 THEN 'weekday' WHEN 10 THEN 'weekend' END + ' of every ' + CAST(freq_recurrence_factor AS VARCHAR(10)) + ' month(s) ' + CASE freq_subday_type WHEN 1 THEN 'at ' + LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(convert(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME), 9), 14), ':000', ' ')) WHEN 2 THEN 'every ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' second(s)' WHEN 4 THEN 'every ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' minute(s)' WHEN 8 THEN 'every ' + CAST(freq_subday_interval AS VARCHAR(10)) + ' hour(s)' ELSE '' END + CASE WHEN freq_subday_type IN ( 2 ,4 ,8 ) /* repeat seconds/mins/hours */ THEN ' between ' + LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(convert(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CAST(active_start_time AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME), 9), 14), ':000', ' ')) + ' and ' + LTRIM(REPLACE(RIGHT(CONVERT(VARCHAR(30), CAST(convert(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CAST(active_end_time AS VARCHAR(10)), 6), 3, 0, ':'), 6, 0, ':'), 8) AS DATETIME), 9), 14), ':000', ' ')) ELSE '' END WHEN 64 THEN 'Runs when the SQL Server Agent service starts' WHEN 128 THEN 'Runs when the computer is idle' END AS [Schedule_Description] INTO #schedules FROM msdb.dbo.sysjobs SJ INNER JOIN msdb.dbo.sysjobschedules SJS ON SJ.job_id = SJS.job_id INNER JOIN msdb.dbo.sysschedules SS ON SJS.schedule_id = SS.schedule_id option (recompile); --- Get all details into a temp table with all fancy calculations !! SELECT DISTINCT CONVERT(NVARCHAR(128), SERVERPROPERTY('servername')) AS [Server_Name] ,trh.job_id AS 'Job_ID' ,trh.name AS 'Job_Name' ,trh.Last_RunDate AS 'Last_Run_Date' ,'Last_RunStatus' = CASE trh.Last_RunStatus WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Canceled' WHEN 4 THEN 'In progress' END -- MIND BOGGLING CALCULATIONS .... IT will spin your head if you try to decode it !! ,'Last_RunDuration (hh:mm:ss)' = cast(trh.Last_RunDuration / 3600 AS VARCHAR(10)) + ':' + replicate('0', 2 - len((trh.Last_RunDuration % 3600) / 60)) + cast((trh.Last_RunDuration % 3600) / 60 AS VARCHAR(2)) + ':' + replicate('0', 2 - len((trh.Last_RunDuration % 3600) % 60)) + cast((trh.Last_RunDuration % 3600) % 60 AS VARCHAR(2)) ,'Avg_Duration (hh:mm:ss)' = cast(trh.run_duration_avg / 3600 AS VARCHAR(10)) + ':' + replicate('0', 2 - len((trh.run_duration_avg % 3600) / 60)) + cast((trh.run_duration_avg % 3600) / 60 AS VARCHAR(2)) + ':' + replicate('0', 2 - len((trh.run_duration_avg % 3600) % 60)) + cast((trh.run_duration_avg % 3600) % 60 AS VARCHAR(2)) ,'Max_Duration (hh:mm:ss)' = cast(trh.run_duration_max / 3600 AS VARCHAR(10)) + ':' + replicate('0', 2 - len((trh.run_duration_max % 3600) / 60)) + cast((trh.run_duration_max % 3600) / 60 AS VARCHAR(2)) + ':' + replicate('0', 2 - len((trh.run_duration_max % 3600) % 60)) + cast((trh.run_duration_max % 3600) % 60 AS VARCHAR(2)) ,'Min_Duration (hh:mm:ss)' = cast(trh.run_duration_min / 3600 AS VARCHAR(10)) + ':' + replicate('0', 2 - len((trh.run_duration_min % 3600) / 60)) + cast((trh.run_duration_min % 3600) / 60 AS VARCHAR(2)) + ':' + replicate('0', 2 - len((trh.run_duration_min % 3600) % 60)) + cast((trh.run_duration_min % 3600) % 60 AS VARCHAR(2)) ,trh.fromRunDate AS 'From_Date' ,trh.Sampling ,sched.[Schedule_Description] INTO #finalReport FROM #temp_runhistory trh LEFT JOIN #schedules AS sched ON trh.job_id = sched.job_id option (recompile) -- insert back into reporting server (if you have one ...) --- Generate an AWESOME FINAL REPORT !! SELECT getdate() AS [Data_Collection_Date] ,[Server_Name] ,[Job_ID] ,[Job_Name] ,[Last_Run_Date] ,[Last_RunStatus] ,[Last_RunDuration (hh:mm:ss)] ,[Avg_Duration (hh:mm:ss)] ,[Max_Duration (hh:mm:ss)] ,[Min_Duration (hh:mm:ss)] ,[From_Date] ,[Sampling] -- we want to get all the schedule for a single JOB with ;** seperated ,STUFF(( SELECT ';** ' + [Schedule_Description] FROM #finalReport b WHERE b.[Job_Name] = a.[Job_Name] FOR XML PATH('') ), 1, 3, '') AS [Schedule_Description] FROM #finalReport a GROUP BY [Server_Name] ,[Job_ID] ,[Job_Name] ,[Last_Run_Date] ,[Last_RunStatus] ,[Last_RunDuration (hh:mm:ss)] ,[Avg_Duration (hh:mm:ss)] ,[Max_Duration (hh:mm:ss)] ,[Min_Duration (hh:mm:ss)] ,[From_Date] ,[Sampling] ORDER BY [Last_RunDuration (hh:mm:ss)] DESC option (recompile)
這是我用來獲取工作資訊的查詢。我過去也使用過 SQLJobVis，但他們的網站目前似乎已關閉
with jobs as( select a.name,a.[description], a.enabled, case c.freq_type -- Daily, weekly, Monthly when 1 then 'Once' when 4 then 'Daily' when 8 then 'Wk ' -- For weekly, add in the days of the week + case freq_interval & 2 when 2 then 'M' else '' end -- Monday + case freq_interval & 4 when 4 then 'Tu' else '' end -- Tuesday + case freq_interval & 8 when 8 then 'W' else '' end -- etc + case freq_interval & 16 when 16 then 'Th' else '' end + case freq_interval & 32 when 32 then 'F' else '' end + case freq_interval & 64 when 64 then 'Sa' else '' end + case freq_interval & 1 when 1 then 'Su' else '' end when 16 then 'Mthly on day ' + convert(varchar(2), freq_interval) -- Monthly on a particular day when 32 then 'Mthly ' -- The most complicated one, "every third Friday of the month" for example + case c.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 'Sunday' when 2 then 'Monday' when 3 then 'Tuesday' when 4 then 'Wednesday' when 5 then 'Thursday' when 6 then 'Friday' when 7 then 'Saturday' when 8 then 'Day' when 9 then 'Week day' when 10 then 'Weekend day' end when 64 then 'Startup' -- When SQL Server starts when 128 then 'Idle' -- Whenever SQL Server gets bored else 'Err' -- This should never happen end as schedule, case c.freq_subday_type -- FOr when a job funs every few seconds, minutes or hours when 1 then 'Runs once 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),c.active_start_time), 6), 1, 2) + ':' + substring ( right (stuff (' ', 1, 1, '000000') + convert(varchar(6), c.active_start_time), 6) ,3 ,2) + ':' + substring ( right (stuff (' ', 1, 1, '000000') + convert(varchar(6),c.active_start_time), 6) ,5 ,2) as start_at ,case c.freq_subday_type when 1 then NULL -- Ignore the end time if not a recurring job else substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6), c.active_end_time), 6), 1, 2) + ':' + substring ( right (stuff (' ', 1, 1, '000000') + convert(varchar(6), c.active_end_time), 6) ,3 ,2) + ':' + substring ( right (stuff (' ', 1, 1, '000000') + convert(varchar(6), c.active_end_time), 6) ,5 ,2) end as end_at from msdb.dbo.sysjobs a inner join msdb.dbo.sysjobschedules b on a.job_id = b.job_id inner join msdb.dbo.sysschedules c on b.schedule_id = c.schedule_id) select * from jobs where start_at between '23:30' and '23:59' or start_at between '00:00' and '04:30'