Submit a ticketCall us

Looking to compare latest NPM features with previous versions of NPM?
The NPM new feature summary offers a comparison of new features and improvements offered with this release.

 

Home > Success Center > Server & Application Monitor (SAM) > SQL Server Agent jobs do not display in AppInsight for SQL

SQL Server Agent jobs do not display in AppInsight for SQL

Created by Tiarnan Stacke, last modified by Lori Krell on May 23, 2017

Views: 119 Votes: 0 Revisions: 8

Updated: 5-23-2017

Overview

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.

Environment

SAM version 6.1 or later

 

Cause 

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

Please see this article for another possible cause when monitoring SQL clusters.

 

Resolution

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

Enable SQL Agent Job Info

  1. Open the Orion 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:
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:

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

 

 

Last modified
14:24, 23 May 2017

Tags

Classifications

Public