sysJobActivity showing jobs from years ago that never ended
-
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
-
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?
-
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.
-
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?
-
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.
-
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
-
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)
-
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.0005709182012-09-23 00:00:00.0001
2013-03-20 01:15:00.0003145232013-03-20 01:15:00.0001
2012-12-15 00:00:00.0004513982012-12-15 00:00:01.0001
2012-09-15 00:15:00.0005824232012-09-15 00:15:00.0001
2012-09-23 00:15:00.000570903NULL1
2013-03-20 01:00:00.0003145382013-03-20 01:00:00.0001
2013-10-22 00:00:00.0003558 2013-10-22 00:00:00.0001
Can/how/should I delete these records?
-
I never solved it. I just omit results older than 'X' days.
-
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
-
@TheJrDBA
Thanks. This helped with the issue I was seeing too.
Eric Humphrey
http://www.erichumphrey.com/ -
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.sysjobactivitySETlast_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
WHEREsession_id = 2006--SessionID of the problematic instances of job
ANDjob_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.
-
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.sysjobactivitySETlast_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
WHEREsession_id = 2006--SessionID of the problematic instances of job
ANDjob_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.
-
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
-
I know this is an old thread, but I ran into this issue myself. I have job A that will check and stop job B if it is running when job A starts with this TSQL in step 1:
if exists (select 1 from [msdb].[dbo].[sysjobactivity] a
join [msdb].[dbo].[sysjobs] j on a.[job_id] = j.[job_id]
where j.[name] = 'my job name'
and a.[start_execution_date] is not null
and a.[stop_execution_date] is null
)
EXEC [msdb].[dbo].[sp_stop_job] N'my job name';
I will put in the join to the sessions table, but I was able to "clean-up" the activity history by dropping and recreating the job itself, of course, beware that this will purge the entire job history.
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply