SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sysJobActivity showing jobs from years ago that never ended


sysJobActivity showing jobs from years ago that never ended

Author
Message
RossRoss
RossRoss
Old Hand
Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)

Group: General Forum Members
Points: 333 Visits: 763
I've noticed that sysJobActivity shows a number of jobs where the stop_execution_date is NULL, but the run_requested_date is up to 3 years ago, so it looks like the job started 3 years ago and has not yet finished.

If I look at these jobs in Redgate SQL Backup or in SQLSoft SQLjobvis then it looks like the jobs are just starting and ending on their usual schedules.

I have 24 jobs in total like this, and it looks like the run_requested_date on them is in batches of around the same date/time per batch, so at first I though it might be something to do with the server being restarted and leaving jobs hanging, but the more recent ones were only 2 months ago and the server hasn't been restarted in over a year (it's a live/production server that is used 24/7).

So my questions are:
(1) What might have caused this?
(2) Is it safe to just do an UPDATE on these records and change the stop_execution_date to something like '1900-01-01'
(3) Are there any better ways to tidy this up?


EDIT: Source code to quickly see what I mean:
SELECT   job.Name, 
job.job_ID,
job.Originating_Server,
activity.run_requested_Date,
DATEDIFF(mi, activity.run_requested_Date, GETDATE()) as Elapsed
FROM msdb.dbo.sysjobs_view job
JOIN msdb.dbo.sysjobactivity activity
ON job.job_id = activity.job_id
WHERE activity.run_requested_date IS NOT NULL
AND activity.stop_execution_date IS NULL
--AND run_Requested_date >= '2013-01-01'
ORDER BY 4


dineshvishe
dineshvishe
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 342
reatart Sql Aget and see.
Any problem,Let me know.
durai nagarajan
durai nagarajan
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1607 Visits: 2775
There is a problem in my servers as well that starts from 2008 data.

I dont think any these jobs are still running which i can get from "Job Activity monitor".

If you are trying to get job status try that data using SysJobHistory table with run_Status.

You query have would be useful if the data would have available properly.

Regards
Durai Nagarajan
durai nagarajan
durai nagarajan
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1607 Visits: 2775
dineshvishe (5/20/2013)
reatart Sql Aget and see.
Any problem,Let me know.


please dont suggest to restart servers as it will affect production instead have to identify the root cause for it.

From 2008 i have certain job like this status , so many time they have patched windows and sql and restarted the server.

i think sql is not recording current job activity details properly.

http://msdn.microsoft.com/en-IN/library/ms190484(v=sql.90).aspx

Regards
Durai Nagarajan
RossRoss
RossRoss
Old Hand
Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)

Group: General Forum Members
Points: 333 Visits: 763
Thanks for the replies guys, no luck as yet though.


dineshvishe (5/20/2013)
reatart Sql Aget and see.
Any problem,Let me know.


Restarting didn't work, the server never usually gets restarted as it is a production server with several website that are reliant on it, I recently had to move DBs onto a SAN though, which involved the only server restart in well over a year, the old jobs are still there though.



durai nagarajan (5/21/2013)
If you are trying to get job status try that data using SysJobHistory table with run_Status.

I'm not just trying to get the status, I'm looking for jobs that are currently running, which the SysJobHistory table doesn't show, this is why I'm looking at sysJobActivity.
shiobhand.gaitor
shiobhand.gaitor
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 8
I tried your codes and realize that I have the same problem. I see exactly what you mean.
durai nagarajan
durai nagarajan
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1607 Visits: 2775
durai nagarajan (5/21/2013)
If you are trying to get job status try that data using SysJobHistory table with run_Status.

I'm not just trying to get the status, I'm looking for jobs that are currently running, which the SysJobHistory table doesn't show, this is why I'm looking at sysJobActivity.


Any problem in using Job Activity Monitor provided in sqlagent?.

Regards
Durai Nagarajan
homebrew01
homebrew01
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4819 Visits: 9108
I also have old "orphaned" active jobs. I ran across it a few weeks ago when creating a report to alert me about long running jobs and failed job steps. I just filter them out of my query and ignore them.



durai nagarajan
durai nagarajan
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1607 Visits: 2775
homebrew01 (5/21/2013)
I also have old "orphaned" active jobs. I ran across it a few weeks ago when creating a report to alert me about long running jobs and failed job steps. I just filter them out of my query and ignore them.


Are you sure it is "orphaned" active jobs, because i have a job showing in this query which is on 2008 but executing it fine now.

How a orphaned job can run now wihtout any issues. i hope these are not properly maintained session.

Regards
Durai Nagarajan
homebrew01
homebrew01
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4819 Visits: 9108
Maybe "orphaned" was not the best word.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search