Configuration of Agent XP brings down server

  • Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.

    Next alert

    Only administrators may connect at this time.

    There is a chance some one messed with this but I cannot understand why.The person who was logged in at the time was running profiler and the logs confirm that the trace was stopped?

    What could trigger the change except user input.

    your input is as usual appreciated

    Mike

  • This was removed by the editor as SPAM

  • Not sure this is a sure answer, but I recently experienced a similar event while snmp services was being installed. There is little to explain it other than this. The machines (plural) that were 'clustered' together both terminated the DTC and SQL Server 2005 processes. Once the SQL service was restarted, both services (snmp) run together with out any issues. Unfortunately we only have these 2 machines configured in this manner

    We tried a repeat on a stand alone, and it did not cause the process to stop.

  • The agent XPs always change from 1 to 0 as the server starts shutting down. It's an effect, not a cause.

    What caused the shutdown is another matter. What's the last line of the error log?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Date12/11/2007 3:14:02 PM

    LogSQL Server (Archive #1 - 12/11/2007 3:15:00 PM)

    SourceServer

    Message

    The connection has been lost with Microsoft Distributed Transaction Coordinator (MS DTC). Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) will begin once the connection is re-established. This is an informational message only. No user action is required.

    Next-----

    Date12/11/2007 3:15:41 PM

    LogSQL Server (Archive #1 - 12/11/2007 3:15:00 PM)

    SourceLogon

    Message

    Login failed for user 'OVERN'. Only administrators may connect at this time. [CLIENT: 10.200.18.22]

    NExt-----

    Date12/11/2007 3:15:44 PM

    LogSQL Server (Archive #1 - 12/11/2007 3:15:00 PM)

    Sourcespid35s

    Message

    Service Broker manager has shut down.

  • We are having the same with the SQL Server 2005 Cluster. Does anyone have any idea why the server failover randomly with these errors?

    Thanks,

  • Check the cluster logs and the event logs to see if you can find a reason for the service to fail over.

    Agent XPs are always switched to 0 as a SQL server shuts down. It's not the cause of the failure.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Please,

    Try this.

    exec sp_configure 'agent',1

    reconfigure with override

    go

    The original post is: "http://learnsqlwithbru.com/SQLForums/index.php?topic=148.0"

  • This can be because of a security patch update on the server, for more information please refer:

    Agent XPs Server Configuration Option[/url]

    Thanks,

    Suresh Raavi

  • We also just experienced this issue. In our case, it was due to our infrastructure team installing SNMP on the server, which in certain environments will stop the SQL Server service and SQL Agent service -

  • One of the things I change and right after that I got this is the default SQL service I change the login account that is running as.

Viewing 11 posts - 1 through 10 (of 10 total)

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