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
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25306 Visits: 12488
Would it not make sense to see what the start_execution_date is for these jobs, instead of (or as well as) the execution_requested_date? Also, to see what the run_requested_source is for each of these jobs?

Tom

RossRoss
RossRoss
SSC-Addicted
SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)

Group: General Forum Members
Points: 467 Visits: 763
L' Eomot Inversé (5/23/2013)
Would it not make sense to see what the start_execution_date is for these jobs, instead of (or as well as) the execution_requested_date? Also, to see what the run_requested_source is for each of these jobs?


Hi Tom, the start_execution_date is the same as the run_requested_date for these jobs, the run_requested_source is 1 (source scheduler)
Michael-401546
Michael-401546
SSC-Enthusiastic
SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)

Group: General Forum Members
Points: 123 Visits: 348
OK has anyone solved this issue?

Server and SQL Services were restarted 4 days ago.

Here is the query I am running:
select job.Name, job.job_ID, job.Originating_Server,
activity.run_requested_Date, datediff(minute, activity.run_requested_Date, getdate()) as Elapsed, start_execution_date, run_requested_source
from msdb.dbo.sysjobs_view job
inner join msdb.dbo.sysjobactivity activity
on (job.job_id = activity.job_id)
where run_Requested_date is not null and stop_execution_date is null

Results shows :
run_requested_date Elapsed start_execution_date Run_requested_source
2012-09-23 00:00:00.000 570918 2012-09-23 00:00:00.000 1
2013-03-20 01:15:00.000 314523 2013-03-20 01:15:00.000 1
2012-12-15 00:00:00.000 451398 2012-12-15 00:00:01.000 1
2012-09-15 00:15:00.000 582423 2012-09-15 00:15:00.000 1
2012-09-23 00:15:00.000 570903 NULL 1
2013-03-20 01:00:00.000 314538 2013-03-20 01:00:00.000 1
2013-10-22 00:00:00.000 3558 2013-10-22 00:00:00.000 1


Can/how/should I delete these records?
homebrew01
homebrew01
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11786 Visits: 9222
I never solved it. I just omit results older than 'X' days.



SQLDuck
SQLDuck
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 345
I was having this same problem, and it turns out the key is the the session_id column in msdb..sysjobactivity. To answer the OP's questions:

(1) In my case, these "orphaned" records in sysjobactivity were caused by server restarts that occurred before the jobs had a chance to finish. You may be able to prevent this by stopping the SQL Agent service manually before restarting, but I haven't tested that.
(2) I'm not sure whether it's safe to update this table manually, so I wouldn't recommend it.
(3) Rather than "cleaning up" these records, I found that joining sysjobactivity with syssessions solved my issue. Each time SQL Agent starts, it writes a record to msdb.dbo.syssessions, so we're only interested in the sysjobactivity rows where session_id equals the max(session_id) from syssessions. This essentially limits your result set to the job activity since the last server restart.

For example, I have a Change Data Capture job that needs to run all the time, so I have an alert that emails me if the following query returns 0:


DECLARE @currentSession AS INT
SELECT @currentSession = MAX(session_id)
FROM msdb.dbo.syssessions

SELECT count(*)
FROM msdb.dbo.sysjobactivity ja
JOIN msdb.dbo.sysjobs j
ON j.job_id = ja.job_id
WHERE j.name = 'cdc.Audit_capture'
AND session_id = @currentSession
AND ja.run_requested_date IS NOT NULL
AND ja.stop_execution_date IS NULL


Eric Humphrey
Eric Humphrey
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 108
@TheJrDBA

Thanks. This helped with the issue I was seeing too.

Eric Humphrey
http://www.erichumphrey.com/
RossRoss
RossRoss
SSC-Addicted
SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)

Group: General Forum Members
Points: 467 Visits: 763
Thanks TheJrDBA, that's a very useful solution.

I tried something else which seems to work on my dev server to actually remove the jobs from any reports that show long runners, time will tell how it plays out though:
UPDATE msdb.dbo.sysjobactivity 
SET last_executed_step_id = 1,
last_executed_step_date = '2014-02-18', --Today or any date
stop_execution_date = '2014-02-18' --Today or any date
WHERE session_id = 2006 --SessionID of the problematic instances of job
AND job_id = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx' --jobID of the problematic job



I've only tried this on the dev server, it does have jobs running frequently though, and I monitor them with Redgate SQL Backup, SQLJobVis, and my own queries, for all intensive purposes, the jobs that were showing as never finishing are showing as complete now. That said, I haven't updated the problem records with a job_history_id or a next_sheduled_run_date but I don't think it needs these to just show the jobs as complete for the sake of reporting.

I'll keep an eye on how things play out before trying anything like this on a production server, and I'll post back here as/when I come to any conclusions about it.
S. Kusen
S. Kusen
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2867 Visits: 1119
Ross.M (2/18/2014)
Thanks TheJrDBA, that's a very useful solution.

I tried something else which seems to work on my dev server to actually remove the jobs from any reports that show long runners, time will tell how it plays out though:
UPDATE msdb.dbo.sysjobactivity 
SET last_executed_step_id = 1,
last_executed_step_date = '2014-02-18', --Today or any date
stop_execution_date = '2014-02-18' --Today or any date
WHERE session_id = 2006 --SessionID of the problematic instances of job
AND job_id = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx' --jobID of the problematic job



I've only tried this on the dev server, it does have jobs running frequently though, and I monitor them with Redgate SQL Backup, SQLJobVis, and my own queries, for all intensive purposes, the jobs that were showing as never finishing are showing as complete now. That said, I haven't updated the problem records with a job_history_id or a next_sheduled_run_date but I don't think it needs these to just show the jobs as complete for the sake of reporting.

I'll keep an eye on how things play out before trying anything like this on a production server, and I'll post back here as/when I come to any conclusions about it.


To add to this, if you use sp_help_jobactivity to pull back the job activity, that also takes the session_id as a parameter:

DECLARE @v_session_id int /*sql agent session id.. some data gets left behind if a job is running when the SQL Agent is stopped mid-run*/
select @v_session_id = max(session_id) from msdb.dbo.syssessions

-- Get list of job activity
EXEC msdb.dbo.sp_help_jobactivity @session_id = @v_session_id





Thanks to those who put this out here- had me scratching my head until I found this thread.
lindsayscott23
lindsayscott23
Mr or Mrs. 500
Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)Mr or Mrs. 500 (548 reputation)

Group: General Forum Members
Points: 548 Visits: 492
Hi all,

I've been experiencing this problem as well recently. It was driving me mad until I found this thread!

Thanks SQLDuck for the solution that worked for me

Lins
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