Who Started That Job?

While a SQL Server job is running, details on its status are limited. The job activity monitor will show which job step is currently being processed, but it is completely opaque as to whether a transaction in that step is executing or rolling back or just waiting.

Even simple information such as “who started this job” is not available until AFTER the job completes, when the user who called the job will be written into the message field of msdb.dbo.sysjobhistory.

So what? Well, normally that is not really a problem… except when the job takes hours (or days!) to complete. Or, what if it is a continuous task such as a replication monitor? For whatever reason, the job might need to be stopped… but you might not know with whom to confirm unless you know who started the job in the first place. Of course, some jobs start with the SQL Server or based on a schedule — but for those jobs there is rarely a question as to why the job is running.

Execution details are well and truly hidden during a job run, but, thankfully, at least the job initiator is not. And Microsoft even gave us a blueprint on how to find that information. Take a look at the definition of the stored procedure msdb.dbo.sp_get_composite_job_info to see how the system execution procedure xp_sqlagent_enum_jobs is used to return some very useful job status information.

The following example breaks out the relevant bits for our purpose.

-- Create a table to store job information
    , 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 -- boolean
    , request_source INT NOT NULL
    , request_source_id SYSNAME COLLATE database_default NULL
    , running INT NOT NULL -- boolean
    , current_step INT NOT NULL
    , current_retry_attempt INT NOT NULL
    , job_state INT NOT NULL)

-- Declare and initialize variables
    @is_sysadmin INT = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)
    , @job_owner SYSNAME = N'sa'
    /* To filter on a single job, uncomment the lines below */
    --, @job_id UNIQUEIDENTIFIER = (
    -- SELECT job_id
    -- FROM msdb.dbo.sysjobs
    -- WHERE name = N'<job_name SYSNAME>')

-- Select the job information
EXECUTE xp_sqlagent_enum_jobs
    , @job_owner
    /* To filter on a single job, uncomment the line below */
    --, @job_id

-- Select the running jobs that were started by a user
    'JobName' = jobs.name
    , 'JobStartedBy' = t1.request_source_id
    , 'JobStarted' = acts.start_execution_date
    , 'CurrentTime' = GETDATE()
    , 'CurrentRunTimeSeconds' =
        DATEDIFF(SECOND, acts.start_execution_date, GETDATE())
    , 'CurrentRunTimeMinutes' =
        DATEDIFF(MINUTE, acts.start_execution_date, GETDATE())
    , 'CurrentRunTimeHours' =
        DATEDIFF(HOUR, acts.start_execution_date, GETDATE())
    , 'CurrentRunTimeDays' =
        DATEDIFF(DAY, acts.start_execution_date, GETDATE())
    @tbl AS t1
    INNER JOIN msdb.dbo.sysjobs AS jobs
        ON t1.job_id = jobs.job_id
    INNER JOIN msdb.dbo.sysjobactivity AS acts
        ON t1.job_id = acts.job_id
    t1.running = 1 -- running jobs only
    AND t1.request_source = 4 -- started by a user

Please note that there is small delay between when a job starts and when SQL Server populates information into sysjobactivity. Thus, if you are testing this with simple (read: quick-running) jobs, be sure to add a few WAITFOR DELAY commands in there to give SQL time to work. A delay of just a few seconds is sufficient.

Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: