Linked server hangs

Question

Post reply

Linked server hangs

Sylvain Coutu

Ten Centuries

Points: 1077
More actions
February 6, 2006 at 9:41 am

#113289

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

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 1

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.

Sylvain Coutu Ten Centuries Points: 1077 More actions · Answer 2

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.

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 3

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?

Sylvain Coutu Ten Centuries Points: 1077 More actions · Answer 4

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

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 5

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?

Sylvain Coutu Ten Centuries Points: 1077 More actions · Answer 6

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: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

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 7

That is really weird.

What is the service pack of sql server & your client tools?

OS is up to date?

Any antivirus software present?

Sylvain Coutu Ten Centuries Points: 1077 More actions · Answer 8

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

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 9

You're welcome.

Make sure the antivirus isn't scanning the ldf, mdf,ndf files.

Sylvain Coutu Ten Centuries Points: 1077 More actions · Answer 10

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