Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

sysJobActivity showing jobs from years ago that never ended Expand / Collapse
Author
Message
Posted Tuesday, May 14, 2013 3:30 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 11, 2014 7:46 AM
Points: 270, Visits: 487
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

Post #1452450
Posted Monday, May 20, 2013 3:15 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 10:53 PM
Points: 62, Visits: 320
reatart Sql Aget and see.
Any problem,Let me know.
Post #1454451
Posted Monday, May 20, 2013 10:53 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 5:48 AM
Points: 1,048, Visits: 2,646
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
Post #1454811
Posted Monday, May 20, 2013 11:09 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 5:48 AM
Points: 1,048, Visits: 2,646
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
Post #1454815
Posted Tuesday, May 21, 2013 6:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 11, 2014 7:46 AM
Points: 270, Visits: 487
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.
Post #1454956
Posted Tuesday, May 21, 2013 11:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 23, 2013 2:00 PM
Points: 1, Visits: 8
I tried your codes and realize that I have the same problem. I see exactly what you mean.
Post #1455152
Posted Tuesday, May 21, 2013 9:14 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 5:48 AM
Points: 1,048, Visits: 2,646
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
Post #1455286
Posted Tuesday, May 21, 2013 9:38 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, April 12, 2014 11:40 AM
Points: 2,795, Visits: 8,297
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.


Post #1455290
Posted Tuesday, May 21, 2013 11:15 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 5:48 AM
Points: 1,048, Visits: 2,646
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
Post #1455308
Posted Tuesday, May 21, 2013 11:17 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, April 12, 2014 11:40 AM
Points: 2,795, Visits: 8,297
Maybe "orphaned" was not the best word.


Post #1455309
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse