{"id":33617,"date":"2024-06-12T14:50:36","date_gmt":"2024-06-12T12:50:36","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=33617"},"modified":"2024-06-12T14:50:39","modified_gmt":"2024-06-12T12:50:39","slug":"keep-an-eye-on-your-sql-agent-jobs-using-t-sql","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/keep-an-eye-on-your-sql-agent-jobs-using-t-sql\/","title":{"rendered":"Keep an eye on your SQL Agent jobs using T-SQL!"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\" id=\"h-introduction\">Introduction:<\/h2>\n\n\n\n<p>A couple of weeks ago I had a customer who was interested in monitoring the duration of some of his SQL-Agent jobs. The jobs were running in a data warehouse environment and were executing some ETL processes for loading data. The goal was to get informed when one of these specific jobs were running significantly longer than usual. Therefore I wrote a short stored procedure and a script which I would like to show you in this blog post!<\/p>\n\n\n\n<p>First of all, I would like to start with explaining the parameter\u2019s of the stored procedure to get a better understanding of the logic:<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-input-parameter-s\">Input parameter&#8217;s:<\/h2>\n\n\n\n<p>We have the following input parameter\u2019s for influencing the behavior of the stored procedure:<\/p>\n\n\n\n<p><strong><u>@SensitivityPercentage<\/u><\/strong>: This \u201cfloat\u201d parameter defines the percentage increase of the last job runtime compared to the calculated average value, which must be exceeded to set the <strong>@ThresholdExceeded<\/strong> output variable as true. I set the default value for this Input parameter to \u201c30%\u201d or \u201c0.3\u201c in decimal, but the value can be overwritten to a less or more aggressive behavior when executing the stored procedure.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\" \/>\n\n\n\n<p><strong><u>@SensivityTotalMinutes<\/u><\/strong>: This \u201cinteger\u201d parameter defines the increase in minutes of the last job runtime compared to the calculated average value, which must be exceeded to set the <strong>@ThresholdExceeded<\/strong> output variable as true. The default value in the stored procedure is 20 minutes, but it can be changed here as well!<\/p>\n\n\n\n<p>Note: both values provided in the sensitivity variables must be exceeded to set the <strong>@ThresholdExceeded <\/strong>variable as true. I implemented this logic, for not getting notified when a job is increasing from 1 minute to 2 minutes \u2013 the increase would be 100% but as it\u2019s only 1 minute I don\u2019t care\ud83d\ude09<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\" \/>\n\n\n\n<p><strong><u>@granularity<\/u><\/strong>: This \u201cinteger\u201d parameter defines how many of the latest entries of the job history should be used to calculate a average value for comparing the last run duration. I set this with a default value of 5, but here as well you can change it.<\/p>\n\n\n\n<p>Note: If in the job history are less than 5 entries, the stored procedure takes automatically the amount of entries which are available to calculate a average value.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\" \/>\n\n\n\n<p><strong><u>@JobId <\/u><\/strong>-&gt; This is quite obvious: The ID of the job which should be checked.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-output-parameter-s\">Output parameter&#8217;s:<\/h2>\n\n\n\n<p>To get some interesting values out of the stored procedure, I defined the following output parameters:<\/p>\n\n\n\n<p><strong><u>@AverageJobRunDuration<\/u><\/strong>: This is the average job run duration in minutes.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\" \/>\n\n\n\n<p><strong><u>@RunDurationLastExecution<\/u><\/strong>: This is the duration of the last execution in minutes.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\" \/>\n\n\n\n<p><strong><u>@Factor<\/u><\/strong>: This is the factor of the duration from the last execution compared to the calculated average value.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\" \/>\n\n\n\n<p><strong><u>@IncreasionPercentageString<\/u><\/strong>: This is the increase or decrease of the last duration compared to the average value in a percentage value.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\" \/>\n\n\n\n<p><strong><u>@IncreasionTotalMinutes<\/u><\/strong>: This is the increase or decrease of the last duration compared to the average value in minutes.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\" \/>\n\n\n\n<p><strong><u>@OutputMessage<\/u><\/strong>: This is a generated output message.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\" \/>\n\n\n\n<p><strong><u>@ThresholdExceeded<\/u><\/strong>: This defines if the threshold is exceeded based on the above sensitivity values.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-the-stored-procedure\">The stored procedure:<\/h2>\n\n\n\n<p>See below the stored procedure:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nCREATE PROCEDURE &#x5B;dbo].&#x5B;dbi_jobruntimeinfo]\n------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n--Parameters\n------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n(\n\n--Input\n@SensitivityPercentage float = 0.3,\n@SensivityTotalMinutes int = 20,\n@granularity int = 5,\n@JobId uniqueidentifier,\n\n--Output\n@AverageJobRunDuration float OUTPUT,\n@RunDurationLastExecution float OUTPUT,\n@Factor float OUTPUT,\n@IncreasionPercentageString NVARCHAR(50) OUTPUT,\n@IncreasionTotalMinutes int OUTPUT,\n@OutputMessage NVARCHAR(max) OUTPUT,\n@ThresholdExceeded BIT OUTPUT\n\n)\n\nAS\n\nBegin\n\n   SET NOCOUNT ON;\n\n------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n--Gather average runtime information\n------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n\t\tdeclare @IncreasionPercentage float\n       --@StepCount selects how many stps a specific Job has\n       declare @StepCount Int\n       Set @StepCount = (select count(*) from msdb.dbo.sysjobsteps where job_id = @JobId)\n\n       --@RowCount dynamically sets how many rows of informations must be queried from job history --&gt; its calculated between granularity and the amount of job steps for a specific job\n       declare @RowCount Int\n       Set @RowCount = @granularity * @StepCount\n\n\t   declare @TotalAvailableRows int\n\t   set @TotalAvailableRows = (\n\t   select count(*)\n\t   From msdb.dbo.sysjobs j \n\t   INNER JOIN msdb.dbo.sysjobhistory h \n\t   ON j.job_id = h.job_id \n\t   where j.enabled = 1  --Only Enabled Job\n\t   and j.job_id = @JobId\n\t   and h.step_id != 0)\n\n\t   if @TotalAvailableRows &lt;= @RowCount\n\t\tBegin \n\t\t\tif @TotalAvailableRows &lt;= @StepCount\n\t\t\t\tBegin\n\t\t\t\tDeclare @MessageReturn NVARCHAR(max)\n\t\t\t\tSet @MessageReturn= &#039;Not enough History Data for Job with ID: &#039; +cast(@JobId as nvarchar(50))+ &#039; There is only: &#039; + cast((@TotalAvailableRows\/@StepCount) as nvarchar (1)) + &#039; Runtime entrie in the history.&#039;\n\t\t\t\tPrint @MessageReturn\n\t\t\t\tset @AverageJobRunDuration = NULL\n\t\t\t\tset @RunDurationLastExecution = NULL\n\t\t\t\tset @Factor = NULL\n\t\t\t\tset @IncreasionPercentageString = NULL\n\t\t\t\tset @IncreasionTotalMinutes = NULL\n\t\t\t\tset @OutputMessage = @MessageReturn\n\t\t\t\tset @ThresholdExceeded = NULL\n\t\t\t\tReturn\n\t\t\t\tEnd\n\n\t\t\tSet @RowCount = @TotalAvailableRows\n\t\t\tset @granularity = (@RowCount - @StepCount) \/ @StepCount\n\t\tEnd\n\t\t\telse\n\t\t\t\tBegin\n\t\t\t\t\tset @RowCount = @RowCount + @StepCount\n\t\t\t\tEnd\n\t\t\tprint cast(@RowCount as NVARCHAR(max)) + &#039; Rowcount&#039;\n\t\t\tprint cast(@granularity as nvarchar(max)) + &#039; Granularity&#039;\n\n       --@SumRunDuration is for summing the total run Duration of all job executions defined in @granularity\n       declare @SumRunDuration int\n\n       Set @SumRunDuration = (Select SUM(RunDurationMinutes) from\n       (select Top (@RowCount) j.name as &#039;JobName&#039;, h.step_id as &#039;StepID&#039;,\n       msdb.dbo.agent_datetime(run_date, run_time) as &#039;RunDateTime&#039;,\n       ((run_duration\/10000*3600 + (run_duration\/100)%100*60 + run_duration%100 + 31 ) \/ 60)\n                     as &#039;RunDurationMinutes&#039;\n       From msdb.dbo.sysjobs j \n       INNER JOIN msdb.dbo.sysjobhistory h \n       ON j.job_id = h.job_id \n       where j.enabled = 1  --Only Enabled Job\n       and j.job_id = @JobId\n       and h.step_id != 0\n       Order by run_date desc, run_time desc) SubSelect)\n\n----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n--Gather last runtime information\n----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n\n       declare @RowCountLastExecution int\n       set @RowCountLastExecution = 1 * @StepCount\n\n       set @RunDurationLastExecution = (Select SUM(RunDurationMinutes) from\n       (select Top (@RowCountLastExecution) j.name as &#039;JobName&#039;, h.step_id as &#039;StepID&#039;,\n       msdb.dbo.agent_datetime(run_date, run_time) as &#039;RunDateTime&#039;,\n       ((run_duration\/10000*3600 + (run_duration\/100)%100*60 + run_duration%100 + 31 ) \/ 60)\n                     as &#039;RunDurationMinutes&#039;\n       From msdb.dbo.sysjobs j \n       INNER JOIN msdb.dbo.sysjobhistory h \n       ON j.job_id = h.job_id \n       where j.enabled = 1  --Only Enabled Job\n       and j.job_id = @JobId\n       and h.step_id != 0\n       Order by run_date desc, run_time desc) SubSelect)\n\n       --@AverageJobRunDuration calculates the avarage runtime for a single job execution. Calculateion is made through @SumRunDuration and the @granularity\n       set @AverageJobRunDuration = (@SumRunDuration - @RunDurationLastExecution) \/ @granularity\n----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n--Calculate Factor\n----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n       \n       Begin try\n              set @Factor = (1\/@AverageJobRunDuration)*@RunDurationLastExecution\n       End try\n       Begin catch\n              set @Factor = NULL\n       End catch\n\n----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n--Check if sensitivity threshold is exceeded\n----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n\n       if @Factor != 0 and @Factor !&lt; 0 and @Factor Is Not NULL\n       Begin\n              set @IncreasionPercentage = @Factor -1\n              set @IncreasionTotalMinutes = @RunDurationLastExecution - @AverageJobRunDuration\n\n\t\t\t\tif @IncreasionPercentage &gt; 0\n\t\t\t\tBegin\n\t\t\t\t\tset @IncreasionPercentageString = &#039;+&#039; + convert(nvarchar(max), @IncreasionPercentage*100) + &#039;%&#039;\n\t\t\t\tEnd\n\t\t\t\t\tElse\n\t\t\t\t\tBegin\n\t\t\t\t\tset @IncreasionPercentageString = convert(nvarchar(max), @IncreasionPercentage*100) + &#039;%&#039;\n\t\t\t\t\tEnd\n\n       End \n              Else \n              Begin\n              set @IncreasionPercentage = NULL\n\t\t\t  set @IncreasionPercentageString = NULL\n              set @IncreasionTotalMinutes = NULL\n              End\n\n       If @IncreasionPercentage Is Not Null and @IncreasionTotalMinutes is not null and @IncreasionPercentage &gt;= @SensitivityPercentage and @IncreasionTotalMinutes &gt;= @SensivityTotalMinutes\n       Begin\n              Set @OutputMessage = &#039;Job with ID: &#039; + cast(@JobId as nvarchar(200)) + &#039; has exceed the defined thresholds. &#039; + &#039;The duration has increased by factor: &#039; +  cast(@Factor as nvarchar(200)) + &#039; Which is in total &#039; + cast(@IncreasionTotalMinutes as nvarchar(200)) + &#039; minutes.&#039;\n              Set @ThresholdExceeded = 1\n       End\n       Else\n              Begin\n              Set @OutputMessage = &#039;Job with ID: &#039; + cast(@JobId as nvarchar(200)) + &#039; has not exceeded the defined threshold&#039;\n              Set @ThresholdExceeded = 0\n              End\n\nEnd\nGO\n\n\n\n<\/pre><\/div>\n\n\n<h2 class=\"wp-block-heading\" id=\"h-execution-script\">Execution Script:<\/h2>\n\n\n\n<p>In case of my customer I implemented a daily running job which is executing the stored procedure through an execution script. In this case a daily schedule was appropriate, as the ETL jobs were also running on a daily base.<\/p>\n\n\n\n<p>In the script below, you can put every job name which should be monitored in the <strong>@Jobs <\/strong>variable and divide the names by semicolons. The <strong>STRING_SPLIT<\/strong> function will separate the string by semicolons and load the cursor with the job names. Note: that this functions is available from compatibility level 130 and above.<\/p>\n\n\n\n<p>After the cursor is loaded with each job, the script will execute the stored procedure for each of them. If the threshold is exceeded for any of the provided jobs, the execution script will use SQL-Server mail to inform. To get informed you must put in the appropriate mail profile and recipients mail addresses in the <strong>@profile_name<\/strong> and <strong>@recipients <\/strong>input parameter!<\/p>\n\n\n\n<p>See below the execution script:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\ndeclare @Jobs NVARCHAR(max)\nSet @Jobs = &#039;&#039;\n\nDeclare @Job_Id uniqueidentifier\nDeclare @JobName Nvarchar(max)\n\n--Load Jobs i cursor\ndeclare jobs_cursor cursor\n       for select value from STRING_SPLIT(@Jobs, &#039;;&#039;)\n\nopen jobs_cursor\nfetch next from jobs_cursor into @JobName\n\nWhile @@FETCH_STATUS = 0\nBegin\n\n       --set JobId from Jobname\n       set @Job_Id = (select j.job_id from msdb.dbo.sysjobs j where j.name = @JobName)\n\n       --Exec SP\n       declare @AverageJobRunDurationOut float\n       declare @RunDurationLastExecutionOut int\n       declare @FactorOut float\n\t   declare @IncreasionPercentageStringOut NVARCHAR(50)\n       declare @IncreasionTotalMinutesOut int\n       declare @OutputMessageOut NVARCHAR(max)\n       declare @ThresholdExceededOut BIT\n\n       exec dbo.dbi_jobruntimeinfo @SensivityTotalMinutes = 1,\n       @JobId = @Job_Id,\n       @AverageJobRunDuration = @AverageJobRunDurationOut Output,\n       @RunDurationLastExecution = @RunDurationLastExecutionOut Output,\n       @Factor = @FactorOut Output,\n       @IncreasionTotalMinutes = @IncreasionTotalMinutesOut Output,\n       @OutputMessage = @OutputMessageOut Output,\n       @ThresholdExceeded = @ThresholdExceededOut Output,\n\t   @IncreasionPercentageString = @IncreasionPercentageStringOut Output;\n\n       Select \n\t   @JobName as &#039;Job&#039;,\n\t   @AverageJobRunDurationOut as &#039;AverageDuration&#039;,\n\t   @RunDurationLastExecutionOut as &#039;DurationLastExecution&#039;,\n\t   @IncreasionPercentageStringOut as &#039;IncreasionInPercent&#039;,\n\t   @IncreasionTotalMinutesOut as &#039;IncreasionMinutes&#039;,\n       @ThresholdExceededOut as &#039;Threshold exceeded&#039;,\n\t   @FactorOut as &#039;Factor&#039;,\n\t   @OutputMessageOut as &#039;Message&#039;\n\n\t   \n\n\t   if @ThresholdExceededOut = 1\n\t   Begin\n\n\t\tdeclare @bodycontent nvarchar(max)\n\t\tset @bodycontent = &#039;Job Name = &#039;+@JobName+&#039;; Average duration in minutes = &#039;+cast(@AverageJobRunDurationOut as nvarchar(max))+&#039;; Duration of last execution in minutes = &#039;\n\t\t+cast(@RunDurationLastExecutionOut as nvarchar(max))+&#039;; Slow down in percent = &#039;+\n\t\t@IncreasionPercentageStringOut+&#039;; Slow down in minutes = &#039;+cast(@IncreasionTotalMinutesOut as nvarchar(max))\n\t\t\n\t\tdeclare @subjectcontent nvarchar(100)\n\t\tset @subjectcontent = &#039;SQL Agent Job: &#039;+@JobName+&#039; has slowed down&#039;\n\n\t\texec msdb.dbo.sp_send_dbmail\n\t\t\t@profile_name = &#039;&#039;,\n\t\t\t@recipients  = &#039;&#039;,\n\t\t\t@subject = @subjectcontent,\n\t\t\t@body = @bodycontent\n       End\n\n       fetch next from jobs_cursor into @JobName\n\nEnd\nCLOSE jobs_cursor;\nDEALLOCATE jobs_cursor;\n<\/pre><\/div>\n\n\n<h2 class=\"wp-block-heading\" id=\"h-let-s-see-how-it-works\"><strong><u>Let\u2019s see how it works!<\/u><\/strong><\/h2>\n\n\n\n<p>For testing I have two jobs as shown below:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"401\" height=\"237\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-31.png\" alt=\"\" class=\"wp-image-33619\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-31.png 401w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-31-300x177.png 300w\" sizes=\"auto, (max-width: 401px) 100vw, 401px\" \/><\/figure>\n\n\n\n<p>The only thing which these jobs are actually doing is waiting:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"276\" height=\"294\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-32.png\" alt=\"\" class=\"wp-image-33620\" \/><\/figure>\n\n\n\n<p>The job <strong>Test_dbi_jobruntimeinfo<\/strong> has one step and the job <strong>Test_dbi_jobruntimeinfo_2<\/strong> has two steps:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"498\" height=\"203\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-33.png\" alt=\"\" class=\"wp-image-33621\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-33.png 498w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-33-300x122.png 300w\" sizes=\"auto, (max-width: 498px) 100vw, 498px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"499\" height=\"187\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-34.png\" alt=\"\" class=\"wp-image-33622\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-34.png 499w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-34-300x112.png 300w\" sizes=\"auto, (max-width: 499px) 100vw, 499px\" \/><\/figure>\n\n\n\n<p>For demonstration purpose I made sure, that the history for both jobs is empty:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"432\" height=\"276\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-35.png\" alt=\"\" class=\"wp-image-33623\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-35.png 432w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-35-300x192.png 300w\" sizes=\"auto, (max-width: 432px) 100vw, 432px\" \/><\/figure>\n\n\n\n<p>Let\u2019s start and see how the stored procedure is going to behave when no data is in the history. Therefore I\u2019m putting the name of both jobs in the <strong>@Jobs<\/strong> variable of my execution script:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"541\" height=\"110\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-36.png\" alt=\"\" class=\"wp-image-33624\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-36.png 541w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-36-300x61.png 300w\" sizes=\"auto, (max-width: 541px) 100vw, 541px\" \/><\/figure>\n\n\n\n<p>In this demonstration I\u2019m also selecting the output values from the stored procedure for showing the returned values:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"447\" height=\"174\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-37.png\" alt=\"\" class=\"wp-image-33625\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-37.png 447w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-37-300x117.png 300w\" sizes=\"auto, (max-width: 447px) 100vw, 447px\" \/><\/figure>\n\n\n\n<p>I\u2019m now executing the script and these are the values which are returned:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"602\" height=\"142\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-53.png\" alt=\"\" class=\"wp-image-33642\" style=\"width:834px;height:auto\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-53.png 602w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-53-300x71.png 300w\" sizes=\"auto, (max-width: 602px) 100vw, 602px\" \/><\/figure>\n\n\n\n<p>We can see that every value is \u201cNULL\u201d and the message says, that there is no history data available for the jobs. The stored procedure is even telling us the amount of available history data which is 0.<\/p>\n\n\n\n<p>Let\u2019s execute the jobs to generate a first entry in the history. Note: that each job step is running 35 second which would be rounded by the stored procedure to 1 minute. So the stored procedure will consider the job <strong>Test_dbi_jobruntimeinfo <\/strong>with one step as running one minute while the job <strong>Test_dbi_jobruntimeinfo_2 <\/strong>would be considered by the stored procedure as running two minutes.<br><p class=\"MsoNormal\"><span lang=\"EN-US\">The jobs are executed for the first time:<\/span><\/p><\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"602\" height=\"156\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-57.png\" alt=\"\" class=\"wp-image-33648\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-57.png 602w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-57-300x78.png 300w\" sizes=\"auto, (max-width: 602px) 100vw, 602px\" \/><\/figure>\n\n\n\n<p>Now let\u2019s execute again the script and see if we get any values:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"602\" height=\"135\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-52.png\" alt=\"\" class=\"wp-image-33641\" style=\"width:836px;height:auto\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-52.png 602w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-52-300x67.png 300w\" sizes=\"auto, (max-width: 602px) 100vw, 602px\" \/><\/figure>\n\n\n\n<p>We can see, that we still didn\u2019t get any values. As we have only one entry in the history we obviously can\u2019t compare the last run duration with the previous. But the message is now telling us, that we have \u201conly 1\u201d entry in the history.<\/p>\n\n\n\n<p>I\u2019m now executing both jobs again and after that the script and we can see from the script the following data:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"602\" height=\"143\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-50.png\" alt=\"\" class=\"wp-image-33639\" style=\"width:837px;height:auto\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-50.png 602w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-50-300x71.png 300w\" sizes=\"auto, (max-width: 602px) 100vw, 602px\" \/><\/figure>\n\n\n\n<p>We can see, that we have for the first time real data. As mentioned previously: In case that there is not enough history data to calculate the average according to the <strong>@granularity<\/strong> input parameter (which is 5 per default), the stored procedure is just taking the amount of history data which is available. So in this case were we have 2x entries in the history, the stored procedure is just taking the first duration as the average and compares the second execution to it.<\/p>\n\n\n\n<p>I\u2019m now executing the jobs again but now I\u2019m changing the waiting duration within the job steps. For the first job <strong>Test_dbi_jobruntimeinfo<\/strong>, with only one step, I\u2019m changing the wait duration to 1 minute and 35 seconds which will be considered by the stored procedure as 2 minutes. So we can expect for this job an increase of 100% returned by the stored procedure. For the other job <strong>Test_dbi_jobruntimeinfo_2<\/strong> I\u2019m doing the same but only for one step. So we can expect there an increase of 50% returned by the stored procedure:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"280\" height=\"267\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-41.png\" alt=\"\" class=\"wp-image-33629\" style=\"width:367px;height:auto\" \/><\/figure>\n\n\n\n<p>After executing the jobs with the changed duration and executing the script, we got the following values back from the stored procedure:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"602\" height=\"150\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-49.png\" alt=\"\" class=\"wp-image-33638\" style=\"width:840px;height:auto\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-49.png 602w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-49-300x75.png 300w\" sizes=\"auto, (max-width: 602px) 100vw, 602px\" \/><\/figure>\n\n\n\n<p>We can see, that we have the increase exactly as expected in the output. But the threshold value is still at \u201c0 = false\u201d, as we didn\u2019t reached the default sensitivity value in minutes of 20 minutes.<\/p>\n\n\n\n<p>I\u2019m changing now the waiting duration back to 35 seconds and execute the jobs and the script again. We can expect now a decrease, as the previous increase will be considered when the stored procedure is calculating the average value. After doing so, the following values are returned as expected:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"602\" height=\"142\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-48.png\" alt=\"\" class=\"wp-image-33637\" style=\"width:840px;height:auto\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-48.png 602w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-48-300x71.png 300w\" sizes=\"auto, (max-width: 602px) 100vw, 602px\" \/><\/figure>\n\n\n\n<p>The stored procedure is telling us, that the decrease was 0 minutes because its working with rounded values. But for the percentage value we can see a small decrease.<\/p>\n\n\n\n<p>I\u2019m now changing the wait time for the job <strong>Test_dbi_jobruntimeinfo_2<\/strong> back to 1 minute and 35 seconds but this time for both steps and execute this job again. We can see that we have now again an increase for this job:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"602\" height=\"163\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-47.png\" alt=\"\" class=\"wp-image-33636\" style=\"width:852px;height:auto\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-47.png 602w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-47-300x81.png 300w\" sizes=\"auto, (max-width: 602px) 100vw, 602px\" \/><\/figure>\n\n\n\n<p><p class=\"MsoNormal\"><span lang=\"EN-US\">In my execution script, I send a mail over the SQL-Server Mail in case when the threshold is exceeded to inform interested people:<\/span><\/p><\/p>\n\n\n\n<figure class=\"wp-block-image size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"602\" height=\"145\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-44.png\" alt=\"\" class=\"wp-image-33632\" style=\"width:1033px;height:auto\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-44.png 602w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-44-300x72.png 300w\" sizes=\"auto, (max-width: 602px) 100vw, 602px\" \/><\/figure>\n\n\n\n<p>To demonstrate the generated information mail, I overwrite the default sensitivity value in minutes, which is 20 minutes, as I\u2019m too impatient to wait 20 minutes \ud83d\ude09:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"602\" height=\"166\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-45.png\" alt=\"\" class=\"wp-image-33633\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-45.png 602w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-45-300x83.png 300w\" sizes=\"auto, (max-width: 602px) 100vw, 602px\" \/><\/figure>\n\n\n\n<p>After executing the script again, we can see, that the threshold value for the job <strong>Test_dbi_jobruntimeinfo_2 <\/strong>is now set to \u201c1 = true\u201d:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"602\" height=\"111\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-54.png\" alt=\"\" class=\"wp-image-33643\" style=\"width:837px;height:auto\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-54.png 602w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-54-300x55.png 300w\" sizes=\"auto, (max-width: 602px) 100vw, 602px\" \/><\/figure>\n\n\n\n<p>And when I check the interface of the test SMTP Server I can see the following mail:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"602\" height=\"161\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-55.png\" alt=\"\" class=\"wp-image-33644\" style=\"width:827px;height:auto\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-55.png 602w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-55-300x80.png 300w\" sizes=\"auto, (max-width: 602px) 100vw, 602px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-summary\">Summary:<\/h2>\n\n\n\n<p>For some SQL Agent jobs it can be interesting or necessary to keep an eye on it. Feel free to use the stored procedure and script from this blog or get inspired by them when you are facing a similar situation! <\/p>\n\n\n\n<p><br><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Introduction: A couple of weeks ago I had a customer who was interested in monitoring the duration of some of his SQL-Agent jobs. The jobs were running in a data warehouse environment and were executing some ETL processes for loading data. The goal was to get informed when one of these specific jobs were running [&hellip;]<\/p>\n","protected":false},"author":145,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,99],"tags":[51],"type_dbi":[],"class_list":["post-33617","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-sql-server","tag-sql-server"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.4) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Keep an eye on your SQL Agent jobs using T-SQL! - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/keep-an-eye-on-your-sql-agent-jobs-using-t-sql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Keep an eye on your SQL Agent jobs using T-SQL!\" \/>\n<meta property=\"og:description\" content=\"Introduction: A couple of weeks ago I had a customer who was interested in monitoring the duration of some of his SQL-Agent jobs. The jobs were running in a data warehouse environment and were executing some ETL processes for loading data. The goal was to get informed when one of these specific jobs were running [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/keep-an-eye-on-your-sql-agent-jobs-using-t-sql\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2024-06-12T12:50:36+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-06-12T12:50:39+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-31.png\" \/>\n<meta name=\"author\" content=\"Hocine Mechara\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Hocine Mechara\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"11 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/keep-an-eye-on-your-sql-agent-jobs-using-t-sql\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/keep-an-eye-on-your-sql-agent-jobs-using-t-sql\\\/\"},\"author\":{\"name\":\"Hocine Mechara\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/29415d02bc1b50884796a01cf649951f\"},\"headline\":\"Keep an eye on your SQL Agent jobs using T-SQL!\",\"datePublished\":\"2024-06-12T12:50:36+00:00\",\"dateModified\":\"2024-06-12T12:50:39+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/keep-an-eye-on-your-sql-agent-jobs-using-t-sql\\\/\"},\"wordCount\":1474,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/keep-an-eye-on-your-sql-agent-jobs-using-t-sql\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2024\\\/06\\\/image-31.png\",\"keywords\":[\"SQL Server\"],\"articleSection\":[\"Database Administration &amp; Monitoring\",\"SQL Server\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/keep-an-eye-on-your-sql-agent-jobs-using-t-sql\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/keep-an-eye-on-your-sql-agent-jobs-using-t-sql\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/keep-an-eye-on-your-sql-agent-jobs-using-t-sql\\\/\",\"name\":\"Keep an eye on your SQL Agent jobs using T-SQL! - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/keep-an-eye-on-your-sql-agent-jobs-using-t-sql\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/keep-an-eye-on-your-sql-agent-jobs-using-t-sql\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2024\\\/06\\\/image-31.png\",\"datePublished\":\"2024-06-12T12:50:36+00:00\",\"dateModified\":\"2024-06-12T12:50:39+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/29415d02bc1b50884796a01cf649951f\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/keep-an-eye-on-your-sql-agent-jobs-using-t-sql\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/keep-an-eye-on-your-sql-agent-jobs-using-t-sql\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/keep-an-eye-on-your-sql-agent-jobs-using-t-sql\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2024\\\/06\\\/image-31.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2024\\\/06\\\/image-31.png\",\"width\":401,\"height\":237},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/keep-an-eye-on-your-sql-agent-jobs-using-t-sql\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Keep an eye on your SQL Agent jobs using T-SQL!\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/29415d02bc1b50884796a01cf649951f\",\"name\":\"Hocine Mechara\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f771f838feed0619485da1e42ae05d771dcb446e1f4785244582280315fa73c3?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f771f838feed0619485da1e42ae05d771dcb446e1f4785244582280315fa73c3?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f771f838feed0619485da1e42ae05d771dcb446e1f4785244582280315fa73c3?s=96&d=mm&r=g\",\"caption\":\"Hocine Mechara\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/hocinemechara\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Keep an eye on your SQL Agent jobs using T-SQL! - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/keep-an-eye-on-your-sql-agent-jobs-using-t-sql\/","og_locale":"en_US","og_type":"article","og_title":"Keep an eye on your SQL Agent jobs using T-SQL!","og_description":"Introduction: A couple of weeks ago I had a customer who was interested in monitoring the duration of some of his SQL-Agent jobs. The jobs were running in a data warehouse environment and were executing some ETL processes for loading data. The goal was to get informed when one of these specific jobs were running [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/keep-an-eye-on-your-sql-agent-jobs-using-t-sql\/","og_site_name":"dbi Blog","article_published_time":"2024-06-12T12:50:36+00:00","article_modified_time":"2024-06-12T12:50:39+00:00","og_image":[{"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-31.png","type":"","width":"","height":""}],"author":"Hocine Mechara","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Hocine Mechara","Est. reading time":"11 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/keep-an-eye-on-your-sql-agent-jobs-using-t-sql\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/keep-an-eye-on-your-sql-agent-jobs-using-t-sql\/"},"author":{"name":"Hocine Mechara","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/29415d02bc1b50884796a01cf649951f"},"headline":"Keep an eye on your SQL Agent jobs using T-SQL!","datePublished":"2024-06-12T12:50:36+00:00","dateModified":"2024-06-12T12:50:39+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/keep-an-eye-on-your-sql-agent-jobs-using-t-sql\/"},"wordCount":1474,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/keep-an-eye-on-your-sql-agent-jobs-using-t-sql\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-31.png","keywords":["SQL Server"],"articleSection":["Database Administration &amp; Monitoring","SQL Server"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/keep-an-eye-on-your-sql-agent-jobs-using-t-sql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/keep-an-eye-on-your-sql-agent-jobs-using-t-sql\/","url":"https:\/\/www.dbi-services.com\/blog\/keep-an-eye-on-your-sql-agent-jobs-using-t-sql\/","name":"Keep an eye on your SQL Agent jobs using T-SQL! - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/keep-an-eye-on-your-sql-agent-jobs-using-t-sql\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/keep-an-eye-on-your-sql-agent-jobs-using-t-sql\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-31.png","datePublished":"2024-06-12T12:50:36+00:00","dateModified":"2024-06-12T12:50:39+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/29415d02bc1b50884796a01cf649951f"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/keep-an-eye-on-your-sql-agent-jobs-using-t-sql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/keep-an-eye-on-your-sql-agent-jobs-using-t-sql\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/keep-an-eye-on-your-sql-agent-jobs-using-t-sql\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-31.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2024\/06\/image-31.png","width":401,"height":237},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/keep-an-eye-on-your-sql-agent-jobs-using-t-sql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Keep an eye on your SQL Agent jobs using T-SQL!"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/29415d02bc1b50884796a01cf649951f","name":"Hocine Mechara","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f771f838feed0619485da1e42ae05d771dcb446e1f4785244582280315fa73c3?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f771f838feed0619485da1e42ae05d771dcb446e1f4785244582280315fa73c3?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f771f838feed0619485da1e42ae05d771dcb446e1f4785244582280315fa73c3?s=96&d=mm&r=g","caption":"Hocine Mechara"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/hocinemechara\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/33617","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/145"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=33617"}],"version-history":[{"count":5,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/33617\/revisions"}],"predecessor-version":[{"id":33652,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/33617\/revisions\/33652"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=33617"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=33617"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=33617"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=33617"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}