sysJobActivity showing jobs from years ago that never ended
-
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:
SELECTjob.Name,job.job_ID,
job.Originating_Server,
activity.run_requested_Date,
DATEDIFF(mi, activity.run_requested_Date, GETDATE()) as Elapsed
FROMmsdb.dbo.sysjobs_view job
JOINmsdb.dbo.sysjobactivity activity
ONjob.job_id = activity.job_id
WHEREactivity.run_requested_date IS NOT NULL
ANDactivity.stop_execution_date IS NULL
--AND run_Requested_date >= '2013-01-01'
ORDER BY 4
-
reatart Sql Aget and see.
Any problem,Let me know.
-
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 -
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 -
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.
-
I tried your codes and realize that I have the same problem. I see exactly what you mean.
-
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 -
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.
-
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 -
Maybe "orphaned" was not the best word.
-
durai nagarajan (5/21/2013)
Any problem in using Job Activity Monitor provided in sqlagent?.
Nope, Job Activity Monitor works fine, and it doesn't show these old jobs, but I would like to build my own queries around the job tables to make monitoring more flexible.
homebrew01 (5/21/2013)
I just filter them out of my query and ignore them.Yeah I've resorted to this too, our longest job is a few hours, so I've been filtering out anything older than 1 day by using:
start_execution_date >= DATEADD(dd, -1, GETDATE())I would still like to understand this though, and clean it up if possible without breaking things. I don't see that it would break anything, but being a production server I'm hesitant to do things I'm not 100% sure about.
-
it doesn't show these old jobs
what do you mean by this?
These jobs are not there now or you want job history details or others?
Regards
Durai Nagarajan -
durai nagarajan (5/22/2013)
it doesn't show these old jobs
what do you mean by this?
These jobs are not there now or you want job history details or others?
I mean it doesn't show the instances of the old jobs from several years ago, which is fine because I'm not too bothered about Job Activity Monitor anyway.
For example a job which runs daily and is in sysjobactivity as starting in 2010 but never finished will just show as normal for it's day to day routine in Job Activity Monitor. If it ran this morning and succeeded, then Job Activity Monitor say's it succeeded and the last run date was this morning. There is no mention of the 2010 instance of it in Job Activity Monitor.
The real questions I'm getting at though, are why have these old jobs never been marked with an end date, and can I delete these old entries from sysJobActivity, or UPDATE them with an end date of a long long time ago, so that they will never play into any queries. Can anyone think of anything that might break by doing this?
-
For example a job which runs daily and is in sysjobactivity as starting in 2010 but never finished will just show as normal for it's day to day routine in Job Activity Monitor. If it ran this morning and succeeded, then Job Activity Monitor say's it succeeded and the last run date was this morning. There is no mention of the 2010 instance of it in Job Activity Monitor.
You can find the job run status , job start date , job start time and Duration with much more information here in sysjobhistory.
Job Activity Monitor takes and shows the information from here. have you tried right click and check "View History" which will give more information.
History table will have few records operated by settings set in agent properties , history.
hope this will be useful.
Regards
Durai Nagarajan -
Ross.M (5/14/2013)
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?
You can tidy this up by scripting the job as drop and create.
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply