July 5, 2012 at 5:20 am
Hello Masters,
Could you please provide script for find the long running jobs in all servers(not for single server)?
Thanks in Advance..
July 5, 2012 at 11:13 am
I would recommend you do a little investigation and look for scripts that check for long running jobs. I have no doubt there are some on this site. From there you would probably want to use PowerShell along with a CMS to iterate through all the servers and run that script against all the machines to get the information that you are looking for.
Of course this all depends on what you mean by a long running job. Is that a job that runs for over 30 minutes or over 2 days? Would it be one that runs more that twice the standard deviation? Those are all going be questions you have to answer yourself before you can consider deploying anything.
July 5, 2012 at 3:40 pm
If you are using SQL Server 2008 and above, You can use the Inbuild reports which gives you the long running queries for all the Databases.
Regards
Srikanth Reddy Kundur
July 6, 2012 at 12:14 pm
we have third party tools which monitor these long running queries. Try to download the trial version.
September 1, 2012 at 4:20 am
HI mahesh try to use
SELECT
j.job_id AS 'JobId',
name AS 'JobName',
start_execution_date AS 'StartTime',
stop_execution_date AS 'StopTime',
avgruntimeonsucceed,
DATEDIFF(s,start_execution_date,GETDATE()) AS 'CurrentRunTime',
CASE WHEN stop_execution_date IS NULL THEN
DATEDIFF(ss,start_execution_date,stop_execution_date) ELSE 0 END 'ActualRunTime',
CASE
WHEN stop_execution_date IS NULL THEN 'JobRunning'
WHEN DATEDIFF(ss,start_execution_date,stop_execution_date)
> (AvgRunTimeOnSucceed + AvgRunTimeOnSucceed * .05) THEN 'LongRunning-History'
ELSE 'NormalRunning-History'
END 'JobRun',
CASE
WHEN stop_execution_date IS NULL THEN
CASE WHEN DATEDIFF(ss,start_execution_date,GETDATE())
> (AvgRunTimeOnSucceed + AvgRunTimeOnSucceed * .05) THEN 'LongRunning-NOW'
ELSE 'NormalRunning-NOW'
END
ELSE 'JobAlreadyDone'
END AS 'JobRunning'
FROM msdb.dbo.sysjobactivity ja
INNER JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id
INNER JOIN (
SELECT job_id,
AVG
((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100)
+
STDEV
((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100) AS 'AvgRuntimeOnSucceed'
FROM msdb.dbo.sysjobhistory
WHERE step_id = 0 AND run_status = 1
GROUP BY job_id) art
ON j.job_id = art.job_id
WHERE
(stop_execution_date IS NULL) OR
(DATEDIFF(ss,start_execution_date,stop_execution_date) > 60
AND
CAST(LEFT(start_execution_date,11) AS DATETIME) = CAST(LEFT(GETDATE(),11) AS DATETIME))
ORDER BY start_execution_date DESC
Thanks
Naga.Rohitkumar
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply