Why does SQL Server @@SERVERNAME return my old machine name?

Question

I just stumbled across an issue in my SQL Server 2008 R2 - When I call @@SERVERNAME, it's returning my OLD computer's machine name, rather than the current one. Why is this? And how can I fix it? SQL Server somehow is remembering the old machine name.

Penland

Probably stored on the kernal databases during the installation. – Farmergeneral

Thanks for the previous answers, helped me fix my issue. Just a note to others, if you want the whole server name like returned from @@SERVERNAME, you can use SELECT SERVERPROPERTY('ServerName'); – Tann

Just an interesting thing to add - I just changed a machine name running SQL Server 2014 - and it did this task automatically. – Penland

Answer 1

This is well known and documented, see Rename a Computer that Hosts a Stand-Alone Instance of SQL Server:

When you change the name of the computer that is running SQL Server, the new name is recognized during SQL Server startup. You do not have to run Setup again to reset the computer name. Instead, use the following steps to update system metadata that is stored in sys.servers and reported by the system function @@SERVERNAME:

sp_dropserver <old_name>;
GO
sp_addserver <new_name>, local;
GO

You can also use SERVERPROPERTY('MachineName') which is guaranteed to always return the updated name:

MachineName Windows computer name on which the server instance is running. For a clustered instance, an instance of SQL Server running on a virtual server on Microsoft Cluster Service, it returns the name of the virtual server.

SERVERPROPERTY('ComputerNamePhysicalNetBIOS') will return the current active node in a cluster, or the same value as 'MachineName' on a non-clustered instance.

Edit (by a 3rd party) to add WEFX's comment in case anyone misses it:

Also, you'll need to restart your SQL services (or reboot the SQL Server) in order for SELECT @@SERVERNAME to return the accurate (new) servername

Squeaky

Also, you'll need to restart your SQL services (or reboot the SQL Server) in order for 'SELECT @@SERVERNAME' to return the accurate (new) servername. – Electromechanical

declare @s varchar(50); select @s = cast(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as varchar); exec sp_dropserver @@servername; exec sp_addserver @s,local; – Moyer

Answer 2

I have tried all the possible solutions and the approved answer didn't work for me. I have surfed a bit and come up with the perfect solution. I hope somebody find this helpful.

1) Open registry by Window + R key. Type regedit

2) Go to *HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server* You will see plenty of numbered directories ( 100,120,130 ....)

OR

you can simply type "Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\" in the address bar of Registry Editor

3) go through all the numbered directories and see if you can find "Machines" directory inside

4) Once you find "Machines" change the OriginalMachineName key to the server name you want it to be. That's actually Original machine name when the windows was first installed.

PS: My path was OriginalMachineName > Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\Machines

Senecal

Interestingly, for AWS SQL Server AMIs the value at this registry location is different than the default machine name when you spin up a new machine, which is then likely different if you manually name the machine. The only solution that works consistently across our machines is using SERVERPROPERTY('MachineName') instead of @@SERVERNAME. – Harumscarum

Answer 3

I suspect this is because the instance was a default installation and inherited the machine name at the time and has kept it. Try this?

SELECT SERVERPROPERTY('MachineName')

Chaiken

Answer 4

Sometimes you can get an error There are still remote logins or linked logins for the server 'yourServerName' when you run sp_dropserver 'oldServerName';

If you have this kind of error then try to run sp_dropserver 'oldServerName', 'droplogins'; instead.

Nitrite

Answer 5

First, with two SQL queries below, check your Windows device name and your MSSQL server name respectively:

SELECT HOST_NAME()  -- Get Windows device name
SELECT @@SERVERNAME -- Get MSSQL server name

Now, you could check your Windows device name "DESKTOP-NEW" and your MSSQL server name "DESKTOP-OLD\SQLEXPRESS" respectively as shown below:

Next, with the SQL queries below, you can drop your old MSSQL server "DESKTOP-OLD\SQLEXPRESS" and add the new MSSQL server "DESKTOP-NEW\SQLEXPRESS":

EXEC sp_dropserver 'DESKTOP-OLD\SQLEXPRESS'       -- Drop server
GO
EXEC sp_addserver 'DESKTOP-NEW\SQLEXPRESS', local -- Add server
GO

Now, you could drop the old MSSQL server "DESKTOP-OLD\SQLEXPRESS" and add the new MSSQL server "DESKTOP-NEW\SQLEXPRESS" as shown below:

But, even if you check your MSSQL server name again, your MSSQL server name is still the old MSSQL server "DESKTOP-OLD\SQLEXPRESS" as shown below. *But no worries, in spite of that, the old MSSQL server "DESKTOP-OLD\SQLEXPRESS" is actually dropped properly and the new MSSQL server "DESKTOP-NEW\SQLEXPRESS" is actually added properly:

If you really want to check that the new MSSQL server "DESKTOP-NEW\SQLEXPRESS" is actually added properly, restart MSSQL with SQL Server Configuration Manager(SSCM). This is How to find SQL Server Configuration Manager in your Windows Machine:

Then, if you check your MSSQL server name again, your MSSQL server name is now the new MSSQL server "DESKTOP-NEW\SQLEXPRESS" as shown below.

Halverson

Note that HOST_NAME() returns the machine name where the SSMS instance is executing, not the Server host of the SQL Server you're connected to. If you are connected to a remote SQL Server instance via SSMS, HOST_NAME() will give you YOUR machine name, not the name of the SQL Server instance. You should use SERVERROPERTY('MachineName') instead. – Bandung

Answer 6

In my case I couldn't drop the old name; I could restart MSSQL or the VM all I wanted, @@SERVERNAME kept returning the old name while every other method (including SERVERPROPERTY('SERVERNAME')) was returning the new name, which was causing all sorts of issues. Turns out dropping the new name and re-adding it (and restarting MSSQL of course) fixes this odd issue:

sp_dropserver <new_name>; GO
sp_addserver <new_name>, local; GO

Superload