It seems to happen from time to time: your network engineers decide on a new
network topology or naming scheme and they want to rename one or more of your
SQL Server machines or worse your desktop machine!

Renaming a SQL Server instance is not as simple as renaming the computer in
My Computer Properties.
After you’ve restarted windows, you will find that while the instance is now
available on the network with the new machine name, internally @@SERVERNAME will
probably still be using the old name. This will upset a number of features
within SQL Server and Management Studio and some 3rd party tools.

Using the following sql, you can see if @@SERVERNAME has the incorrect value:

SELECT @@SERVERNAME As [@@SERVERNAME],
CAST(SERVERPROPERTY(‘MACHINENAME’) AS VARCHAR(128)) + COALESCE(” +

CAST(SERVERPROPERTY(‘INSTANCENAME’) AS VARCHAR(128)), ”) As RealInstanceName

Both @@SERVERNAME and RealInstanceName should be identical. After a recent
name change my results looked like this:


@@SERVERNAME                      RealInstanceName
--------------------------------- ---------------------------------
IAUTO-124F92                   JKUITERS-DEV

To correct @@SERVERNAME for a default instance use the following commands:

exec sp_dropserver old_name
GO
exec sp_addserver new_name, ‘local’
GO

To correct @@SERVERNAME for a named instance:

exec sp_dropserver old_nameinstancename
GO
exec sp_addserver new_nameinstancename, ‘local’
GO

Download the sql script to fix
your @@SERVERNAME.

Related

MSDN Article.

Tags: Management Studio, SQL Server, Tools