What do msdb.dbo.sp_sqlagent_refresh_job and msdb.dbo.sp_sqlagent_notify do ? RRS feed

  • Question

  • Hi,

    What do these procedures msdb.dbo.sp_sqlagent_refresh_job does and msdb.dbo.sp_sqlagent_notify do ?

    I need to refresh the job cache ? since I get the following error :

    "SQLServerAgent Error: Job 0x77026AB29723C74C9048859DFA702E1F does not exist in the job cache"

    When I run

    IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'MyJob')

    EXEC msdb.dbo.sp_delete_job @job_name=N'MyJob', @delete_unused_schedule=1

    Regards, Asi Pesa

    Thursday, September 17, 2015 3:23 PM


All replies

  • Hi Asi,

    Based on your description, you get error "SQLServerAgent Error: Job 0x77026AB29723C7 4C904885 9D F A702E1F does not exist in the job cache" when you run the query.

    This issue can be related to SQL Agent account permission or SQL Server Agent service. Please check if the SQL Agent account has rights to the database server, if not, you can grant sysadmin permission to the SQL Agent account. After that, if this issue still exist, please restart the SQL Server Agent service.

    Additionally, the procedure msdb.dbo.sp_sqlagent_refresh_job and msdb.dbo.sp_sqlagent_notify  are undocumented.  But from my test, we can query job’s information by running the procedure msdb.dbo.sp_sqlagent_refresh_job in SQL Server management studio. When you manually update the schedule table, you can run the procedure below to update the job cache.

    EXEC msdb.dbo.sp_sqlagent_notify @op_type = N’J’, @job_id = @JOB, @action_type = N’U’

    Ice Fan 

    Ice Fan
    TechNet Community Support

    • Edited by Ice Fan Monday, September 21, 2015 4:08 AM
    • Proposed as answer by Ice Fan Wednesday, September 30, 2015 4:26 AM
    Monday, September 21, 2015 4:07 AM
  • Hi Ice,

    I'm sysAdmin, I do not have permissions issue.

    I can't use sp_sqlagent_notify since I get error on the job_id which does not exits.

    Restarting sqlAgent solves the problem but it is not a solution for me.

    right now I run sql_sqlagent_refresh_job before the IF EXISTS that solves the problem.

    But I couldn't understand why?

    Regards, Asi Pesa

    • Proposed as answer by Ice Fan Wednesday, September 30, 2015 4:26 AM
    Monday, September 21, 2015 4:52 AM
  • Hi Asi,

    Check the below link

    Please Dont forget to mark as answer and Helpful Post. It helps others to find relevant posts to the same question. Milan Das

    Monday, September 21, 2015 6:41 AM