February 6, 2006 at 9:41 am
Hi all,
I have server A linked with server B with linked server. I have added llinked server using a remote user "Report" that is used for authentication.
sp_linkedservers
SRV_NAME SRV_PROVIDERNAME SRV_PRODUCT SRV_DATASOURCE SRV_PROVIDERSTRING SRV_LOCATION SRV_CAT
-------------------------------------------------------------------------------------------------------------
A Server SQLOLEDB SQL Server A Server NULL NULL NULL
sp_helplinkedsrvlogin
Linked Server Local Login Is Self Mapping Remote Login
---------------- ------------- --------------- --------------
A Server NULL 0 report
From time to time I get into a blocking situation. I cannot query anything on server B. The only thing I have found is to restart service of server B.
In query analyzer I can query server A ( connected to server A ). I can query server B ( connected to server B ). But when I query server A using the linked server I get an infinte process ( I have timeout set to 0 ). It seams that leinked server is wait for a lock to be released but that is not the problem because I have tried issuing SET TRANSACTION ISOLATION LEVEL READUNCOMMITTED and the query still hang.
I have no more clues on where to look please help
Sylvain
February 6, 2006 at 10:23 am
Can you test it with a very simple query, specifying a timeout if necessary?
Start a trace with sql profiler on the linked server and look how far the query got.
February 6, 2006 at 10:58 am
More Info :
On server A I can query a single table a time. But when I try to join 2 specific tables it will not resume. On server B I can run the joined query with no troubles
If I create 2 new tables and query both and join them it works. On server A and B.
February 6, 2006 at 11:06 am
Thanks for the info. So the problem is reduced to a specific table.
Not sure if SET TRANSACTION ISOLATION LEVEL READUNCOMMITTED propagates to a linked server.
If you use the sql profiler, can you trace your query to the specific table?
Do the tables have an index on the join/where-statement?
February 6, 2006 at 11:16 am
I tried SET TRANSACTION ISOLATION LEVEL READUNCOMMITTED with no succes That was my first guest )
Now the 2 new tables I created cannot be joined.
Index yes. Can it be related ?
Guess what now the table I created with 3 row in it cannot be self joined. Wow this puzzel me. I can query it
SELECT a FROM testTable -- works
SELECT a.a FROM testTable a INNER JOIN testTable b ON a.a = b.a -- does not work
thanks
February 6, 2006 at 2:16 pm
SELECT a FROM testTable -- works
SELECT a.a FROM testTable a INNER JOIN testTable b ON a.a = b.a -- does not work
Is there an error message or what do you mean with does not work?
February 6, 2006 at 2:26 pm
When I say it does not work I mean that the query will just hang.
I am querying a table with 3 rows. It should not take more then a nanosecond
I have to cancel the query after a few minutes because it will never come back.
I have traced the the query
I know it will look ugly but here it goes
************** selecting from a single table *****************
TraceStart 2006-02-06 13:42:00.573
SQL:BatchStarting select * from Server a.myDataBase.dbo.test123
SQL Query Analyzer myLogin myDomain\myLogin 2436 189 2006-02-06 13:42:09.447
Lock:Acquired SQL Query Analyzer myLogin myDomain\myLogin 0 2436 189 2006-02-06 13:42:09.447
Lock:Acquired SQL Query Analyzer myLogin myDomain\myLogin 0 2436 189 2006-02-06 13:42:09.447
Lock:Released SQL Query Analyzer myLogin myDomain\myLogin 2436 189 2006-02-06 13:42:09.447
Lock:Released SQL Query Analyzer myLogin myDomain\myLogin 2436 189 2006-02-06 13:42:09.447
Lock:Acquired SQL Query Analyzer myLogin myDomain\myLogin 0 2436 189 2006-02-06 13:42:09.447
Lock:Released SQL Query Analyzer myLogin myDomain\myLogin 2436 189 2006-02-06 13:42:09.447
Lock:Acquired SQL Query Analyzer myLogin myDomain\myLogin 0 2436 189 2006-02-06 13:42:09.447
Lock:Released SQL Query Analyzer myLogin myDomain\myLogin 2436 189 2006-02-06 13:42:09.447
SQL:StmtStarting select * from Server a.myDataBase.dbo.test123
SQL Query Analyzer myLogin myDomain\myLogin 2436 189 2006-02-06 13:42:09.680
Lock:Acquired SQL Query Analyzer myLogin myDomain\myLogin 0 2436 189 2006-02-06 13:42:09.680
Lock:Released SQL Query Analyzer myLogin myDomain\myLogin 2436 189 2006-02-06 13:42:09.680
Lock:Acquired SQL Query Analyzer myLogin myDomain\myLogin 0 2436 189 2006-02-06 13:42:09.680
Lock:Released SQL Query Analyzer myLogin myDomain\myLogin 2436 189 2006-02-06 13:42:09.680
Lock:Acquired SQL Query Analyzer myLogin myDomain\myLogin 0 2436 189 2006-02-06 13:42:09.680
Lock:Released SQL Query Analyzer myLogin myDomain\myLogin 2436 189 2006-02-06 13:42:09.680
SQL:StmtCompleted select * from Server a.myDataBase.dbo.test123
SQL Query Analyzer myLogin myDomain\myLogin 0 7 0 109 2436 189 2006-02-06 13:42:09.680
SQL:BatchCompleted select * from Server a.myDataBase.dbo.test123
SQL Query Analyzer myLogin myDomain\myLogin 953 15 0 343 2436 189 2006-02-06 13:42:09.447
************ I have then tried to select from the table with a self join ***********************
SQL:BatchStarting set parseonly on
SQL Query Analyzer myLogin myDomain\myLogin 2436 189 2006-02-06 13:43:07.073
SQL:BatchCompleted set parseonly on
SQL Query Analyzer myLogin myDomain\myLogin 0 0 0 0 2436 189 2006-02-06 13:43:07.073
SQL:BatchStarting select * from Server a.myDataBase.dbo.test123 a
inner join Server a.myDataBasedbo.test123 b on a.a = b.a
SQL Query Analyzer myLogin myDomain\myLogin 2436 189 2006-02-06 13:43:07.103
SQL:BatchCompleted select * from Server a.myDataBase.dbo.test123 a
inner join Server a.myDataBasedbo.test123 b on a.a = b.a
SQL Query Analyzer myLogin myDomain\myLogin 0 0 0 0 2436 189 2006-02-06 13:43:07.103
SQL:BatchStarting set parseonly off
SQL Query Analyzer myLogin myDomain\myLogin 2436 189 2006-02-06 13:43:07.213
SQL:BatchCompleted set parseonly off
SQL Query Analyzer myLogin myDomain\myLogin 0 0 0 0 2436 189 2006-02-06 13:43:07.213
SQL:BatchStarting select * from Server a.myDataBasedbo.test123 a
inner join Server a.myDataBase.dbo.test123 b on a.a = b.a
SQL Query Analyzer myLogin myDomain\myLogin 2436 189 2006-02-06 13:43:10.010
********* A few locks acquired but Batch completed never obtained **************
Thank you very much for time
February 6, 2006 at 2:37 pm
That is really weird.
What is the service pack of sql server & your client tools?
OS is up to date?
Any antivirus software present?
February 6, 2006 at 2:41 pm
Anti virus fully updated regulary OS updated too.
I have to admit that I am still on SP3
I am think about not sleeping tonigh and get this done.
I rarely get the chance to put this server down. I might have some opportunity during the night
Thanks for your support
Sylvain
February 6, 2006 at 3:46 pm
You're welcome.
Make sure the antivirus isn't scanning the ldf, mdf,ndf files.
February 7, 2006 at 6:31 am
I have restarted both servers. The problem goes away when I get to this weird locking.
I had to, some reports needed for upper management. So now I do not have the problem anymore and can't investigate.
I keep an eye on this. When I comes back I'll make more test.
Thank for everyones attention.
Sylvain
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy