Just Code‎ > ‎

TSQL - Find orphan jobs that does not have a target database on the server anymore

posted Apr 26, 2012, 4:39 AM by Peter Henell   [ updated Apr 26, 2012, 4:39 AM ]
If you have a development server where you frequently create alot of jobs for different development databases then it will surely come a day when there are jobs installed on the server that does not have it's database anymore.

Use this script to find such jobs.

    j.name, * FROM msdb..sysjobsteps js
    inner join msdb..sysjobs j on js.job_id = j.job_id
where not exists(select name from master..sysdatabases db where js.database_name = db.name )
order by js.database_name

-- copy paste the job_id to here to delete the job
exec msdb.dbo.sp_delete_job @job_id = ''