SQL Server @@SERVERNAME returning 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.

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

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