SQL SERVER – Linked Server Error – Msg 3910 – Transaction Context In Use By Another Session

SQL
No Comments

One of my clients contacted me for an error that appeared while performing a distributed transaction over a linked server. The interesting part was that the linked server was local SQL Server, this is also called a loopback linked server. In this blog, I would share my understanding and the solution of Linked Server Error – Msg 3910 – Transaction context in use by another session.

SQL SERVER - Linked Server Error - Msg 3910 - Transaction Context In Use By Another Session Transaction-context-800x284

The situation with my client was that when he runs a query that performs a distributed transaction, it fails. Here is the screenshot of the behavior.

SQL SERVER - Linked Server Error - Msg 3910 - Transaction Context In Use By Another Session local-linked-srv-01

Here is the text of the error message.

Solarwinds

Msg 3910, Level 16, State 2, Line 1
Transaction context in use by another session.

In the above screenshot, the IP used for the linked server was the IP address for the local machine.

If you ever try to create a linked server for a local server, you will get an error message.

SQL SERVER - Linked Server Error - Msg 3910 - Transaction Context In Use By Another Session local-linked-srv-02

Text: You cannot create a local SQL Server as a linked server.

WORKAROUND/SOLUTION

Here are a few things we should check if we get this error message.

  1. Make sure SELECT @@SERVERNAME is giving the right server name.
  2. Make sure SELECT * FROM SYS.SERVERS is showing local server name under server_id = 0

If you must fetch data from the local server, there is no real need for creating a linked server. You can use three-part naming (database.schema.object) instead of four-part naming (server.database.schema.object)

I was able to locate old documentation here which says Loopback linked servers cannot be used in a distributed transaction.

Please comment and let me know if you find some other solution. Here are a few additional blog posts which you may find interesting.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , , ,
Previous Post
MySQL – Download Sample Database – Sakila, World, Employee
Next Post
SQL SERVER – Always On Listener Not Coming Online – Failed to Create New NBT Interface, Status 1450

Related Posts

Leave a Reply

Menu