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 Thursday, May 23, 2013 10:51 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 5:07 AM
Points: 8,745, Visits: 9,293
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
Post #1456103
Posted Thursday, May 23, 2013 11:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:05 AM
Points: 280, Visits: 529
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)
Post #1456113
Posted Thursday, October 24, 2013 9:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 27, 2014 9:39 AM
Points: 12, Visits: 295
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?
Post #1508119
Posted Thursday, October 24, 2013 11:50 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 22, 2014 12:24 PM
Points: 2,829, Visits: 8,477
I never solved it. I just omit results older than 'X' days.


Post #1508200
Posted Thursday, January 23, 2014 11:39 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:50 PM
Points: 23, Visits: 282
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

Post #1534210
Posted Friday, January 31, 2014 10:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 14, 2014 2:04 PM
Points: 5, Visits: 105
@TheJrDBA

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


Eric Humphrey
http://www.erichumphrey.com/
Post #1536887
Posted Tuesday, February 18, 2014 4:34 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:05 AM
Points: 280, Visits: 529
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.
Post #1542476
Posted Friday, June 6, 2014 2:16 PM
SSC Eights!

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

Group: General Forum Members
Last Login: Today @ 9:03 AM
Points: 968, Visits: 965
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.
Post #1578503
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse