SQL Server Performance

Database connections exceeded?

Discussion in 'Performance Tuning for DBAs' started by Krystoff, Mar 24, 2003.

  1. Krystoff New Member

    Hello all, I have a pretty unique problem and I am not sure it can be fixed. I have SQL Server 2000 personal edition. I have around 15 - 25 people that need to connect to the database at any one time.

    To connect, they use Access 2000 projects. However, I can not get more than 13 people in at one time. It gives me an "No Such Interface Supported" error.

    Now, I know that Personal edition isn't supposed to be used with a lot of users but the workload governer is related to Batch Processes (I really don't know what a batch process is but I know I don't use more than the limit of 5. I have checked with DBCC Concurrency in Query Analyzer). I have checked my max connections and it says approx 32,000.

    Is there any way I can get more people connected to the database? Any help is appreciated!!

    Chris

    P.S I don't have the number of "Seats" or license model that I have. If you know a way to check in the program please let me know!
    Krystoff, Mar 24, 2003
    #2

  2. satya Moderator

    Check the MAX WORKER THREADS option value from SP_CONFIGURE.

    _________
    Satya SKJ
    satya, Mar 24, 2003
    #2

  3. Krystoff New Member

    The Max Worker Threads is set at 255.
    Krystoff, Mar 24, 2003
    #2

  4. Chappy New Member

    My understanding is that the concurrency limitations in personal server are not hard limits which will fail when exceeded, but will decrease performance proportionally once the limit of 5 is exceeded.

    You say you havent exceeded 5 batches at all, by the time the connections fail, which could suggest the problem lies elsewhere. When you get the problem, are you able to connect via query analyser ?
    Also run a simple server trace for login and logout audits, to get a better understanding of the state of your server when it goes belly up. To decrease chance of concurrency limit being exceeded, you should close connections as soon as you are finished with them to save resources.

    Either way, I'd seriously consider moving up to standard edition if its an option.
    Chappy, Mar 24, 2003
    #2

  5. bradmcgehee New Member

    I agree with Chappy. The personal edition is not designed for many users at once (in other words, this edition has been intentionally crippled to prevent heavy use). If you need 15+ users to connect, you need to move up to the Standard Edition.

    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
    bradmcgehee, Mar 25, 2003
    #2

  6. barozai New Member

    hi..
    I also agree with chapy...
    you need Enterprise Edition.....(if affordable)
    Caz I had been using personal edition and had the same problem....

    Aman
    barozai, Mar 26, 2003
    #2

Share This Page