Find unused jobs in SQL Server

Hi folks,

Today I want to share a useful script which will help us to clutter list of jobs that either have not run in X months or where the next schedule run date is older than today.

Just run this against any server. Investigate if the job can be deleted.

SELECT    @@SERVERNAME SvrName, J.Name, J.[Enabled], JA.LastRun, JA.NextRun, JV.[description], JC.Name JobCategory
FROM    msdb.dbo.sysjobs J
        JOIN msdb.dbo.sysjobs_view JV
            ON J.Job_ID = JV.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
            ) JA
            ON J.Job_ID = JA.Job_ID
        JOIN msdb.dbo.syscategories JC
            ON J.Category_ID = JC.category_id
WHERE    DATEDIFF(m, ISNULL(LastRun, ‘1900-01-01’), GETDATE()) > 12
        OR NextRun < GETDATE()

 

I would like to have feedback from my blog readers.

Please post your feedback, question, or comments about this article.

Advertisements

3 thoughts on “Find unused jobs in SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s