Vous êtes ici :
Comment être averti lorsqu’un job SQL Agent semble bloqué ou dure beaucoup plus longtemps que d’habitude ?
Ce script déclenche une erreur lorsqu’un job SQL Agent s’exécute depuis plus d’une heure ET que sa durée d’exécution dépasse 300% de sa durée habituelle (moyenne des précédentes exécutions réussies).
Planifiez ce script dans un job SQL Agent toutes les quelques minutes et configurez une notification en cas d’échec. L’erreur déclenchée est une erreur 50000 gravité 19, journalisée dans le journal d’évènements Windows. La liste des jobs concernés est contenue dans le message d’erreur.
Transact-SQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 |
/*------------------------------------------------------------------- [SCRIPT] Check job exceeded duration [DATABASE] msdb [DESCRIPTION] Triggers an error if a job is running since more than one hour and more than 300% of his average duration. [MAJ PAR] DATAFLY - Arian Papillon -------------------------------------------------------------------*/ USE msdb GO IF OBJECT_ID('tempdb..#jobstatus') IS NOT NULL DROP TABLE #jobstatus; IF OBJECT_ID('tempdb..#jobs_in_alert') IS NOT NULL DROP TABLE #jobs_in_alert; CREATE TABLE #jobstatus ( [Job ID] [BINARY](16) NULL , [Last Run Date] [INT] NULL , [Last Run Time] [INT] NULL , [Next Run Date] [INT] NULL , [Next Run Time] [INT] NULL , [Next Run Schedule ID] [INT] NULL , [Requested To Run] [INT] NULL , [Request Source] [INT] NULL , [Request Source ID] [NVARCHAR](66) NULL , [Running] [INT] NULL , [Current Step] [INT] NULL , [Current Retry Attempt] [INT] NULL , [State] [INT] NULL ); INSERT #jobstatus EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, ''; SELECT [Job ID] , job_name , start_execution_date , DATEDIFF(SECOND, start_execution_date, GETDATE()) / 60.0 AS run_duration_min , AvgJobDuration.avg_run_duration_min , ( DATEDIFF(SECOND, start_execution_date, GETDATE()) / 60.0 ) / CASE WHEN avg_run_duration_min = 0 THEN 1 ELSE avg_run_duration_min END * 100 AS percent_duration INTO #jobs_in_alert FROM #jobstatus JOIN ( SELECT job_id , MAX(start_execution_date) AS start_execution_date FROM sysjobactivity GROUP BY job_id ) AS A ON #jobstatus.[Job ID] = A.job_id JOIN ( SELECT j.job_id , j.name AS job_name , avg_run_duration_min = AVG(CAST(( CAST(SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR(6), run_duration), 6), 1, 2) AS DECIMAL(6, 2)) * 3600 + CAST(SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR(6), run_duration), 6), 3, 2) AS DECIMAL(6, 2)) * 60 + CAST(SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR(6), run_duration), 6), 5, 2) AS DECIMAL(6, 2)) ) / 60 AS DECIMAL(10, 2))) FROM msdb..sysjobhistory h INNER JOIN msdb..sysjobs j ON h.job_id = j.job_id WHERE run_status = 1 AND step_id = 0 GROUP BY j.job_id , j.name ) AS AvgJobDuration ON #jobstatus.[Job ID] = AvgJobDuration.job_id WHERE Running = 1 AND ( DATEDIFF(SECOND, start_execution_date, GETDATE()) / 60.0 ) > 60 -- plus d'une heure AND ( ( DATEDIFF(SECOND, start_execution_date, GETDATE()) / 60.0 ) / CASE WHEN avg_run_duration_min = 0 THEN 1 ELSE avg_run_duration_min END * 100 ) > 300; -- 300% du temps moyen IF @@ROWCOUNT > 0 BEGIN DECLARE @msg VARCHAR(8000); SET @msg = 'Job(s) duration exceeded : ' + STUFF(( SELECT ', ' + job_name AS [text()] FROM #jobs_in_alert FOR XML PATH('')) , 1 , 2 , ''); RAISERROR(@msg, 19, 1) WITH LOG; END; |
Table of Contents