July 20, 2006 at 11:24 am
I have a question relating to how active jobs are logged in the sysprocesses table. This table represents active processes on the SQL Server. When I launch a job, it does in fact show up in this table w/ the job id in the program name column.
However... when the job references a perl.exe or a step fails and has retries defined, the job no longer shows up in the sysprocesses table as described above. When the perl step completes -- or retries complete/exhausted -- the job continues as it should, yet does not appear again in sysprocesses as I'd expect.
Has anyone run into this and can explain why? I wrote a proc that runs a job and sits dormant until the job completes for a client. The job handles the above -- as it uses sysjobhistory to monitor the status -- but the client did wonder why?
July 20, 2006 at 1:20 pm
Might be better using this:
create
table #enum_job (
Job_ID
uniqueidentifier,
Last_Run_Date
int,
Last_Run_Time
int,
Next_Run_Date
int,
Next_Run_Time
int,
Next_Run_Schedule_ID
int,
Requested_To_Run
int,
Request_Source
int,
Request_Source_ID varchar
(100),
Running
int,
Current_Step
int,
Current_Retry_Attempt
int,
State
int
)
insert
into #enum_job
(
[Job_ID],
[Last_Run_Date]
,
[Last_Run_Time]
,
[Next_Run_Date]
,
[Next_Run_Time]
,
[Next_Run_Schedule_ID]
,
[Requested_To_Run]
,
[Request_Source]
,
[Request_Source_ID]
,
[Running]
,
[Current_Step]
,
[Current_Retry_Attempt]
,
[State]
)
execute master.dbo.xp_sqlagent_enum_jobs 1,
'garbage' -- doesn't seem to matter what you put here
select
* from #enum_job where state = 1
You could then test the validity of the job status, if you search this site for xp_sqlagent_enum_jobs you should be able to figure out what state is
July 20, 2006 at 1:33 pm
I could -- and considered doing that.
However -- the proc returns a lot of info I didn't need and didn't tell me a lot of what I did need.
What I have is working and meets every requirement. The question is why the job_id no longer shows up in sysprocesses when an external application is referenced or a step fails and begins retrying.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply