You may find yourself in a position where you need to look at what jobs have been running over a specified
period of time. SQL Server has a stored procedure sp help job that will allow you to look at the jobs and
what their status is currently, but maybe you need to do some additional processing like sending a database
mail email to someone so that they know to look at the job. Perhaps you want to have the system check
itself before the start of business each day to ensure that no overnight jobs got stuck and are still running
which could cause degraded performance or problems with processing during the day. The following code
will work on SQL 2000, 2005, and 2008.
This function is used to convert the sysjobs.jobid field into the processid that will show up in
sysprocesses.program_name field for an executing job.
Return to Top
The following code will return a row for each job that is currently running and has been running for
over the number of minutes set in the @MaxMinutes variable. To adjust the time frame it looks for,
just change this variable value.
You may find yourself in a position where you need to look at what jobs have been running over a specified
period of time. SQL Server has a stored procedure sp help job that will allow you to look at the jobs and
what their status is currently, but maybe you need to do some additional processing like sending a database
mail email to someone so that they know to look at the job. Perhaps you want to have the system check
itself before the start of business each day to ensure that no overnight jobs got stuck and are still running
which could cause degraded performance or problems with processing during the day. The following code
will work on SQL 2000, 2005, and 2008.
This function is used to convert the sysjobs.jobid field into the processid that will show up in
sysprocesses.program_name field for an executing job.
Return to Top
The following code will return a row for each job that is currently running and has been running for
over the number of minutes set in the @MaxMinutes variable. To adjust the time frame it looks for,
just change this variable value.
period of time. SQL Server has a stored procedure sp help job that will allow you to look at the jobs and
what their status is currently, but maybe you need to do some additional processing like sending a database
mail email to someone so that they know to look at the job. Perhaps you want to have the system check
itself before the start of business each day to ensure that no overnight jobs got stuck and are still running
which could cause degraded performance or problems with processing during the day. The following code
will work on SQL 2000, 2005, and 2008.
JobID to ProcessID Function
This function is used to convert the sysjobs.jobid field into the processid that will show up in
sysprocesses.program_name field for an executing job.
CREATE FUNCTION dbo.udf_SysJobs_GetProcessid(@job_id uniqueidentifier)
RETURNS VARCHAR(8)
AS
BEGIN
RETURN (substring(left(@job_id,8),7,2) +
substring(left(@job_id,8),5,2) +
substring(left(@job_id,8),3,2) +
substring(left(@job_id,8),1,2))
END
Return to Top
TSQL Code to Find Jobs Running Over x Minutes
The following code will return a row for each job that is currently running and has been running for
over the number of minutes set in the @MaxMinutes variable. To adjust the time frame it looks for,
just change this variable value.
DECLARE @MaxMinutes int
SET @MaxMinutes = 30
SELECT p.spid,
j.name,
p.program_name,
isnull(DATEDIFF(mi, p.last_batch, getdate()), 0) [MinutesRunning],
last_batch
FROM master..sysprocesses p
JOIN msdb..sysjobs j ON dbo.udf_sysjobs_getprocessid(j.job_id) = substring(p.program_name,32,8)
WHERE program_name like 'SQLAgent - TSQL JobStep (Job %'
AND isnull(DATEDIFF(mi, p.last_batch, getdate()), 0) > @MaxMinutes
You may find yourself in a position where you need to look at what jobs have been running over a specified
period of time. SQL Server has a stored procedure sp help job that will allow you to look at the jobs and
what their status is currently, but maybe you need to do some additional processing like sending a database
mail email to someone so that they know to look at the job. Perhaps you want to have the system check
itself before the start of business each day to ensure that no overnight jobs got stuck and are still running
which could cause degraded performance or problems with processing during the day. The following code
will work on SQL 2000, 2005, and 2008.
JobID to ProcessID Function
This function is used to convert the sysjobs.jobid field into the processid that will show up in
sysprocesses.program_name field for an executing job.
CREATE FUNCTION dbo.udf_SysJobs_GetProcessid(@job_id uniqueidentifier)
RETURNS VARCHAR(8)
AS
BEGIN
RETURN (substring(left(@job_id,8),7,2) +
substring(left(@job_id,8),5,2) +
substring(left(@job_id,8),3,2) +
substring(left(@job_id,8),1,2))
END
Return to Top
TSQL Code to Find Jobs Running Over x Minutes
The following code will return a row for each job that is currently running and has been running for
over the number of minutes set in the @MaxMinutes variable. To adjust the time frame it looks for,
just change this variable value.
DECLARE @MaxMinutes int
SET @MaxMinutes = 30
SELECT p.spid,
j.name,
p.program_name,
isnull(DATEDIFF(mi, p.last_batch, getdate()), 0) [MinutesRunning],
last_batch
FROM master..sysprocesses p
JOIN msdb..sysjobs j ON dbo.udf_sysjobs_getprocessid(j.job_id) = substring(p.program_name,32,8)
WHERE program_name like 'SQLAgent - TSQL JobStep (Job %'
AND isnull(DATEDIFF(mi, p.last_batch, getdate()), 0) > @MaxMinutes
