locked
SQL Server 2005 Agent XPs being disabled by Live Update? RRS feed

  • Question

  • It looks like LiveUpdate is disabling the Agent XPs (SQL Agent). We have a few production servers and I noticed that after a LiveUpdate was run, the Agent XPs configuration option was turned off (set to 0), which of course prevented the SQL Agent service from running, which prevented *ALL* of our jobs from running!

    Things like full database backups and LOG backups just was not scheduled and was not run!

    Has anyone else seen this behavior? It looks like the security change in the LiveUpdate that we ran on April 13, 2009 contained some logic that turned off the Agent XPs. I have reconfigured the options in SQL Server, but we have several production servers in the field and it will be time consuming to check them all EVERY TIME  WE RUN A LIVE UPDATE.


    - Fred
    Friday, April 17, 2009 7:36 AM

All replies

  • Hi Fred,

    I got same issue like you about Agent XPs.

    Usually I check the status in SYS.CONFIGURATIONS.

    use MASTER;
    SLEECT * FROM SYS.CONFIGURATIONS WHERE NAME = 'Agent xps';

    if value = 0 then you must reconfigure it with this script:


    USE MASTER;

    SP_CONFIGURE 'SHOW ADVANCE OPTIONS', 1;
    GO
    RECONFIGURE
    GO
    SP_CONFIGURE 'Agent XPs', 1;
    GO
    RECONFIGURE
    GO

    SP_CONFIGURE 'SHOW ADVANCE OPTIONS', 0;
    GO
    RECONFIGURE WITH OVERRIDE
    GO




    Warm regards, Andre Wihardjo Jakarta - Indonesia
    • Proposed as answer by Andre Wihardjo Thursday, June 11, 2009 2:49 PM
    Thursday, June 11, 2009 2:47 PM
  • Its not recommended to use Live update in SQL Server .  



    Thanks Faiz Farazi MCDBA,MCSA
    • Edited by Faiz Farazi Thursday, June 18, 2009 5:12 PM
    Monday, June 15, 2009 7:28 PM