Changing Server name - sp_dropserver,sp_addserver

When one changes the name of the computer running the SQL Server, one needs to
perform the following steps to ensure that SQL Server also uses the new updated
name.
First to check whether the SQL Server has the correct name,
execute the following query


SELECT @@SERVERNAME                 AS oldname,
       Serverproperty('Servername') AS actual_name


@@Servername gives the computer name that has been set/registered with SQL Server
Serverproperty('Servername') provides the newly updated name from windows.

If @@servername,Serverproperty('Servername') results are different then
one should update the new name on the SQL Server. To update, follow the steps
provided below.

step 1 :- sp_dropserver/sp_addserver

EXEC  Sp_dropserver
  'oldservername'

>
EXEC  Sp_addserver
  'newservername' ,
  'local'


step 2 :- update sysjobs
Applies only for SQL 2k.

USE msdb
UPDATE sysjobs
SET    originating_server = Serverproperty('Servername')

step 3 :- Restart SQL Service
Restart SQL Services for the changes to take effect.

Even if one doesnt update the Servername on SQL Server, SQL Server will still function,but one can face a few issues like the error

'Error 14274: Cannot delete a job that originated from an MSX server'
The above error appears, when one attempts to change the SQL jobs properties like enabled/disabled and if the Servername is not correctly updated.
Linked server configurations may not work correctly. So in general, if the servername is changed its better to update it on SQL Server using the above mentioned steps.
For more details refer here