One common surprise I have encountered in almost all my DBA environments is that a sql job that usually runs <x> minutes, sometimes can take <2x> minutes or even more time to finish.

Case Scenario: (Monitor / alert long running sql jobs)

We have hundreds of sql instances supporting many 3rd party applications, and there are many sql jobs that are part of these applications, all the jobs are supposed to finish within a few seconds to a few minutes, some may take longer up to about 2 hours. But no job should take longer than 8 hours. However, from time to time, we do see jobs running the whole weekend and still not  finishing on Monday morning and start to impact users. Usually we do not know this until users start to complain.

So from proactive monitoring perspective, how can we monitor these jobs and alert the application support teams if the job is running longer than 8 hours?

PowerShell Solution:

The solution logic is very straight-forward, using sp_help_job @execution_status = 1 to get the list of jobs running. and then get the job start information from msdb.dbo.sysjobactivity.

## Tested against sql2005+, PS V2
[int]$min_ago = 10 ## if job has been running longer than $min_ago, we will capture it
[string]$sqlInstance = "MyServer"
$jobSet = invoke-sqlcmd -Server $sqlInstance -Query "exec msdb.dbo.sp_help_job @execution_status=1" -Database "msdb";
#Here-String
[string]$query = @"
set nocount on;
declare @session_id int;
select @session_id = max(session_id) from msdb.dbo.syssessions;
select JobName = j.name
, JobStartTime=a.start_execution_date
, Duration_Min=datediff(minute, a.start_execution_date, getdate())
from dbo.sysjobactivity a
inner join dbo.sysjobs j
on j.job_id = a.job_id
and j.name in (`$(Jobs))
and a.stop_execution_date is null
and a.session_id = @session_id
and a.start_execution_date < dateadd(minute, $min_ago, getdate());
"@;
if ($jobSet -ne $null)
{
$jobSet | ForEach-Object -Begin {[string] $jobname_list="";} -Process { $jobname_list += "'" + $_.name + "'" +","} -End {$jobname_list = $jobname_list.substring(0,$jobname_list.Length-1);}
[string[]]$var = "Jobs=$jobname_list";
invoke-sqlcmd -Server $sqlInstance-Query $query -database "msdb" -variable $var | select JobName, JobStartTime, Duration_Min | ft -auto -Wrap
}

Advantage Analysis:

When I initially thought of this case, I thought it would be pretty easy by first creating a proper temp table, and then

insert into #tmp_table exec msdb.dbo.sp_help_job @execution_status = 1;

Then I can join this #tmp_table with msdb.dbo.sysjobactivity to find out the jobs in question. However, when I run the t-sql, I will get the following error

Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 72

An INSERT EXEC statement cannot be nested.

However, using PS, I avoid this error. In my real monitoring script, an alert with all the necessary info will be sent out via email to DBAs once a long-running job is detected.

Other Thoughts:

There are a few times that pure t-sql solution is not ideal or efficient. The above mentioned issue is a one. There is another one, say, I have a variable $tbl, and during my business process, this $tbl may have different value pointing to the real table names, and I want to be able to do the following:

select * from $tbl

This is not possible in current t-sql (except for dynamic t-sql which will make the script very “spaghetti”). Yet with PS, this is easy to achieve by setting the correct –variable parameter in the invoke-sqlcmd cmdlet.