Sometimes
the DTS packages will run unusually taking long time which leads to slow down
the performance of the server.
The
reason might be the bad SQL used in the package or the scheduled time for the
package to run. Use the below query to find the long running DTS packages and
its information.
We
can not get the long running job information which is currently executing using
system table msdb.dbo.sysjobhistory.
Below
query gives the information about the DTS jobs which has got executed recently
and took more than one hour duration to execute.
SELECT
j.name
,
h.run_status
,
durationHHMMSS = STUFF(STUFF(REPLACE(STR(h.run_duration,7,0), ' ','0'),4,0,':'),7,0,':'),
[start_date] = CONVERT(DATETIME, RTRIM(h.run_date) + ' ' +
STUFF(STUFF(REPLACE(STR(RTRIM(h.run_time),6,0), ' ','0'),3,0,':'),6,0,':'))
FROM
msdb.dbo.sysjobs
AS j
INNER JOIN
(
SELECT job_id,
instance_id = MAX(instance_id)
FROM msdb.dbo.sysjobhistory
GROUP BY job_id
)
AS l
ON
j.job_id = l.job_id
INNER JOIN msdb.dbo.sysjobhistory AS h
ON
h.job_id
= l.job_id
AND h.instance_id
= l.instance_id
WHERE
STUFF(STUFF(REPLACE(STR(h.run_duration,7,0), ' ','0'),4,0,':'),7,0,':') > '000:60:00'
AND
CONVERT(DATETIME, RTRIM(run_date))
> = CONVERT(DATETIME, RTRIM(GETDATE()-6))
ORDER BY CONVERT(INT, h.run_duration) DESC,
[start_date] DESC
After
I searched many sites, I have found that the usage of extended stored procedure
xp_sqlagent_enum_jobs will give the exact information about job status
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'ICON-EU\sevells'
By
executing this, you can find the currently executing job which shows the state
as “1”. To know the status, check the column ‘Running’ with the following
values
0 -
Returns only those jobs that are not idle or suspended.
1 - Executing.
2 - Waiting for thread.
3 - Between retries.
4 - Idle.
5 - Suspended.
7 - Performing completion actions.
1 - Executing.
2 - Waiting for thread.
3 - Between retries.
4 - Idle.
5 - Suspended.
7 - Performing completion actions.
Hope this helps to know more details about the long running DTS jobs.
