473,847 Members | 1,756 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Audit Logon / Audit Logoff problem with SQL 2K

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???
Jan 18 '06 #1
6 10888
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******** *********@bigne ws2.bellsouth.n et...
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???

Jan 18 '06 #2
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****@sommarsk og.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
Jan 18 '06 #3
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.co m> 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

Jan 19 '06 #4
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****@sommarsk og.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

Jan 19 '06 #5
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****@sommarsk og.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
Jan 19 '06 #6
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.

Jan 23 '06 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
293
by: Rod Kestler | last post by:
After applying the KB Automatic Update #833987 (JPEG Security Issue), our development server suddenly will not allow any IIS 6.0 App Pool running with identity set to ASPNET to run. The Event Viewer shows the following trace from the Application Log: Event Type: Warning Event Source: W3SVC Event Category: None Event ID: 1057
1
2984
by: Jonas | last post by:
Hi! I'm building an ASP.NET web application that needs an automatic logoff after a specified time out period, forcing the user to enter his/her Windows authentication credentials again. I use Windows authentication and IIS is set to use Basic Authentication as this is going to be used in an extranet/internet environment with IE as supported browser. Can I from server code logoff a user from the Windows domain, or do I have to...
2
3226
by: Raimund Taschler | last post by:
Hi all, I'm implementing a windows service (running with local system account) which has to do some tasks when users logon/logoff. How can the service register to those logon/logoff-events? I'm using .NET 1.1 and C# and the service will be installed on machines with os >= W2K. If you have any solution or hint tracking down this problem please let me know!
0
2535
by: gewe | last post by:
Can anyone tell me what is wrong with the following code? The code has to watch for logon/logoff events: public void StartListenLogon() { EventHandler handler = new EventHandler(); WqlEventQuery query = new WqlEventQuery(); query.EventClassName = "__InstanceOperationEvent"; query.WithinInterval = new TimeSpan(0, 0, 3);
2
1668
by: Robert Scheer | last post by:
Hi. I need to write a service that writes to a database the time of the logon and logoff of the users. Our users logs on Active Directory and some can also log as local administrators. The majority of the workstations are Windows 2000 and some are Windows XP. I am reading about WMI but have found some limitations when reading the WMI structures, mainly with Windows 2000. What is the best way to read logon/logoff information in this...
1
4146
by: dotyet | last post by:
Hi Everyone, I am looking at a lot of entries similar to the following in my Windows 'Audit' Event viewer. They are all success events and not failures. The environment is: DB2 8.2 FP 14 Windows 2003 x64
1
2730
by: =?Utf-8?B?ZGpjYXNlbGV5?= | last post by:
I'm trying to write a service that will sit on the domain controller (or, if this proves to be impossible, to be rolled to all connecting Windows machines). It needs to get notification of each logon and logoff, capturing username & IP address. I've currently got a test application to check the inputs & outputs and the business logic is using EventLog_OnEntryWritten to trap 538's & 540's. Unfortunately (and peculiarly) a physical logon...
0
1629
by: questionit | last post by:
Hi 1) I have installed Ms Exchange Enterprise Edition. Since then Windows LogOff and Shutdown has become too slow. Installed the same Exchange server on another Machine, that too has become too slow on shutdown and logoff. Why has it become too slow, any idea. Is it normal or could it be fixed? 2) I can logon to Ms Exchange only with Domain Administrator Account, with all other accounts, on entering user ID and password, i get the...
2
1864
by: Dan2kx | last post by:
Function LogonLog(OnOff As String) Dim rst As DAO.Recordset If OnOff = "On" Then Set rst = CurrentDb.OpenRecordset("tblLogonLog", dbOpenDynaset, dbAppendOnly) rst.AddNew rst!StaffID = LogStaffID rst!Logon = Now() rst.Update ElseIf OnOff = "Off" Then Set rst = CurrentDb.OpenRecordset("SELECT Max(tblLogonLog.LID) AS LID FROM tblLogonLog HAVING (((tblLogonLog.StaffID)=1) AND ((tblLogonLog.LogOff)...
0
9887
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10983
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10707
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10341
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7883
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7056
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5911
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4529
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3164
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.