Post reply

Non existent job posting failure in Event Log and sending notifications via Operators

  • November 1, 2007 at 11:56 am

    #70442

    SQL Server 2000 SP3a - Standard Edition - Windows Server 2003

    I have a non-existent job that is posting a failure in the Windows Event Log and sending notifications via the enabled operators that I have in the SQL Agent.

    Error Message from the Event Log:

    "SQL Server Scheduled Job 'db_job_full_backup' (0x41CF3E643C108F4CA0218A2B5A6D16FF) - Status: Failed - Invoked on: 2007-11-01 01:00:00 - Message: The job failed. Unable to retrieve steps for job db_job_full_backup.

    I have a job by the same name that is running successfully, but that runs at 00:01. If you look at the job in the SQL Agent jobs view, it says that it ran successfully, and the history shows no failure on any steps.

    To give a little history, I recently changed the job via SQL Script. The way that I changed it was to delete the job in it's entirety and then recreate the job. I used sp_delete_job to delete the job that had been running at 01:00 and created a new job to run at 00:00. (This, of course was not the only change or I would have just adjusted the schedule.)

    I have looked in the sysjobs, sysjobschedules, sysjobservers, sysjobhistory, and sysjobsteps tables. I can only see the current job. I see no reference to the old job.

    The failure does not show up in the SQL Server Logs.

    Any ideas where I may be failing to look?

  • November 2, 2007 at 10:01 am

    #747840

    1 .Did you checked if this job was managed from another server?

    check out the article in BOL "Creating a Multiserver Environment"

    2. Could it be somehow related that one of the servers already changed time?

    3. Could it be related that you are in Europe and the machine where the job is logged is in US? the time changed there last weekend

    Regards,Yelena Varsha

  • November 2, 2007 at 10:17 am

    #747846

    Have you checked sysjobschedules to be sure the old job still isn't in there?

    Also, moved to the SS2K forums. Please watch the titles when you're posting.

  • November 13, 2007 at 12:10 pm

    #750800

    I have the same problem and the answer has nothing to do with time changes.

    If you right click on the SQLagent and then Properties and General you will see where the agent logs errors. If you look at one of the recent errors I bet you see error #208 as well as the job_id that the agent failed to find. Select * from msdb..sysjobs will not list this job_id so, why is the agent trying to run it????

    If you execute the undocumented MS SPROC master..xp_sqlagent_enum_jobs 0,garbage where 0 means info on all jobs and "garbage" is just some required filler you will see a composite list of all the jobs living on your server. Do you see the job_id the agent is looking for?

    What I haven't been able to figure out is where the xp is getting these job_ids from. This xp runs a DLL so profiler isn't of much help.

  • November 13, 2007 at 12:28 pm

    #750804

    The phantom jobs that are getting pulled are the result of stale cache. If you stop/start the SQL Server Agent and then execute master..xp_sqlagent_enum_jobs 1,garbage I think you will see your problem go away - it fixed things for me.

  • November 13, 2007 at 3:14 pm

    #750861

    Thanks for that info. I stopped and restarted the Agent a while back and that resolved my problem as well. Sorry, I didn't respond to this earlier.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply