Jobs that have been deleted through LiteSpeed Enterprise Console or SQL Server Enterprise Manager or SSMS, attempt to run on schedule and display errors. How to delete these "phantom" jobs?
Job has not been completely deleted from one of several tables used by MSSQL or the job may remain in cache.
Use the script below to resolve the issue.
Note: You may have to rename the LiteSpeed job to something simple (on the Enterprise Console, right-click on the job and just enter a new name, then close/save), such as XXX to get the script to run. The SQLAgent must be running.
USE msdb
EXEC sp_delete_job @job_name='job_name'
It will return a "job doesn't exist" type error - but the alerts go away - for all "phantom" jobs.
The sp_delete_job moves the job to a temporary table, deletes the job from sysjobs, sysdownloadlist, sysjobservers, sysjobsteps, sysjobschedules, sysjobhistory tables, then deletes the temp table.
This issue may be due to the function of MSSQL Server, and that the backup may still be stored in SQL cache.
Run DBCC DROPCLEANBUFFERS. However, the subsequent executions of other plans, SP, etc. will have to run from a non-cached state, and may take a little longer when run for the first time, after having refreshed the cache. Performance may be similar to the box after a reboot or as though the SQL Services were stopped and restarted.
