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 = ''