Audit Logon / Audit Logoff problem with SQL 2K

[Oscar Santiesteban Jr.]

I need help...here is the problem.

Last weekend, the servers in our datacenter where moved around. After this
move, and maybe coincidental, 1 server is performing very poor. After
running a trace with SQL Profiler, I saw the problem which was later
confirmed with another tool for SQL server performance monitoring. It seems
that all connections to the SQL server (between 200 - 400) are doing a login
/ logout for each command that they process. For example, the user's
connection will login, perform a SELECT, and then logout. This is not a
..NET application. The client software was not changed, it is still the
same. The vendor has said that it is not supposed to do that, it is
supposed to use 1 connection that log's on in the morning and logs off at
the end of the day or whenever the user exits. 1 user may have several
connections to the database.

At times, the server is processing over 250 login / logouts (avgeraged for
30 second period). Has anyone seen this problem? I have the server in
AUDIT FAILUREs only. The server has become very unresponsive, things that
took 3 seconds now take over 15 seconds.

Any ideas???

[Jack Vamvas]

What connection type is used , is it ODBC or DSN Less?
Also, which version of SQL server are you using?


--
Jack Vamvas
__________________________________________________ ________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"Oscar Santiesteban Jr." <o_************@bellsouth.net> wrote in message
news:xm*****************@bignews2.bellsouth.net...

I need help...here is the problem.

Last weekend, the servers in our datacenter where moved around. After this move, and maybe coincidental, 1 server is performing very poor. After
running a trace with SQL Profiler, I saw the problem which was later
confirmed with another tool for SQL server performance monitoring. It seems that all connections to the SQL server (between 200 - 400) are doing a login / logout for each command that they process. For example, the user's
connection will login, perform a SELECT, and then logout. This is not a
.NET application. The client software was not changed, it is still the
same. The vendor has said that it is not supposed to do that, it is
supposed to use 1 connection that log's on in the morning and logs off at
the end of the day or whenever the user exits. 1 user may have several
connections to the database.

At times, the server is processing over 250 login / logouts (avgeraged for
30 second period). Has anyone seen this problem? I have the server in
AUDIT FAILUREs only. The server has become very unresponsive, things that
took 3 seconds now take over 15 seconds.

Any ideas???

[Erland Sommarskog]

Oscar Santiesteban Jr. (o_************@bellsouth.net) writes:

Last weekend, the servers in our datacenter where moved around. After
this move, and maybe coincidental, 1 server is performing very poor.
After running a trace with SQL Profiler, I saw the problem which was
later confirmed with another tool for SQL server performance monitoring.
It seems that all connections to the SQL server (between 200 - 400) are
doing a login / logout for each command that they process. For example,
the user's connection will login, perform a SELECT, and then logout.
This is not a .NET application. The client software was not changed, it
is still the same. The vendor has said that it is not supposed to do
that, it is supposed to use 1 connection that log's on in the morning
and logs off at the end of the day or whenever the user exits. 1 user
may have several connections to the database.

It sounds as if connection pooling was turned off. Most applications
these days - and it does not have to be .Net - works with the paradigm
that they connect, run a query and then disconnect. Under the covers,
the client API maintains a connection pool, which means that a logical
disconnect is not directly a physical, but if there is a reconnection
within 60 seconds, the connection will be reused.

But this is stricly a client-side feature, so moving a server should not
cause this, but there has to be some change on the client side for
connection pooling to be ditched.

An other alternative is that SQL Server would itself close the connection,
but this only happens on error; SQL Server does not have any inactivity
monitor.

That leaves the network. The network could be configured to drop the
connection when nothing have happned for n seconds. But in such case,
I would expect the client to report errors, as most clients are not
prepare for this form of brutal disconnection. Still I would investigate
networks and firewalls.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Oscar Santiesteban Jr.]

This application is VB (classic) with a DAO type of technology. The
database is an MS Access database with all tables being a "linked table"
that points to MS SQL Server 2K database. Vendor says they will not move to
ADO or OLEDB, too expensive.
"Jack Vamvas" <in**@nospam.com> wrote in message
news:dq**********@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...

What connection type is used , is it ODBC or DSN Less?
Also, which version of SQL server are you using?


--
Jack Vamvas
__________________________________________________ ________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp

[Oscar Santiesteban Jr.]

Thanks Erland for the comments. Here is some follow up.
The "Audit Logoff" seems to come from the client. We have monitored all
connections using ETHERReal and the logoff comes from the IP address.

Vendor claims that our data center move caused this. Another thing is that
previously, the NIC of the server (10/100) was hooked up to a switch, which
then went to a CISCO 6509 switch. The server is now connected at 1000
(gigabit) directly to the server. Don't know if that changes anything.
Vendor claims that some hardware (switch/router/etc...) is flaky.

We have enen removed the most recent MS patch, installed by our SMS server,
and things are still the same. This server has over 300 connections daily
and at times of high use, it bogs down with excessive login/logoffs.

It sounds as if connection pooling was turned off. Most applications
these days - and it does not have to be .Net - works with the paradigm
that they connect, run a query and then disconnect. Under the covers,
the client API maintains a connection pool, which means that a logical
disconnect is not directly a physical, but if there is a reconnection
within 60 seconds, the connection will be reused.

But this is stricly a client-side feature, so moving a server should not
cause this, but there has to be some change on the client side for
connection pooling to be ditched.

An other alternative is that SQL Server would itself close the connection,
but this only happens on error; SQL Server does not have any inactivity
monitor.

That leaves the network. The network could be configured to drop the
connection when nothing have happned for n seconds. But in such case,
I would expect the client to report errors, as most clients are not
prepare for this form of brutal disconnection. Still I would investigate
networks and firewalls.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

Oscar Santiesteban Jr. (o_************@bellsouth.net) writes:

Thanks Erland for the comments. Here is some follow up.
The "Audit Logoff" seems to come from the client. We have monitored all
connections using ETHERReal and the logoff comes from the IP address.

I don't know how you conclude this, but at least in Profiler you will
not be able to tell who disconnected whom.

Anyway, in another post you said that the database is Access which is
using linked tables. Do each user have it's own Access database? Or do
they connect to a central Access database which then connects to SQL
Server?

Not that it may matter that much. I know very little about Access, but
my guess is that when Access access SQL Server it's still done in the
context of the user.

What you should try is to set up Access with linked tables to some
other server where you don't have this problem. First verify that
connection pooling works, that is you don't see constant logoffs. Then
sever the connection like pulling out the network cable and put it
back again or similar. If Access handles this gracefully, that is you
don't get an error message, then there is more reason to suspect that
you have a network problem.

After all, since what appears to have changed at your site is the
configuration of server and network, so it's natural to look for
the cause there.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[pb648174]

We had this problem when we moved ISPs, it was a switch setting, as to
what speed it was set for and if it was set to "Autodetect" or
something like that... We ended up configuring the Ethernet connection
through TCP/IP settings to be the ISP switch setting (10MB/s) I believe
and that fixed it.