SQL: Long Running Jobs
Author: jason
Date: 2014-02-05 15:56:50
Category: Musing

There are quite a few tools out there that will monitor SQL for long running transactions, processes, and jobs. Below is an easy free solution for notifying on long running jobs, where long running is arbitrary. Its just a Job that checks other Jobs.

Here is a query that lists rows of running jobs. Use the MaxSeconds variable as a filter.




I used pieces from: http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=LongRunningJobMon

Now, to get that info into a usable/emailable format I concatenated the rows into a string. Then I used RAISERROR if the string contains something.




To make a Notification:
1. Set up DBMail: http://technet.microsoft.com/en-us/library/ms187605.aspx
2. Configure an Operator: http://technet.microsoft.com/en-us/library/ms175962.aspx
3. Create a SQL Server Agent Job: http://technet.microsoft.com/en-us/library/ms190268.aspx
4. Add the script as a Step
5. In the Notifications section of the new Job, check Email and select your Operator for When Job Fails
6. Schedule the Job to run at whatever interval, 1 hour, 4 hours
7. If the job finds other jobs taking too long it will raise an error and send an email





List Running Jobs
Code

DECLARE @MaxSeconds INT = 0;
SELECT p.SPID, j.name AS Job_Name, p.[Program_Name],
ISNULL(DATEDIFF(MI, p.last_batch, GETDATE()), 0) AS [Minutes_Running], Last_Batch
FROM master.dbo.sysprocesses p WITH(NOLOCK)
JOIN msdb.dbo.sysjobs j WITH(NOLOCK) ON (SUBSTRING(LEFT(j.job_id,8),7,2) +
SUBSTRING(LEFT(j.job_id,8),5,2) +
SUBSTRING(LEFT(j.job_id,8),3,2) +
SUBSTRING(LEFT(j.job_id,8),1,2)) = SUBSTRING(p.program_name,32,8)
WHERE p.[program_name] LIKE 'SQLAgent - TSQL JobStep (Job %'
AND ISNULL(DATEDIFF(SS, p.last_batch, GETDATE()), 0) > @MaxSeconds



Raise an Error on Long Running Jobs (change MaxSeconds)
Code

DECLARE @MaxSeconds INT = 0;
DECLARE @Job VARCHAR(MAX) = (
SELECT COALESCE(STUFF((SELECT ',SPID: ' +
CAST(p.SPID AS VARCHAR(50)) + ' JobName: ' + j.name + ' Running for: ' +
CAST(ISNULL(DATEDIFF(MI, p.last_batch, GETDATE()), 0) AS VARCHAR(255)) + ' Minutes Started: ' +
CAST(Last_Batch AS VARCHAR(50))
FROM master.dbo.sysprocesses p WITH(NOLOCK)
JOIN msdb.dbo.sysjobs j WITH(NOLOCK) ON (SUBSTRING(LEFT(j.job_id,8),7,2) +
SUBSTRING(LEFT(j.job_id,8),5,2) +
SUBSTRING(LEFT(j.job_id,8),3,2) +
SUBSTRING(LEFT(j.job_id,8),1,2)) = SUBSTRING(p.program_name,32,8)
WHERE p.[program_name] LIKE 'SQLAgent - TSQL JobStep (Job %'
AND ISNULL(DATEDIFF(SS, p.last_batch, GETDATE()), 0) > @MaxSeconds
FOR XML PATH('')), 1, 1, ''), '') AS LongRunningJobs
)
IF LEN(ISNULL(@Job,'')) > 5 BEGIN
RAISERROR(@Job, 16,1)
END




jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009