No SQL Agent Failover on 2 Node Cluster

  • Currently I have a 2 node active-passive cluster setup running SQL 2005 SP3. Everything works well when running on node 1; however, when the cluster fails over to node 2, the sql agent service will attempt to start and fail. "SQLServerAgent could not be started (reason: Error creating a new session)." is logged in the event viewer. The only thing logged in SQLAGENT.OUT for node 2 is listed below.

    2010-05-24 08:04:45 - ! [298] SQLServer Error: 208, Invalid object name 'sys.configurations'. [SQLSTATE 42S02] (DisableAgentXPs)

    2010-05-24 08:04:45 - ! [298] SQLServer Error: 208, Invalid object name 'msdb.dbo.syssessions'. [SQLSTATE 42S02]

    2010-05-24 08:04:45 - ! [000] Error creating a new session

    2010-05-24 08:04:45 - ! [298] SQLServer Error: 208, Invalid object name 'sys.configurations'. [SQLSTATE 42S02] (DisableAgentXPs)

    2010-05-24 08:04:46 - ? [098] SQLServerAgent terminated (normally)

    I've read alot of other posts about making sure that the service account has sysadmin permissions, but I'm not sure if that applies to this situation. Any help would be greatly appreciated.

  • Is this a new build? Has the failover worked before or did this suddenly start happening?

    Can you give a little bit of history here.

    Leo

    There are 10 types of people in the world.

    Those who understand binary and and those that don't

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

  • This is a brand new server and fresh install. To date the failover for the SQL Agent failover has yet to work.

  • I would check the build was done correctly, particularly that all the databases are on shared resources.

    Leo

    There are 10 types of people in the world.

    Those who understand binary and and those that don't

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

  • Not sure if this is the problem, but is the service account a member of the Administrators local group? Per BOL, it must be to use the auto-restart feature for the agent.

    BOL isn't giving the error you are receiving so this may be a red herring. However, here are some links from BOL to check the permissions on the service account.

    http://msdn.microsoft.com/en-us/library/ms186264.aspx

    http://msdn.microsoft.com/en-us/library/ms345380.aspx

    HTH

    Cindy

  • The auto-restart feature in Agent is to allow Agent to restart on unexpected failures. It is not needed to get Agent to restart after a Cluster failover. The Cluster service should deal with this.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

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

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