Linked server points to the current instance

What this check looks for

A linked server defined on this instance whose data source resolves back to this same instance. This is called a loopback linked server.

The check reads sys.servers and works out where each SQL Server linked server really points. It recognizes the local server through the forms people actually type:

• ., (local), localhost, 127.0.0.1 and ::1
• the machine name, with or without a domain suffix
• the physical node name and the local network address
• a tcp:, lpc: or admin: prefix in front of any of those

It then compares the port when one is given, because a port wins over an instance name when the client connects, and otherwise compares the instance name. A linked server to a different instance on the same machine is not reported.

Not reported: linked servers created by replication for a local distributor, publisher or subscriber, and linked servers that use a provider other than SQL Server.

Why it matters

A loopback linked server makes SQL Server talk to itself over a connection, as if it were a stranger. Every query through it pays for that.

• A second connection to the same server. The remote call runs in its own session, not in the caller’s.
• No shared transaction. The two sessions can not take part in one local transaction without a distributed transaction, so a rollback in the caller does not undo what the linked call did unless MSDTC is involved.
• Lost statistics. The optimizer can not see the remote side’s statistics, so it guesses row counts and often picks a poor plan, such as pulling a whole table across to filter it.
• It can block itself. The caller holds locks, the linked call asks for the same ones from another session, and neither can proceed.
• Hidden dependencies. A query against [Loopback].Sales.dbo.Orders looks like it belongs to another server. A search for what uses the Sales database will not find it.
• It breaks when the database moves. Restore the database to another server and the code still points at the old one, or at the new server itself, whichever the name resolves to.

It is Low severity because it usually works. The problem is that it works slowly, and fails in the one place you did not expect.

How to confirm it yourself

List the linked servers and where they point:

SELECT name, product, provider, data_source, provider_string
FROM sys.servers
WHERE server_id <> 0
  AND is_linked = 1;

A data_source that is this machine, localhost, ., or this instance’s own name is a loopback. Compare against what this instance is called:

SELECT SERVERPROPERTY('MachineName')  AS MachineName,
       SERVERPROPERTY('InstanceName') AS InstanceName,
       CONNECTIONPROPERTY('local_tcp_port') AS TcpPort;

Then find the code that uses it. Module definitions are the first place to look:

SELECT OBJECT_SCHEMA_NAME(object_id) AS SchemaName,
       OBJECT_NAME(object_id)        AS ObjectName
FROM sys.sql_modules
WHERE definition LIKE N'%[[]LoopbackName]%'
   OR definition LIKE N'%LoopbackName.%';

Replace LoopbackName with the linked server’s name. This does not find queries in application code, Agent job steps or SSIS packages, so search those separately.

How to fix it

Change the code to use three part names for the local database, then drop the linked server.

1. Find every use, in modules, Agent job steps, SSIS packages and application code.
2. Rewrite each reference. A four part name becomes a three part name:

-- before: goes out through the linked server and comes back in
SELECT * FROM [Loopback].Sales.dbo.Orders;

-- after: a plain local query
SELECT * FROM Sales.dbo.Orders;

3. Test the changed code. Results should match. Plans usually improve, since the optimizer can use real statistics again.
4. Drop the linked server and its logins.

EXEC sp_dropserver @server = N'Loopback', @droplogins = 'droplogins';

If some of the code can not be changed yet, leave the linked server in place until it can. The finding is a configuration warning, not an outage.

How long it takes

About half an hour for a linked server with a few known users. Finding and changing the code that uses it is the real work, and it grows with how long the linked server has been there.

Related reports

Related checks

Frequently asked questions

Why would anyone create one? Usually to keep old code working after a database moved. The code used a linked server name for the old home of the database, and a loopback to the new home was the quick way to keep it running.

Is it ever the right answer? Occasionally, to test code that will run across two servers in production, or to run a statement under a different login. If it is there for one of those reasons it is a decision, and this check is only reminding you it exists.

My linked server uses a different instance on the same machine. It is not reported. The check matches the instance name or port, so only a link that reaches the current instance counts.

Does this report replication linked servers? No. Those created for a local distributor, publisher or subscriber are skipped on purpose.