Finding Potentially redundant Jobs

Finding potentially redundant jobs

Often if there is a large lists of jobs on a server it can be hard to go through and find the jobs that are redundant or havent been run in a long time.

This script is a handy way of finding these jobs.

In this example we are finding jobs older than 2010. You can easily change this by changing the date at the bottom of the script

DECLARE @is_sysadmin INT
DECLARE @job_owner   sysname
SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)
SELECT @job_owner = SUSER_SNAME()

CREATE TABLE #xp_results (job_id                UNIQUEIDENTIFIER NOT NULL,
            last_run_date         INT              NOT NULL,
            last_run_time         INT              NOT NULL,
            next_run_date         INT              NOT NULL,
            next_run_time         INT              NOT NULL,
            next_run_schedule_id  INT              NOT NULL,
            requested_to_run      INT              NOT NULL, -- BOOL
            request_source        INT              NOT NULL,
            request_source_id     sysname          COLLATE database_default NULL,
            running               INT              NOT NULL, -- BOOL
            current_step          INT              NOT NULL,
            current_retry_attempt INT              NOT NULL,
            job_state             INT              NOT NULL)


INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner


SELECT j.NAME, t.* 
FROM #xp_results t
LEFT OUTER JOIN msdb.dbo.sysjobs j ON t.job_id = j.job_id
WHERE last_run_date < 20081231
AND j.enabled = 0

DROP TABLE #xp_results