Re: Strange SQL 2000 connections ! (ghost connections)
- From: "William \(Bill\) Vaughn" <billvaRemoveThis@xxxxxxxxx>
- Date: Wed, 16 May 2007 16:15:55 -0400
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)
-----------------------------------------------------------------------------------------------------------------------
"Russell Fields" <russellfields@xxxxxxxxxx> wrote in message
news:uY%23EFL%23lHHA.3484@xxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:DFC661E4-2552-4436-95AB-005BAFF053D2@xxxxxxxxxxxxxxxx
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
.
- References:
- Re: Strange SQL 2000 connections ! (ghost connections)
- From: Russell Fields
- Re: Strange SQL 2000 connections ! (ghost connections)
- From: Russell Fields
- Re: Strange SQL 2000 connections ! (ghost connections)
- Prev by Date: Re: Strange SQL 2000 connections ! (ghost connections)
- Next by Date: Re: problem in connection with SQLServer using DSN
- Previous by thread: Re: Strange SQL 2000 connections ! (ghost connections)
- Index(es):
Relevant Pages
|
