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

Post date: Apr 26, 2012 11:39:35 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.

SELECT 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 jobexec msdb.dbo.sp_delete_job @job_id = ''