Changing SQL Server Instance Name - Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 44

If you change name of the computer where SQL Server is running, new name will automatically recorded in sys.servers metadata table. Below is the procedure to change name of the SQL Server Instance if it is running in a stand-alone server.

sp_dropserver     -- Removes a server from the list.

sp_addserver       -- Defines a remote server or the name local server.

sp_dropserver

sp_addserver

sp_dropserver kalyan

sp_addserver kalyan\SQL2005

After running the above command we need to restart the SQL Instance.

Some Considerations while renaming the SQL Instance.
1) If the computer has remote remotes it may generate an error.
Server: Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 44
There are still remote logins for the server 'kalyan'.

We can drop remote logins using below command

sp_dropremotelogin

sp_dropremotelogin

sp_dropserver [old_server_name], 'droplogins';

2) We need to manually re-configure linked server instance names.