Freigeben über

SQL Server Agent cannot start because the instance of the Server Instance is not the expected Instance

  • Artikel

Problem:
SQL Server Agent cannot start because the instance of the Server Instance is not the expected Instance

The SQL Server Agent may fail to start after a restart of the SQL Services. You may see this issue when you have multiple SQL Server instances installed on the Sever. But this issue can also occur with instances running across machines. The troubleshooting steps are almost similar unless where its specified

The easier way to troubleshoot an SQL Server Agent service failure is by peeking in to the SQLAGENT.out file. This file is located by default in the LOG folder under the SQL Installation folder.

Typically on a default installation the file is here -> "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLAGENT.OUT"

Open the SQLAGENT.out file in any text editor. Since the Service fails right during the start the files size should be pretty small and little but useful information in the log file.

Below is a sample SQLAGENT.out which we will discuss further

2010-04-30 21:50:10 - ? [100] Microsoft SQLServerAgent version 9.00.4035.00 ((Unknown) unicode retail build) : Process ID 2216

2010-04-30 21:50:10 - ? [101] SQL Server RASH1935803\PUB version 9.00.4053 (0 connection limit)

2010-04-30 21:50:10 - ? [102] SQL Server ODBC driver version 9.00.4035

2010-04-30 21:50:10 - ? [103] NetLib being used by driver is DBMSSOCN.DLL; Local host server is RASH1935803\SUB

2010-04-30 21:50:10 - ? [310] 4 processor and 2048 MB RAM detected

2010-04-30 21:50:10 - ? [339] Local computer is RASH1935803 running Windows NT 5.2 (3790) Service Pack 2

2010-04-30 21:50:10 - ! [000] SQLServerAgent cannot start because the instance of the server (PUB) is not the expected instance (SUB)

2010-04-30 21:50:10 - ? [098] SQLServerAgent terminated (normally)

Its easy to identify the error from the log files. Now let us concentrate on the error

"SQLServerAgent cannot start because the instance of the server (PUB) is not the expected instance (SUB)"

The very statement says there was something wrong when the SQL Agent tried connecting to the server. The error is a little more detailed as to, it says "the instance of the server (PUB) is not the expected instance"

Let us start the troubleshooting.

From the error statement it’s clear that the SQL Agent is trying to connect to instance (PUB) than its own instance(SUB).

Get necessary login information to connect to the instance(PUB). This instance should exist or else we would have got a different error in the SQLAgent.out.

Use the SQL Server Profiler and connect to the (PUB)instance. Make sure the Audit login and SP:Starting events are traced. Refer "Using SQL Server profiler" for more details https://msdn.microsoft.com/en-us/library/ms187929.aspx

While this trace is running, go ahead and start the SQL Agent of (SUB) instance. On failure, toggle to the Profiler and stop the trace. You will see couple of stored procedures being executed.

EXECUTE msdb.dbo.sp_sqlagent_has_server_access

EXECUTE msdb.dbo.sp_sqlagent_get_startup_info

This makes our assumption clear that the SQL Agent of (SUB) instance is indeed connecting to the wrong instance(PUB)

Now let us focus on the Settings of (SUB) instance.

Since the SQL Agent is not running we may not be able to see the configuration details using SSMS rather we should look in to the registry. The SQL Server Registry location is detailed in the article below

https://msdn.microsoft.com/en-us/library/ms143547(SQL.90).aspx

For SQL 2008 Refer https://msdn.microsoft.com/en-us/library/ms143547.aspx

On a typical installation you should find the SQL Server Agent key under "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\SQLServerAgent" Here the MSSQL.1 is for the instance (SUB)

Check the Data of the “ServerHost” Value. The data should be empty or should be pointing to the instance (SUB). If you see that this data is referring to (PUB), you have got the problem. Just delete the data or edit it to point to instance (SUB). Try restarting the SQL Agent you issues is in the thin air now

Continue here if you see that the “ServerHost” data is empty and if the data is correctly pointing to the Instance (SUB).

Now this is a generic connectivity issue and we will try a simple test. Use the "SQL Server Management Studio" to connect to the instance (SUB). On successful connection, open a "New Query" window and run the command "Select @@ServerName". This should return the result (PUB) instead of (SUB). Here we have trapped the problem. It is clear that the connection is being redirected to the wrong instance.

The only check at this point is to look at the Aliases. Open the "SQL Server Configuration Manger" Expand the "SQL Native Client Configuration" and Click on the Aliases.

You must see an Alias that is misconfigured. As per the symptoms during the troubleshooting, we should be seeing an alias that points to the (SUB) instance but the Port is wrongly configured to that of instance (PUB). Edit the Alias and change the port to that of instance (SUB). You can get the port information from the Instance(SUB) Errorlog. You SQL Agent should start and do its ske-joo-ling now.

More Info:

When the SQL Server Agent service starts, it calls the “sp_sqlagent_get_startup_info” stored procedure to get the instance name to connect to the correct instance of SQL Server. In our case this was failing since this procedure was being run on the wrong instance and returns the wrong Instance Name.

A similar error is reported in SQL 2000 and SQL 7.0 but the symptoms are different. Please have look at this article too https://support.microsoft.com/kb/321824

Regards,

Levi Justus

TL, SQL Server support.

Comments

  • Anonymous
    May 11, 2010
    This post saved my life!  I have SQL Server 2008 installed on Windows 7 Ultimate, and on my system, I removed the bad alias from the registry key HKLMSOFTWAREMicrosoftMicrosoft SQL ServerMSSQL10.MSSQLSERVERSQLServerAgentServerHost and SQL Server Agent started up immediately. I have to say this: Windows 7 is an amazingly well-done OS, and SQL Server Management Studio is a very nicely put-together GUI.  Neither system deserves a fix like the above that requires going into the Registry. Again, thanks for this blog post. Todd S.