Solved

SQL Server Linked DB Query creates multiple connections

Posted on 2013-11-22
2
33 Views
Last Modified: 2015-11-05
I created a linked server in SQL 2008 to an external DB without any issues. However, each time I run a query against this DB, there seem to be multiple connections to the DB it's linking to. For example, I ran a simple SELECT col1 FROM LINKTEST..SYSTEM.table
and there were two connections made to the external DB server.
Looking at the logging from the other server it looks like it first does a SELECT * from SYSTEM.table and then actually runs SELECT col1 FROM LINKTEST.SYSTEM.table

Is this to be expected? It seems like each time I add a table to the query I get two more processes on the external DB.
0
Comment
Question by:esb91077
2 Comments
 
LVL 26

Accepted Solution

by:
Zberteoc earned 100 total points
ID: 39669023
If you did everything according to documentation, I am referring to the linked server, and your query works then I wouldn't worry.  If you want to find out what the SQL engine does in the background then use the SQL profiler and you will see,
0
 

Author Comment

by:esb91077
ID: 39669864
It's an issue because the external DB that it's connecting to is a per connection license. So they are quickly running out licenses because complex queries are making a ton of connections. I'll look into SQL profiler to see what I can find.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question