Prob: NO NEW CONNECTIONS ALLOWED

Question

Post reply

Prob: NO NEW CONNECTIONS ALLOWED

Torin

SSChasing Mays

Points: 635
More actions
September 29, 2003 at 10:44 am

#83215

SQL 6.5
New machine build on NT4
dual 2.6 GHz processors
2.5 GB ram
Prob: about 30-50 users connect, then any new attempts just hour glass forever.
This is a rebuild from other machines. Configuration setting appear all correct. (User connections set to 330).
Any ideas?
Thanls

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47794 More actions · Answer 1

How are the users connecting to the database?

I've seen similar messages when using a proprietary ODBC driver. We had to purchase additional licences.

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

Torin SSChasing Mays Points: 635 More actions · Answer 2

We appear to have found the problem - in spite of the published information from MS stating that 6.5 can address up to GB of memory, it doesn't. I finally tried dropping the memory config from 1000000 blocks to 256000 and the mysterious problem went away.

I just now found a document buried at MS (found it with google) that states that the use of more that 1.5Gb with 6.5 can have unpredicateble results. Really?

To answer your question, the clients connect using TCP/IP.

Thanks

Torin

philcart SSC-Forever Points: 47794 More actions · Answer 3

Memory config in 6.5 is one of those black arts. You need to account for memory usage of connections, locks, etc...

I'm not too sure about the limit or instability though. The last time I had a database running on 6.5 it was 40GB of data on a quad processor server with 2gb Ram. From memory I think SQL chewed up about 1.8gb of that. The only problem I had was with one table that was 10gb in size.

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

Torin SSChasing Mays Points: 635 More actions · Answer 4

Torin

SSChasing Mays

Points: 635

September 30, 2003 at 9:48 am

#476400

What problem did you have with the 10GB table?

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 5

quote:
We appear to have found the problem - in spite of the published information from MS stating that 6.5 can address up to GB of memory, it doesn't. I finally tried dropping the memory config from 1000000 blocks to 256000 and the mysterious problem went away.

SQL Server 6.5 has 1 block = 2kb. If you configured it to use 1000000 blocks, then that's approx 2GB RAM, leaving very little space for any other client applications to run, let alone the OS stuff. (Windows partitions memory as 2GB for client appls, 2GB for system.)

Thomas Rushton
blog: https://thelonedba.wordpress.com

philcart SSC-Forever Points: 47794 More actions · Answer 6

quote:
What problem did you have with the 10GB table?

Try running a reindex and have it complete any time soon

The database was from a legacy system which had a very, very bad structure. That table had an average of 25,000+ inserts and 15,000+ deletes per week and no ability to archive out any data. Given that SQL 6.5 only has 2k pages we were only able to fit 1 or 2 records per data page for that table. Lot of fiddling with the fill factor.

If we didn't do fortnightly re-indexing the performance dropped through the floor.

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

philcart SSC-Forever Points: 47794 More actions · Answer 7

quote:
quote:
We appear to have found the problem - in spite of the published information from MS stating that 6.5 can address up to GB of memory, it doesn't. I finally tried dropping the memory config from 1000000 blocks to 256000 and the mysterious problem went away.
SQL Server 6.5 has 1 block = 2kb. If you configured it to use 1000000 blocks, then that's approx 2GB RAM, leaving very little space for any other client applications to run, let alone the OS stuff. (Windows partitions memory as 2GB for client appls, 2GB for system.)

Ahhh ... yes it all comes back now ... the delights of configuring SQL 6.5 ... no mama ... I'll be good ... no you don't need to lock me in my room again ... I promise I'll be good ...

Thomas is right, if you drop the number of blocks down at bit and give the OS room to breath, you might find things run a bit better.

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 8

quote:
Ahhh ... yes it all comes back now ... the delights of configuring SQL 6.5 ... no mama ... I'll be good ... no you don't need to lock me in my room again ... I promise I'll be good ...

I've still got a few of 'em to play with. Three of our production servers are SQL6.5; the remainder are, thankfully, SQL2000, but we still have a couple of SQL7 boxes around somewhere.

Thomas Rushton
blog: https://thelonedba.wordpress.com

Torin SSChasing Mays Points: 635 More actions · Answer 9

the machine has 2.5GB memory. By using 1000000 blocks for SQL, that left around 600MB for NT - more than enough. Unless NT can't address more than 2GB?

I've got SQL at 1.25GB now. With no issues. There it will stay.

Configuring SQL 6.5 is easy compared to 4.21