Hi
I'm trying to use the sysjobhistory table to find out if a job is already running.
I'm using :
select sjh.run_status
from msdb.dbo.sysjobhistory sjh,
msdb.dbo.sysjobs_view sj
where sj.job_id=sjh.job_id
and sjh.run_status=4
and sj.name='Full Backup'
Even though I know the Full Backup job is running, I never get any records returned.
I don't get anything returned using sp_help_jobhistory either.
Anybody got any ideas?
Thanks
Andy
Currently all of my scheduled tasks are completing successfully (I can see the results in output logs I create) but that aren't being written to the sysjobhistory table. Any ideas?
View 1 Replies View RelatedHas anyone written routines to analyze sysjobhistory? I'm looking for a tool/routine to analyze jobs for failures, trends (such as constantly increasing run times) and other information. It's not as straighforward as I had originally hoped it would be.
I am specifically prohibited from using any 3rd-party tools (too expensive per mgmt).
I would be grateful for any input/insight.
Regards,
hmscott
I see a lot of posts saying that to check if a job is currentlyexecuting one needs to look at run_status for the job in thesysjobhistory table in msdb catalog. I also see the possible valuesof that column in the help files. What gets me is that even when Iknow my job is executing (according to MMC), no record in thesysjobhistory has the run_status of 4 (In Progress). Why is that? Isthere a server-wide setting that needs to be enabled?
View 2 Replies View RelatedHi,
Can any one suggest me how to retrieve most recent job from msdb..sysjobhistory table?
I want to supply the job name which has more than 1 steps. Step 1 or more is already completed ( success/failure) and in the last step I am trying to retrieve sysjobhistory.messages(success/failure) stored in the sysjobhistory table for the steps already executed.
I want the records related with last/current job executed.
Thanks.
I am setting up a monitor to alert me if an SQL job has failed in the "last 20 minutes". This should run 24 hours a day, 7 days a week. My query looks something like this.
select * from TALMAIN.msdb.dbo.sysjobhistory where job_id = '7139D5D1-CD88-46E8-8324-5D5A0D8D3A27' and run_status <> 1 and
DATEPART(YYYY,GETDATE()) = substring(convert(char(8),run_date),1,4)and
DATEPART(MM,GETDATE()) = substring(convert(char(8),run_date),5,2) and
DATEPART(DD,GETDATE()) = substring(convert(char(8),run_date),7,2)and DATEPART(HH,GETDATE()) = substring(convert(char(8),run_time),1,2)and (DATEPART(MI,GETDATE()) - substring(convert(char(8),run_time),3,2)) <= 20.
The run_date and run_time columns in msdb..sysjobhistory are stored as integers. Tried a couple of things, but I am unable to convert both of them to datetime data type. The last conditions in the above logic hold true for only "2 digit" hour and minute values.
DATEPART(HH,GETDATE()) = substring(convert(char(8),run_time),1,2)and (DATEPART(MI,GETDATE()) - substring(convert(char(8),run_time),3,2)).
What about time values like 00:05 AM and single digit time values like 1:00 AM and 9:05 AM, for example?. I pasted some sample run_date and run_time values from sysjobhistory below.
run_date run_time
2006122821510 -- 02:15:10 AM (how to get the minute count?)
2006122821510 -- 02:15:10 AM (same as above)
20061227233014 -- 23:30:14 PM (this is strt forward)
20061227233014 -- 23:30:14 PM (same as above)
200612273016 -- 00:30:16 AM (how to get minute count?)
200612273015 -- 00:30:15 AM (how to get minute count?)
Is there a simpler logic to achieve this? Hope I was clear, else let me know. Please advise. Thank you.