Listare sql jobs nefolosite mai mult de x luni

Situatie

Mai jos este o procedura ce primeste un parametru @x de tip integer si listeaza joburile de pe un server Microsoft SQL Server care nu au rulat o perioada mai mare de @x luni.

Solutie

— exec FindUnusedJobsMoreThanXMonths 12

create procedure FindUnusedJobsMoreThanXMonths
@x int

SELECT
sj.Name,
sj.[Enabled],
sja.LastRun,
sjv.[description]
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobs_view sjv
ON sj.Job_ID = sjv.Job_ID
JOIN
(
SELECT Job_ID, MAX(Last_Executed_Step_Date) LastRun, MAX(Next_Scheduled_Run_Date) NextRun
FROM msdb.dbo.sysjobactivity
GROUP BY Job_Id
) sja
ON sj.Job_ID = sja.Job_ID
JOIN msdb.dbo.syscategories sjc
ON sj.Category_ID = sjc.category_id
WHERE DATEDIFF(m, ISNULL(LastRun, ‘1900-01-01’), GETDATE()) > @x

Tip solutie

Permanent

Follow Us