|
|
You're in the
sql server connect
group:Strange SQL 2000 connections ! (ghost connections)
sql server connect:
Hi all,
A customer have a strange issue: He connects .Net applications and Access db's to a SQL Server 2000 SP3. Whenever a user connects using Access, another connection appears (sp_who, sysprocesses) with the same hostprocess and another hostname. Sometimes the hostname is the one of a machine that is not anymore in the network since weeks. When the Access is closed the "ghost connection" is also closed. The problem appeared when he upgraded his SQL Server machine from Windows 2000 to Windows Server 2003 SP1. Because of those "ghost connections" he must restart its server every 2 days since one month. I am desperatly looking for a solution and I don't want to advice him to install SQL Server 2000 SP 4 "just to try" not being sure it will solve the preoblem. I had a look at all fixes in SP4 and did not see anything related to it. Any idea, suggestion, would be appreciated. Have a nice day, Pascal Lengelé
Maybe this article will help you in the right direction:
http://support.microsoft.com/default.aspx/kb/137983
Hi,
Thanks but it doesn't help at all. When I sp_who or check within the sysprocesses table it contains invalid information. E.g. The user User1 connects from machine Computer1 with Access (PID 1234) The contents of the sysprocesses will be: hostprocess nt_username hostname program_name 1234 User1 Computer1 Access 1234 User1 Computer(*1) Access *1 the computer name is a wrong one on wich no process 1234 exist Another info: The net_address column of sysprocesses contains the same mac address for two different machine names (Computer1) !!! Sound strange, no ? I am currently checking if it is a connection pooling issue. Thx, anyway,
Pascal,
Access has been known to remember the machine that was used to create the connections, so that can improperly identify a hostname that is no longer involved in the activity. This is annoying, but I don't know of any problem that it causes. I suspect that the 'ghost' connection will continue to report CPU of 0 and DiskIO of 0, if you use sp_who2 instead of sp_who to check it out. Since the 'ghost' connection goes away when the Access connection is closed, I don't understand why your customer feels a need to restart the server every 2 days. Is there some problem that you do not mention, such as locks being held, resources consumed, or such? FWIW, I don't remember seeing this in connection with Access, but some 'ghost' connections will not disappear until the Windows users logs out of the network and logs back in again. RLF [quoted text, click to view] "Scal" <Scal@discussions.microsoft.com> wrote in message news:855369D8-4C4B-4703-81C3-B7672336CC84@microsoft.com... > Hi all, > A customer have a strange issue: > He connects .Net applications and Access db's to a SQL Server 2000 SP3. > Whenever a user connects using Access, another connection appears (sp_who, > sysprocesses) with the same hostprocess and another hostname. > Sometimes the hostname is the one of a machine that is not anymore in the > network since weeks. > When the Access is closed the "ghost connection" is also closed. > The problem appeared when he upgraded his SQL Server machine from Windows > 2000 to Windows Server 2003 SP1. > Because of those "ghost connections" he must restart its server every 2 > days > since one month. > > I am desperatly looking for a solution and I don't want to advice him to > install SQL Server 2000 SP 4 "just to try" not being sure it will solve > the > preoblem. I had a look at all fixes in SP4 and did not see anything > related > to it. > > Any idea, suggestion, would be appreciated. > > Have a nice day, > > Pascal Lengelé > Software Consultant
Hello Russel,
Thank you for your answer. Indeed some of the suspected connections do have IO and CPU usage of zero. And yes i did not mention everything (you got me). Here is what I get: - Access 2000 clients are connected to a SQL Server 2000 SP3 - For some reason all users are using the same domain identification - For historical reasons, Access is using both linked-tables and ODBC connections to get on the SQL Server. - .Net applications are performing jobs using distributed transactions on one database/server (I don't know why the DTC is used) using SqlClient. - The Win 2K server has been upgraded to 2K3 SP1 (SP2 just installed this weekend). - After this upgrade they started to have issues like 8525 "Distributed transaction completed. Either enlist this session in a new transaction or the null transaction" during execution of stored procedures using transactions. Here are my investigations: - Problem occurs after the server being up for two days (sounds like a leak somewhere, unclosed connections / transactions ... ?) - The SP's are falling after 15 minutes (probably a time-out) - Many connections are opened at the time the problem occurs - We are now not able to reproduce the problems because the server is now restarted every night, we can only watch what seems to be the beginning of the issues. - What we can see is that a "ghost connection" is opened at the same time a normal connection is opened by the Access database. FYI: I have been called during two projects as problem solving helper, I am not an IT guy but a analyst developper. I wrote a small C# application that lists the suspect connections selecting information from sysprocesses and simply showing it a way we can quickly identify the ghost connections (heartbeat 5 seconds, open connection, selects in sysprocesses, close connection). A strange thing is that if I let this small program run using SqlClient (with or without pooling) it runs the normal way, but if I use ODBC (and a DSN) the connections are not closed immediately (after x minutes) and new connections are sometimes opened (not every time) on next heartbeat. Digging in ... the bits. Best regards,
Pascal,
FWIW, I think that the 'ghost' connections are unrelated to your problems. But I could be wrong. When I look up your error 8525, I only get this: http://support.microsoft.com/kb/834849 For SQL 2000, but an unlikely circumstance for you. http://msdn2.microsoft.com/en-us/library/bb326310.aspx For SQL 2005, not your version. One question is have you set the new keyword name SynAttackProtect to the registry key HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\with value data of 00000000? See http://support.microsoft.com/kb/910228 (This is a SQL 2005 document, but it is discussing a Windows 2003 SP1 (and presumably still in SP 2) behavior that can cause mysterious connection problems for SQL Servers.) What I would also check for is open transactions in your database or databases using: DBCC OPENTRAN(DatabaseName) If you find any open transactions that are more than a few minutes old, investigate to make sure that the transaction has not been orphaned transaction and that important locks are not being held. (I believe Access tends to hold a DB shared lock on object 0. This is harmless.) Locks held for a long time could cause other transactions to time out. (Could the 8525 error leave a transaction open? Do not know.) FWIW, RLF [quoted text, click to view] "Scal" <Scal@discussions.microsoft.com> wrote in message news:DFC661E4-2552-4436-95AB-005BAFF053D2@microsoft.com... > Hello Russel, > > Thank you for your answer. > Indeed some of the suspected connections do have IO and CPU usage of zero. > And yes i did not mention everything (you got me). > Here is what I get: > - Access 2000 clients are connected to a SQL Server 2000 SP3 > - For some reason all users are using the same domain identification > - For historical reasons, Access is using both linked-tables and ODBC > connections to get on the SQL Server. > - .Net applications are performing jobs using distributed transactions on > one database/server (I don't know why the DTC is used) using SqlClient. > - The Win 2K server has been upgraded to 2K3 SP1 (SP2 just installed this > weekend). > - After this upgrade they started to have issues like 8525 "Distributed > transaction completed. Either enlist this session in a new transaction or > the > null transaction" during execution of stored procedures using > transactions. > > Here are my investigations: > - Problem occurs after the server being up for two days (sounds like a > leak > somewhere, unclosed connections / transactions ... ?) > - The SP's are falling after 15 minutes (probably a time-out) > - Many connections are opened at the time the problem occurs > - We are now not able to reproduce the problems because the server is now > restarted every night, we can only watch what seems to be the beginning of > the issues. > - What we can see is that a "ghost connection" is opened at the same time > a > normal connection is opened by the Access database. > > FYI: I have been called during two projects as problem solving helper, I > am > not an IT guy but a analyst developper. > > I wrote a small C# application that lists the suspect connections > selecting > information from sysprocesses and simply showing it a way we can quickly > identify the ghost connections (heartbeat 5 seconds, open connection, > selects > in sysprocesses, close connection). > A strange thing is that if I let this small program run using SqlClient > (with or without pooling) it runs the normal way, but if I use ODBC (and a > DSN) the connections are not closed immediately (after x minutes) and new > connections are sometimes opened (not every time) on next heartbeat. > > Digging in ... the bits. > > Best regards, > > Pascal
I no longer like to comment on Access/JET database issues--there are simply
too many of them that are solved by using better, smarter, more scalable and more secure database engines like SQL Server (any version), Oracle and many others. However, when I wrote VB data access documentation (some time ago), I warned that additional connections would be opened automatically by JET to support updatable cursors. This additional connection permits the code to post updates to the rows before rowset population is complete. I'm surprised these docs are not online where they can be searched... I expect that this is the extra connection you're seeing in the profiler. Incidentally, Access/JET is a very challenged interface to SQL Server. It's just tolerable when accessing JET databases but the way it handles SQL Server is ... well, not particularly satisfactory to be kind. hth -- ____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ Visit www.hitchhikerguides.net to get more information on my latest book: Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ----------------------------------------------------------------------------------------------------------------------- [quoted text, click to view] "Russell Fields" <russellfields@nomail.com> wrote in message news:uY%23EFL%23lHHA.3484@TK2MSFTNGP02.phx.gbl... > Pascal, > > FWIW, I think that the 'ghost' connections are unrelated to your problems. > But I could be wrong. > > When I look up your error 8525, I only get this: > http://support.microsoft.com/kb/834849 For SQL 2000, but an unlikely > circumstance for you. > http://msdn2.microsoft.com/en-us/library/bb326310.aspx For SQL 2005, not > your version. > > One question is have you set the new keyword name SynAttackProtect to the > registry key > HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\with > value data of 00000000? See http://support.microsoft.com/kb/910228 (This > is a SQL 2005 document, but it is discussing a Windows 2003 SP1 (and > presumably still in SP 2) behavior that can cause mysterious connection > problems for SQL Servers.) > > What I would also check for is open transactions in your database or > databases using: > DBCC OPENTRAN(DatabaseName) > If you find any open transactions that are more than a few minutes old, > investigate to make sure that the transaction has not been orphaned > transaction and that important locks are not being held. (I believe Access > tends to hold a DB shared lock on object 0. This is harmless.) Locks > held for a long time could cause other transactions to time out. (Could > the 8525 error leave a transaction open? Do not know.) > > FWIW, > RLF > > "Scal" <Scal@discussions.microsoft.com> wrote in message > news:DFC661E4-2552-4436-95AB-005BAFF053D2@microsoft.com... >> Hello Russel, >> >> Thank you for your answer. >> Indeed some of the suspected connections do have IO and CPU usage of >> zero. >> And yes i did not mention everything (you got me). >> Here is what I get: >> - Access 2000 clients are connected to a SQL Server 2000 SP3 >> - For some reason all users are using the same domain identification >> - For historical reasons, Access is using both linked-tables and ODBC >> connections to get on the SQL Server. >> - .Net applications are performing jobs using distributed transactions on >> one database/server (I don't know why the DTC is used) using SqlClient. >> - The Win 2K server has been upgraded to 2K3 SP1 (SP2 just installed this >> weekend). >> - After this upgrade they started to have issues like 8525 "Distributed >> transaction completed. Either enlist this session in a new transaction or >> the >> null transaction" during execution of stored procedures using >> transactions. >> >> Here are my investigations: >> - Problem occurs after the server being up for two days (sounds like a >> leak >> somewhere, unclosed connections / transactions ... ?) >> - The SP's are falling after 15 minutes (probably a time-out) >> - Many connections are opened at the time the problem occurs >> - We are now not able to reproduce the problems because the server is now >> restarted every night, we can only watch what seems to be the beginning >> of >> the issues. >> - What we can see is that a "ghost connection" is opened at the same time >> a >> normal connection is opened by the Access database. >> >> FYI: I have been called during two projects as problem solving helper, I >> am >> not an IT guy but a analyst developper. >> >> I wrote a small C# application that lists the suspect connections >> selecting >> information from sysprocesses and simply showing it a way we can quickly >> identify the ghost connections (heartbeat 5 seconds, open connection, >> selects >> in sysprocesses, close connection). >> A strange thing is that if I let this small program run using SqlClient >> (with or without pooling) it runs the normal way, but if I use ODBC (and >> a >> DSN) the connections are not closed immediately (after x minutes) and new >> connections are sometimes opened (not every time) on next heartbeat. >> >> Digging in ... the bits. >> >> Best regards, >> >> Pascal > >
Russel,
Indeed we saw the KB 834849 and we also think we are not concerned. BTW we ran the InstCat script in order to be sure because this KB talks about ODBC issues and I think it is one of the tracks. We also found ODBC connections with blank UID and password which is considered as a trusted connection but it seems this could be a track because we saw a different reaction when explicitely requesting trusted connection. It remains that I don't know if this is actually related to the 8525 error, but it is sure that having too much connections (and unused) may be a source of problem. Today is a day off in Belgium but we will look at your other suggestions tomorrow. Once again, thank you for your time and advices. Regards,
Bill,
I agree and I never liked Access anyway (and the SQL Profiler shows some of the reasons for this). I also never considered it as a development tool or even a professional tool (a private or proof-of-concept toy) even if you will mention it on my CV 10 or 15 years ago. But as a consultant or a developper (or both) you are not always able to choose the environment the customer works with. And even if you have a customer telling you the magic sentence "I want it to work, do as you want" you will still face: 1) the existing systems you must live with (think to Cobol and year 2000!) 2) we may still make wrong choices we are aware of years later (mea cupla for some old projects) 3) you must sometimes "make it work" as it is right now without the possibility to re-write anything, and it is the case for this issue (tomorrow is the last day I will be able to work on this issue) 4) sometimes people that do not have the correct knowledge in IT or computer science take decisions because they tried this or this tool at home. Let's all try to make it the best way possible for the project, for the customer, for the future and ... at last, for us. Thanks anyway for your remarks Pascal
Russel,
Ok, Friday we found linked tables having a hardcoded workstation ID which explains the "ghost" connections that are not ghost anymore. This eliminates the track but we don't know if we still face the problem of error 8525 because the server is rebooted every night (maybe the InstCat of KB 834849 solved the problem). I supplied my problem report but I failed my role of problem solver. Monday I will start a new project (as analyst - developper). I asked Steven who is in charge of the problem to keep me informed. I wrote an application that monitors the transactions (syslockinfo and sysprocesses similar to DBCC results) and writes traces when a transaction exceeds 10 minutes (because problem occurs after 15 minutes). I hope this small tool will help Steven. Thanks and bye,
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |