CODEWITHSUNDEEP
sqlsql-server
aye26
aye26

Reputation: 179

How to get top 5 history rows of each sql job?

I am trying to get top 5 (latest) of each sql job. I tried to query it like this 

SELECT Pr1.job_ID,MSDB.dbo.Agent_datetime(Pr1.run_date, Pr1.run_time) LastRun, COUNT(*) num
FROM MSDB.dbo.sysjobhistory Pr1 
JOIN MSDB.dbo.sysjobhistory Pr2
    ON MSDB.dbo.Agent_datetime(Pr1.run_date, Pr1.run_time) = MSDB.dbo.Agent_datetime(Pr2.run_date, Pr2.run_time)
     AND Pr1.job_ID = Pr2.job_ID
GROUP BY Pr1.job_ID, MSDB.dbo.Agent_datetime(Pr1.run_date, Pr1.run_time)
HAVING COUNT (*) <= 5
ORDER BY job_ID, MSDB.dbo.Agent_datetime(Pr1.run_date, Pr1.run_time)

But this query runs forever. I am using sql-server.

i also tried with a recommendation from below.

WITH CTE AS
(
   SELECT jo.name,Pr1.job_ID, Pr1.run_date, Pr1.run_time,pr1.run_status,
          Count(*) Over (Partition By Pr1.job_ID,Pr1.run_date,Pr1.run_time) As num,
          Row_Number() Over (Partition By Pr1.job_ID,Pr1.run_date,Pr1.run_time
                             Order By  pr1.run_date desc--, pr1.run_time desc
                             ) As Rn
   FROM   MSDB.dbo.sysjobhistory Pr1 
   join MSDB.dbo.sysjobs jo on jo.job_id=pr1.job_id
   JOIN MSDB.dbo.sysjobhistory Pr2 
         ON Pr1.job_ID = Pr2.job_ID --and pr1.run_status=pr2.run_status
         and pr1.run_date >=pr2.run_date and pr1.run_time >=pr2.run_time 
)
SELECT name,job_ID, run_date, run_time,run_status
FROM CTE
WHERE num <= 3 AND Rn = 1

Upvotes: 1

Views: 128

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460228

It's running forever because you use the scalar valued function Agent_datetime. If you use a query that doesn't need those functions the query optimizer can do it's job and use indexes.

You can simplify your task with window functions and a common-table-expression(CTE). Following groups by the column job_ID and returns the latest record of each group according to the run_date and run_time columns:

WITH CTE AS
(
   SELECT Pr1.job_ID, Pr1.run_date, Pr1.run_time,
          Count(*) Over (Partition By Pr1.job_ID) As num,
          Row_Number() Over (Partition By Pr1.job_ID 
                             Order By Pr1.run_date DESC, Pr1.run_time DESC) As Rn
   FROM   MSDB.dbo.sysjobhistory
)
SELECT job_ID, run_date, run_time
FROM CTE
WHERE num <= 3 AND Rn = 1

Note that i've removed your pointless join condition and the self-join:

ON MSDB.dbo.Agent_datetime(Pr1.run_date, Pr1.run_time) 
 = MSDB.dbo.Agent_datetime(Pr1.run_date, Pr1.run_time)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270513

Use row_number():

select pr.*
from (select pr.*,
             row_number() over (partition by pr.job_id
                                order by pr.run_date desc, pr.run_time desc
                               ) as seqnum
      from MSDB.dbo.sysjobhistory pr
     ) pr
where seqnum <= 5;

Upvotes: 2

Related Questions