SQL Server Agent jobs do not display in AppInsight for SQL

10/9/2018 7:58 PM

12/23/2025 5:27 PM

This article provides brief information and steps to resolve the following error:

Agent is not set up
Either the Agent is not enabled on SQL server or SQL Server Agent component has been disabled.

Resolution options include enabling an agent job or using a query. Two queries are available depending on your version of SAM.

Server Application Monitor

The issue can either be caused by permissions or the component being disabled.

 

 

We provide three options to resolve this issue. See the following options through the SolarWinds Platform Web Console or running a query.

Enable SQL Agent Job Info 

1. Open the SolarWinds Platform Web Console.
2. Go to Application Details page for the AppInsight for the target SQL Instance.
3. Edit the Application Monitor.
4. Find SQL Agent Job Info, and set it to  Enabled.

   

Query for SAM 6.2.4 and earlier 

1. Log in to the target SQL Instance using SQL Server Management Studio.
   Note: You must use the same credentials used by SAM for polling.
2. Run the following query:

   -- 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

The possible values are:

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

Note: If this runs into any error, you may have a permissions issue with the credentials or the SQL Server Agent is not running.

Query for SAM 6.3 and later 

As of SAM 6.3, the script has been updated to resolve issues with of Last Run Time.

The new query is now as follows:

-- 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.

WITH JobLastestSessionDate(job_id, agent_start_date) AS (
    SELECT
        a.[job_id],
        MAX(s.[agent_start_date]) AS [agent_start_date]
    FROM msdb.dbo.[sysjobactivity] a
    JOIN msdb.dbo.[syssessions] s
        ON s.[session_id] = a.[session_id]
    GROUP BY [job_id]
)
,JobLatestSessionId(job_id, session_id) AS (
    SELECT
        a.[job_id],
        a.[session_id]
    FROM msdb.dbo.[sysjobactivity] a
    JOIN msdb.dbo.[syssessions] s
        ON a.[session_id] = s.[session_id]
    JOIN [JobLastestSessionDate] jlsd
        ON jlsd.[job_id] = a.[job_id]
            AND s.[agent_start_date] = jlsd.[agent_start_date]
)
,LatestSessionDate(agent_start_date) AS (
    SELECT
        MAX(agent_start_date) AS [agent_start_date]
    FROM msdb.dbo.[syssessions]
)
,LatestSessionId(session_id) AS (
    SELECT
        [session_id]
    FROM msdb.dbo.[syssessions] s
    JOIN [LatestSessionDate] lsd
        ON s.[agent_start_date] = lsd.[agent_start_date]
)
,JobLatestHistoryId(job_id, job_history_id) AS (
    SELECT
        a.[job_id],
        MAX(a.[job_history_id]) AS [job_history_id]
    FROM msdb.dbo.[sysjobactivity] a
    JOIN msdb.dbo.[sysjobhistory] h
        ON a.[job_history_id] = h.[instance_id]
    GROUP BY a.[job_id]
)
,JobCurrentHistory(job_id, session_id, job_history_id) AS (
    SELECT
        a.[job_id],
        a.[session_id],
        a.[job_history_id]
    FROM msdb.dbo.[sysjobactivity] a
    JOIN [JobLatestHistoryId] lh
        ON a.[job_id] = lh.[job_id]
            AND a.[job_history_id] = lh.[job_history_id]
)
,JobCurrentSession(job_id, session_id, job_history_id) AS (
    SELECT
        a.[job_id],
        a.[session_id],
        a.[job_history_id]
    FROM msdb.dbo.[sysjobactivity] a
    JOIN [JobLatestSessionId] ls
        ON a.[job_id] = ls.[job_id]
            AND a.[session_id] = ls.[session_id]
    WHERE a.[job_id] NOT IN (SELECT [job_id] FROM [JobCurrentHistory])
)
,JobCurrent(job_id, session_id, job_history_id, start_execution_date, run_status, run_duration) AS (
    SELECT
        jch.[job_id],
        jch.[session_id],
        jch.[job_history_id],
        a.[start_execution_date],
        h.[run_status],
        h.[run_duration]
    FROM [JobCurrentHistory] jch
    JOIN msdb.dbo.sysjobactivity a
        ON a.job_id = jch.job_id
            AND a.job_history_id = jch.job_history_id
    JOIN msdb.dbo.sysjobhistory h
        ON jch.job_history_id = h.instance_id
    UNION ALL
    SELECT
        jcs.[job_id],
        jcs.[session_id],
        jcs.[job_history_id],
        a.[start_execution_date],
        NULL,
        NULL
    FROM [JobCurrentSession] jcs
    LEFT JOIN msdb.dbo.sysjobactivity a
        ON jcs.job_id = a.job_id AND jcs.session_id = a.session_id
)
SELECT
    j.[name],
    j.[job_id],
    jc.[session_id],
    jc.[start_execution_date],
    jc.[run_status],
    jc.[run_duration]
INTO #job_results
FROM msdb.dbo.[sysjobs] j
JOIN [JobCurrent] jc
    ON j.[job_id] = jc.[job_id]
WHERE j.[enabled] = 1

IF EXISTS (SELECT * FROM master.sys.sysobjects  WHERE name = 'xp_sqlagent_enum_jobs')
BEGIN
    DECLARE @processed BIT
    DECLARE @date DATETIME
    SET @processed = 0
    SET @date = GETDATE()

    CREATE TABLE #running_job_results (
        job_id uniqueidentifier NOT NULL,
        last_run_date int NOT NULL,
        last_run_time int NOT NULL,
        next_run_date int NOT NULL,
        next_run_time int NOT NULL,
        next_run_schedule_id int NOT NULL,
        requested_to_run int NOT NULL,
        request_source int NOT NULL,
        request_source_id sysname COLLATE database_default NULL,
        running int NOT NULL,
        current_step int NOT NULL,
        current_retry_attempt int NOT NULL,
        job_state int NOT NULL
    )

    BEGIN TRY
        INSERT #running_job_results
            EXEC master.dbo.xp_sqlagent_enum_jobs @is_sysadmin = 1, @job_owner = ''
        SET @processed = 1
    END TRY
    BEGIN CATCH
        SET @processed = 0
    END CATCH;

    IF @processed = 1
    BEGIN
        UPDATE #job_results
            SET run_status = NULL,
                run_duration = NULL
        WHERE run_status = 4
            AND job_id NOT IN (
                SELECT job_id
                FROM #running_job_results
                WHERE running = 1
            )

        ;WITH RunningJobDuration(job_id, start_execution_date, run_status, run_duration, sec) as (
            SELECT
                res.[job_id],
                res.[start_execution_date],
                res.[run_status],
                res.[run_duration],
                DATEDIFF(SECOND, a.[start_execution_date], @date)
            FROM [#job_results] res
            LEFT JOIN [#running_job_results] rjr
                ON rjr.[job_id] = res.[job_id]
            LEFT JOIN msdb.dbo.[sysjobactivity] a
                ON a.[job_id] = res.[job_id]
                    AND a.[session_id] = res.[session_id]
            WHERE rjr.[running] = 1
                AND a.[start_execution_date] IS NOT NULL
                AND a.[stop_execution_date] IS NULL
        ),
        RunningJobDurationHMS(job_id, start_execution_date, run_status, run_duration, dur_hour, dur_min, dur_sec) as (
            SELECT
                [job_id],
                [start_execution_date],
                [run_status],
                [run_duration],
                [sec] / 3600,
                ([sec] % 3600) / 60,
                [sec] % 60
            FROM [RunningJobDuration]
        )
        UPDATE [RunningJobDurationHMS]
            SET [run_status] = 4
                ,[run_duration] = [dur_hour] * 10000 + [dur_min] * 100 + [dur_sec]
        DROP TABLE #running_job_results
    END
END

SELECT
    [name],
    [job_id],
    DATEADD(SECOND, DATEDIFF(SECOND, GETDATE(), GETUTCDATE()), [start_execution_date]) AS [last_executed_step_date],
    [run_status],
    [run_duration]
FROM [#job_results]

DROP TABLE #job_results

The possible values are:

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