Query to fetch long running DTS Jobs

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.

Hope this helps to know more details about the long running DTS jobs.