Job status and Information
-
Hi guys,
I'm trying to create a centralized monitoring system for all the jobs running in all our SQL servers. What I'd like to know is the information on the jobs similar to what you see in enterprise manager whan you go to SQLAgent>Jobs. In addition, I would like to see the schedule, and the duration or the time the job started running (current).
I tried looking for these info from msdb jobs tables but I can't seem to fine the status, and the start time of the currently running jobs. Sysjobshistory tells me the info when the job finishes.
My main purpose is to be able to go thru my servers and see which jobs are running longer that expected. Note: running, not failed (i can use the notification part for failure or completion).
TIA
-
Create table tRunningJob
(
HostName VARCHAR(25),
Step INT,
Job VARCHAR(17),
JobDuration int,
Status VARCHAR(10),
LastBatch VARCHAR(25)
)
CREATE TABLE tStuckJob
(
ServerName VARCHAR(30),
JobName VARCHAR(100),
StepNumber INT,
Duration VARCHAR(20)
)
Your Job should have these steps
1. --empty the tables of old data
DELETE tRunningJob
DELETE tStuckJob
2. --gather data on Jobs (this script is the same as pulling specific information from sp_who2)
--for 2000
INSERT INTO tRunningJob
SELECT sj.originating_server AS Server,
SUBSTRING(REVERSE(RTRIM(program_name)),2,1) AS Step,
STUFF(REVERSE(SUBSTRING(REVERSE(RTRIM(program_name)),11,16)),5,0,'-') AS Job,
DATEDIFF(MI, last_batch, GETDATE()) AS JobDuration,
status,
last_batch
FROM [servername].master.dbo.sysprocesses sp
JOIN [servername].msdb.dbo.sysjobs sj
ON STUFF(REVERSE(SUBSTRING(REVERSE(RTRIM(sp.program_name)),11,16)),5,0,'-') = RIGHT(sj.job_id, 17)
WHERE program_name LIKE 'SQLAgent - TSQL JobStep %'
--for 2005
same script as above, but change master.dbo.sysprocesses to master.sys.sysprocesses
3. --get the jobs that are running too long. Either longer than it did previously or over one hour.
--for 2000 / 2005
INSERT INTO tStuckJob (servername, jobname, stepnumber, duration)
SELECT [servername], sj.name AS jobname, sjs.step_id,
CONVERT(VARCHAR(10),(jd.jobduration / 60.0)) + ' Hours' AS Duration
FROM [servername].msdb.dbo.sysjobs sj
JOIN [servername].msdb.dbo.sysjobsteps sjs
ON sj.job_id = sjs.job_id
JOIN [rptingserver].SQLData.dbo.tRunningJob jd
ON RIGHT(sjs.job_id, 17) = jd.job
AND sjs.step_id = jd.step
WHERE (jd.jobduration > sjs.last_run_duration)
OR (jd.jobduration / 60.0) > 1
We run this script on one server (rptingserver) and have it run against every server we maintain. If you are going to run it on the same server you are checking, don't use the [servername] or [rptingserver] qualifiers.
You will have to make your own adjustment to the final line ( OR (jd.jobduration / 60.0) > 1). I use that line since the previous job may have run too long. If so, then I want to know if it's running longer than one hour. For example, a job normally runs 30 minutes. The last time it ran 1.5 hours and I got an alert. The next time it won't report if it has only been running for 1.25 (that's less than the LAST run duration) - except for that last line which forces the report since it's been over an hour.
Look at what it does and where it's pulling information from. Then tweak it for your needs.
-SQLBill
-
sysprocesses -- thanks Bill! I'll have a look.
-
I wrote a script long ago that does most of the things you are looking for and it is avaialble here:
http://education.sqlfarms.com/ShowPost.aspx?PostID=58
(sorry for pointing to another knowlegde base site, but that's where I put all my scripts...)
Editor's Note: : Dr. Omri Bahat works for SQLFarms, a software vendor of SQL Server tools.
-
Did you take a look target server /target server info in BOL...
It is very helpful to monitory from one centralized location...
MohammedU
Microsoft SQL Server MVP -
you can find the info in sysjobhistroy table itself it has a column called status which either has a value of 0 for failed and a value of 1 for successfull ones. also u will have the jobs active start time, job run duration, next run time, etc. just check these tables
sysjobs
sysjobhistory
sysjobschedules
sysjobsteps
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply