Linked server locking! | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Linked server locking!

I’m trying to trouble shoot a problem that i am having with a linked server and i have not found the problem yet and have run out of things to try: I have a link from A to B. Both A and B are SQL server (both 2000 SP3). Some applications access info from B always through the same login account(remoteUser) through the linked server. The linked server on A is set up to only allow login for RemoteUser. Over the past few months on a few occasions the linked server seems to be ‘locked’. All applications using the linked server come back with a message "Connection busy" If i go to the process info on B and kill the process that has the login RemoteUser then this clears everything and all the applications run ok again. [On B the remoteuser login has mainly read only permissions on some views and tables. The remoteUser login also has two update permissions on tables and a couple of execute permissions on SPs]. This does sound like a locking problem but how do i go about finding out what application/query is causing the problem? Is it possible that because i am using one login that this could cause a lock?
That’s kind of an odd issue. What version of MDAC are you on with these two servers? Have you checked to see if the connection in questions has uncommitted transactions? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
The versions of MDAC are: Server A = 2.71.9030.9
Server B = 2.71.9040.2 How do you check to see if the connection has uncommitted transactions? CE