About Me

My photo
I am MCSE in Data Management and Analytics with specialization in MS SQL Server and MCP in Azure. I have over 13+ years of experience in IT industry with expertise in data management, Azure Cloud, Data-Canter Migration, Infrastructure Architecture planning and Virtualization and automation. Contact me if you are looking for any sort of guidance in getting your Infrastructure provisioning automated through Terraform. I sometime write for a place to store my own experiences for future search and read by own blog but can hopefully help others along the way. Thanks.

You cannot start the SQL Server Agent service of a failover cluster of SQL Server 2005 if the build of SQL Server is 3179 or a later build

You cannot start the SQL Server Agent service of a failover cluster of SQL Server 2005 if the build of SQL Server is 3179 or a later build

-------------------------------------------------------------------------------------------------------------------------
Issue:-

SQL Server Agent in SQL Server 2005 does not start with below error in Event Viewer. 

The description for Event ID ( 17052 ) in Source ( MSSQL$XXXD1 ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: SQLServerAgent Monitor failed to restart SQLServerAgent after SQLServerAgent terminated unexpectedly (reason: SQLSCMControl() returned error 5040, 'The cluster node already exists

Finding :

SQL Server Agent will  not come online on both the cluster nodes.
Even when you try to  bring it up by console using the command:

C:\>"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\SQLAGENT90.EXE" -i XXXD1 -c -v

Microsoft (R) SQLServerAgent 9.00.3215.00
Copyright (C) Microsoft Corporation, 1995 - 1999.

2013-08-10 06:36:11 - ! [246] Startup error: Unable to read SQLServerAgent registry settings (from Software\Micros
oft\Microsoft SQL Server\MSSQL.1\SQLServerAgent)
2013-08-10 06:36:11 - ? [098] SQLServerAgent terminated (normally)

Cluster log getting this error:-
 [sqagtres] CheckServiceAlive: Service is dead

Problem:-

This is a known issue with SQL 2005 SP2. To fix this permanently, you need to apply the following hotfix for SQL Server 2005

You cannot start the SQL Server Agent service of a failover cluster of SQL Server 2005 if the build of SQL Server is 3179 or a later build


To fix  the issue temporarily by creating a Named Pipe alias called “SQLAgentAlias”, and now SQL agent service will run. 

Resolution:-
Configure SQL Server Agent to connect the SQL Server service by using the named pipes protocol. To do this, follow these steps on each cluster node:
  1. Open SQL Server Configuration Manager.
  2. In SQL Server Configuration Manager, click SQL Native Client Configuration, right-click Aliases, and then click New Alias.
  3. In the Alias - New dialog box, select Named Pipes in the Protocol list.
  4. In the Alias Name box, specify the name of the alias.
  5. In the Server box, specify the instance of SQL Server 2005, and then click OK.
  6. Open SQL Server Management Studio, and then connect the instance of SQL Server 2005.
  7. Right-click SQL Server Agent, and then click Properties.
  8. In the SQL Server Agent Properties dialog box, click Connection.
  9. In the Alias local host server box, type the name of the alias that you specified in step 4, and then clickOK.
  10. In SQL Server Management Studio, right-click SQL Server Agent, and then click Restart.

To Fix, this issue permanently pleases upgrade the SQL server 2005 from 3179 to higher build.

No comments: