SQL Server Performance

Supious log-ons are causing performance issues

Discussion in 'Performance Tuning for DBAs' started by mrwizard10, Dec 19, 2002.

  1. mrwizard10 New Member

    I am remotely administering a database in an east coast office. It has uses a MSSQL7 database supporting a ColdFusion interface for our users. I noticed on the WinNT4.0 security log in Event Viewer that 4-5 machines at our partners site attempting and failing at log ons to that machine, on the order of every 1-4 seconds. When we alerted our partners of this fact, they investigated and found that the all of those machines had active user connections through Enterprise Manager with the SQL server connected. They have since closed those connections, with the exception of one and that issue has ended. My question is, what happens when one of their other users tries to restablish the connection? Is there a setting or toggle that needs to be changed to prevent that? [xx(][?]
  2. bradmcgehee New Member

    I am a little confused. Are you saying that all the logon attempts you saw in the log failed, but that you were told they were from Enterprise Manger connections? This is confusing because of a log on fails, then they cannot connect to SQL Server. So I guess I need more info.

    If a user does not have a SQL Server login, they can't access SQL Server, and they are refused a connection (assuming security is set up properly. If a user has a SQL Server login, they can connect to SQL Server, but they can't access any user databases unless they also have a database login ID.

    So once you give us a little more info, we will try to offer some useful advice.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  3. satya Moderator

    First thing you need to assure the timeout settings on server end.
    And similar net libraries should be used at client and server. So flash the exact error when they're attempting connection to the server, this will give link to solve the issue.

    HTH

    Satya SKJ
  4. mrwizard10 New Member

    More informatiom.<br /><br />In talking with our partner, the "offending machines" had Enterprise manager up on each of them with a connection using the sa user name and password to the machine. While that connction was being maintained, each of those machines were also attempting to log onto the server that the MSSQL application was running on. When they severed the connection in Enterprise Manager, the log ons stopped. Some other things, there are some of their machines at their site that are also logged in, but do not attempt the repeated log ons. Also, I tried duplicating this over my WAN, without any success.<br /><br />Hope that sparks any thoughts.<br /><br />Thanks<br /><br />[<img src='/community/emoticons/emotion-2.gif' alt=':D' />]
  5. bradmcgehee New Member

    I have a guess, but it may not be correct. If you have Enterprise Manager running on a desktop or server, and it has one or more registered SQL Servers, it will try to maintain contact with them. Even if the connection is dropped, it will try to reconnect. My guess is that perhaps there is some cause for these Enterprise Managers to disconnect and reconnect over and over, such as a bad network connection due to a bad card, bad driver, etc. One way to find out if this is the case is to do a Profiler Trace of the server in question, and see exactly what is happening.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  6. satya Moderator

    Just a thought most of the questions coming up are totally related to performance related and the first thing you need to keep up is using PROFILER and PERFMON.

    Satya SKJ

Share This Page