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 Wednesday, May 22, 2013 3:09 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 4, 2014 4:04 AM
Points: 280, Visits: 530
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.
Post #1455372
Posted Wednesday, May 22, 2013 3:14 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:15 AM
Points: 1,056, Visits: 2,687
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
Post #1455375
Posted Wednesday, May 22, 2013 3:32 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 4, 2014 4:04 AM
Points: 280, Visits: 530
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?
Post #1455385
Posted Wednesday, May 22, 2013 9:48 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:15 AM
Points: 1,056, Visits: 2,687

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
Post #1455794
Posted Thursday, May 23, 2013 2:49 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 12:33 AM
Points: 938, Visits: 2,927
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.




The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1455891
Posted Thursday, May 23, 2013 3:02 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 4, 2014 4:04 AM
Points: 280, Visits: 530
durai nagarajan (5/22/2013)

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.


sysJobHistory does not show anything that is currently running, data is only stored here after the job step completes, which is why I'm looking at sysJobActivity:

http://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&l=EN-US&k=k(SYSJOBHISTORY_TSQL);k(SQL11.SWB.TSQLRESULTS.F1);k(SQL11.SWB.TSQLQUERY.F1);k(MISCELLANEOUSFILESPROJECT);k(DevLang-TSQL)&rd=true
Post #1455896
Posted Thursday, May 23, 2013 3:34 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 4, 2014 4:04 AM
Points: 280, Visits: 530
Sean Pearce (5/23/2013)
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.


Hi Sean, not sure I know what you mean?
Post #1455912
Posted Thursday, May 23, 2013 3:43 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 12:33 AM
Points: 938, Visits: 2,927
Ross.M (5/23/2013)
Sean Pearce (5/23/2013)
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.


Hi Sean, not sure I know what you mean?

Right click your job in SSMS
Script Job as -->
DROP And CREATE To -->
New Query Editor Window

Execute the script.




The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1455915
Posted Thursday, May 23, 2013 3:51 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 4, 2014 4:04 AM
Points: 280, Visits: 530
Sean Pearce (5/23/2013)
Right click your job in SSMS
Script Job as -->
DROP And CREATE To -->
New Query Editor Window

Execute the script.


Wouldn't this remove all of the job's history?
Post #1455918
Posted Thursday, May 23, 2013 5:28 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 12:33 AM
Points: 938, Visits: 2,927
Ross.M (5/23/2013)
Sean Pearce (5/23/2013)
Right click your job in SSMS
Script Job as -->
DROP And CREATE To -->
New Query Editor Window

Execute the script.


Wouldn't this remove all of the job's history?

Yes, it would by default. You can specify @delete_history = 0 when deleting the job but the newly created job would have a different job_id. In an earlier post you specified
I'm looking for jobs that are currently running
which doesn't need any history.




The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1455951
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse