Identifying Long Running SQL Server Agent Jobs

In my article last month, I wrote about different methods you could use to identify the state of a SQL Server Agent job. One of the methods I discussed was using the undocumented extended stored procedure (XP) "xp_sqlagent_enum_jobs." In this article, I will show you how to use the "xp_sqlagent_enum_jobs" XP to help identify long running jobs.

While researching how to identify long running jobs I found out that Microsoft SQL Server does not provide a simple method to identify how long a SQL Server Agent job has been running. Therefore I decided to build my own process. My own process of identifying long running jobs consisted of one function and one stored procedures. The function is called "fn_hex_to_char" which returns a character string that represents the hex value of a varbinary field. The stored procedure (SP) is called "usp_long_running_jobs" and it does the bulk of the work to identify long running jobs. Below you will find copies of the function code and the SP.

Code for fn_hex_to_char function:

CREATE function fn_hex_to_char (
  @x varbinary(100), -- binary hex value
  @l int -- number of bytes
  ) returns varchar(200)
 as 
-- Written by: Gregory A. Larsen
-- Date: May 25, 2004
-- Description:  This function will take any binary value and return 
--               the hex value as a character representation.
--               In order to use this function you need to pass the 
--               binary hex value and the number of bytes you want to
--               convert.
begin

declare @i varbinary(10)
declare @digits char(16)
set @digits = '0123456789ABCDEF'
declare @s varchar(100)
declare @h varchar(100)
declare @j int
set @j = 0 
set @h = ''
-- process all  bytes
while @j < @l
begin
  set @j= @j + 1
  -- get first character of byte
  set @i = substring(cast(@x as varbinary(100)),@j,1)
  -- get the first character
  set @s = cast(substring(@digits,@i%16+1,1) as char(1))
  -- shift over one character
  set @i = @i/16 
  -- get the second character
  set @s = cast(substring(@digits,@i%16+1,1) as char(1)) + @s
  -- build string of hex characters
  set @h = @h + @s
end
return(@h)
end

Code for usp_log_running_jobs SP:

CREATE proc usp_long_running_jobs as
-- Written by: Gregory A. Larsen
-- Date: May 25, 2004
-- Description: This stored procedure will detect long running jobs.  
--              A long running job is defined as a job that has 
--              been running over 6 hours.  If it detects any long
--              running job then an email is sent to the DBA's.

------------------
-- Begin Section 1
------------------

set nocount on 

declare @c char(1000)
declare @cnt int

-- Create table to hold job information
create table #enum_job ( 
Job_ID uniqueidentifier,
Last_Run_Date int,
Last_Run_Time int,
Next_Run_Date int,
Next_Run_Time int,
Next_Run_Schedule_ID int,
Requested_To_Run int,
Request_Source int,
Request_Source_ID varchar(100),
Running int,
Current_Step int,
Current_Retry_Attempt int, 
State int
)       

------------------
-- Begin Section 2
------------------

-- create a table to hold job_id and the job_id in hex character format
create table ##jobs (job_id uniqueidentifier , 
                     job_id_char varchar(100))

-- Get a list of jobs  
insert into #enum_job 
      execute master.dbo.xp_sqlagent_enum_jobs 1,
                'garbage' -- doesn't seem to matter what you put here

------------------
-- Begin Section 3
------------------

-- calculate the #jobs table with job_id's
-- and their hex character representation
insert into ##jobs 
       select job_id, dba.dbo.fn_hex_to_char(job_id,16) from #enum_job

------------------
-- Begin Section 4
------------------

-- get a count or long running jobs
select @cnt = count(*) 
     from master.dbo.sysprocesses a
          join ##jobs b
          on  substring(a.program_name,32,32)= b.job_id_char
          join msdb.dbo.sysjobs c on b.job_id = c.job_id 
     -- check for jobs that have been running longer that 6 hours.
     where login_time < dateadd(hh,-6,getdate())

------------------
-- Begin Section 5
------------------

if @cnt > 0 
  -- Here are the long running jobs  
exec master.dbo.xp_sendmail                   
      @recipients='Greg.Larsen@databasejournal.com',
      @subject='Jobs Running Over 6 hours',
      @query= 'select substring(c.name,1,78) 
              ''These jobs have been running longer than 6 hours'' 
              from master.dbo.sysprocesses a  
              join ##jobs b
              on  substring(a.program_name,32,32)= b.job_id_char
              join msdb.dbo.sysjobs c on b.job_id = c.job_id 
              where login_time < dateadd(hh,-6,getdate())'

drop table #enum_job
drop table ##jobs
GO 

Let me walk you through the code for both of these objects. First, let me review the code for "fn_hex_to_char" function.

The "fn_hex_to_char" function takes a varbinary parameter and converts it to a string of characters that represent the hex value of the varbinary value. I need this function in my process to find long running jobs, so I can convert a "job_id" column into a varchar variable that represents the job_ids hex character values. This function has two parameters; the first parameter "@x" is the varbinary value that needs to be converted. The second parameter "@l" is the number of bytes that needed to be converted.

This function then goes through a while loop to process through the @x parameter one binary byte at a time and converts it to two different hex characters. It is two hex characters because a single binary byte is represented by two hex characters. Remember now that the binary values are represented by a combination of any of the following 16 characters "0123456789ABCDEF." Inside the while loop, the following code snippet "substring(@digits,@i%16+1,1)" uses modulo operator "%", to index into char variable @digits to identify the hex character that matches one of the four bits of the binary byte being converted. After the while loop is done processing through all bytes of the varbinary column, the variable "@h" holds the hex character representation of the varbinary column. The "@h" variable is then returned by the function.

Now let me walk through the "usp_log_running_jobs" SP code one section at a time. The first section "Section 1" declares local variables and a temporary table "#enum_job." This temporary table is use to hold the output of the undocumented extended stored procedure (XP) "xp_sqlagent_enum_jobs."

The next section, "Section 2" creates a global temporary table "##jobs" to hold two different representations of "job_id", one in varbinary format and one that holds the hex character format of a job_id. Then the "#enum_job" temporary table is populated using "sp_sqlagent_enum_jobs" XP. Note that the first parameter passed to the XP is a "1." This tells this XP to return the status of all jobs on the server. After this section is done the #enum_job temporary table, will contain one row for each job on the server.

"Section 3" populates table "##jobs" using a SELECT statement. This SELECT statement returns a "job_id" column, and converts the "job_id" column using function "fn_hex_to_char." A global table is used here because later on in the code this table will be passed to another XP, and therefore a global table needs to be used, since a temporary table would be out of scope for this other XP.

In "Section 4," I identify the count of the number of jobs that have been running longer than 6 hours. This is done by a SELECT statement that joins "master.dbo.sysprocesses", ##jobs and the "msdb.dbo.sysjobs" tables. The key to getting the SELECT statement to work is to join the "master.dbo.sysprocesses" table with the ##jobs table on the "job_id" that is contained in the "program_name" column in the "master.dbo.sysprocesses" table.

The final section, "Section 5," emails me the name of the SQL Agent jobs that have been running longer than 6 hours. The email is only sent if this process identifies jobs that have been running longer than 6 hours. I use "xp_sendmail" XP to send the email.

I have found it useful to have this automated tool to tell me each morning if there are any SQL Agent jobs that are still running. By getting the long running jobs email when I first come in I can quickly respond to any issues there might be with having these batch processes running while there is online access.