AppInsight for SQL: How "SQL Job Agent Info" Monitor works

11/29/2018 10:53 PM

6/25/2020 9:50 PM

This article provides information on how AppInsight for SQL monitors SQL Jobs

Server Application Monitor

1. The Monitor runs the following script against the SQL Server being monitored:

-- Scripts are not supported under any SolarWinds support program or service. 
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further 
-- disclaims all warranties including, without limitation, any implied warranties 
-- of merchantability or of fitness for a particular purpose. The risk arising 
-- out of the use or performance of the scripts and documentation stays with you. 
-- In no event shall SolarWinds or anyone else involved in the creation, 
-- production, or delivery of the scripts be liable for any damages whatsoever 
-- (including, without limitation, damages for loss of business profits, business 
-- interruption, loss of business information, or other pecuniary loss) arising 
-- out of the use of or inability to use the scripts or documentation.
SELECT
    j.name,
    j.job_id, 
    a.last_executed_step_date,
    h.run_status,
    h.run_duration
FROM [msdb].[dbo].[sysjobs] j
LEFT JOIN [msdb].[dbo].[sysjobactivity] a on j.job_id=a.job_id
LEFT JOIN [msdb].[dbo].[sysjobhistory] h on a.job_history_id=h.instance_id
WHERE h.step_id=0

2. Each Job's "Run_Status" column value is reviewed.
3. The retrieved values are reported back by the Monitor.

 

The possible values are:

 

0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress

 

• If a job was never run, then it has a status of Available (Up)

• If a last run was successful, the status is calculated based on the duration threshold

• If the last run has a status of Failed, the status is set to Critical

• If the last run has a status of Retry or Cancelled, the status is set to Warning

 

Note: SAM does not poll information about the job scheduling state.