May 24, 2010 at 10:01 am
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.
May 24, 2010 at 2:22 pm
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.
May 25, 2010 at 6:43 am
This is a brand new server and fresh install. To date the failover for the SQL Agent failover has yet to work.
May 25, 2010 at 1:47 pm
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.
May 26, 2010 at 7:03 am
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
June 1, 2010 at 2:57 am
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