June 19, 2015 at 2:13 am
Morning all,
I have a job which is extracting millions of records based on two views that pull active clients history to date and another that pulls closed clients within a period of two years.
My problem is that the job which used to take 16 hours has been running for over 65 hours!
I am concerned to say the least. I can see the file the job writes to updating slowly but I would not have expected it to still be running 65 hours later.
Can anyone recommend anything to check if there is a bottleneck or perhaps some analysis of the job while its running?
I've used this query to check its status and runtime but I really need more info:
SELECT
job.name,
job.job_id,
job.originating_server,
activity.run_requested_date,
DATEDIFF( SECOND, 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
JOIN
msdb.dbo.syssessions sess
ON
sess.session_id = activity.session_id
JOIN
(
SELECT
MAX( agent_start_date ) AS max_agent_start_date
FROM
msdb.dbo.syssessions
) sess_max
ON
sess.agent_start_date = sess_max.max_agent_start_date
WHERE
run_requested_date IS NOT NULL AND stop_execution_date IS NULL
Any help would be gratefully received.
Cheers,
Neil
June 19, 2015 at 2:57 am
Probably the query that reads the data is using a different execution plan than usual. Try detecting it using sp_whoisactive.
-- Gianluca Sartori
June 19, 2015 at 3:03 am
I will give it a go. Thanks Gianluca.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply