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.
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.
Here is the text of the error message.
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.
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.
- Make sure SELECT @@SERVERNAME is giving the right server name.
- 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.
- SQL SERVER – Stored Procedure and Transactions
- SQL SERVER – SSMS: Top Transaction Reports
- SQL SERVER – Find Total Number of Transactions on Interval
Reference: Pinal Dave (https://blog.sqlauthority.com)
