Connection Issues in SQL Server 7.0 sp3

[cutiger93]

We are having a problem connecting to SQL Server when the user connections exceeds 20 or 21. This has happened on two separate occasions.

I changed the max worker threads to 20 from 255 to attempt to improve performance. Everything was working great until we rebooted the server. After a few hours from the reboot we began to experience connection problems. We were unable to connect in any way. The SQLServer:General Statistics\User Connections would read 20 for ten minutes. This was the time period that we were not able to connect. I stopped and started the MSSQL Service and it seemed to resolve the issue. I upped the max worker threads to the default 255 and we did not have any problems for 2 days. Then when the SQLServer:General Statistics\User reached 21 and it seemed to create the same problem. No connections. It would time out whether on a remote client or at the server. I am not sure why 21 was the magic number because SQLServer:General Statistics\User was 23 two minutes before the problem. I stopped and started the MSSQL Server and this has seeming resolved the issue once more.


Any ideas what the problem could be. thanks for your help.

Does anyone know any good scripts that they are using to track performance issues?

 

[tlbroadbent]

Do you get any error messages when connecting? If you try to connect with Query Analyzer while this problem occurs, do you get a message or are you able to connect?

What kind of licensing do you have - Server or Client? How many glient licenses are assigned to the server?

Worker threads really shouldn't affect connectivity. We've never had reason to change worker threads and we have hundreds of users. Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[cutiger93]

Thanks Terry.

I wish I could answer some of these questions for you, but unfortunately I can not. I did not copy the error that I was getting which I know is a not good. We stopped and started the server and everything seems to be working great now. I will continue to monitor and will post something is things go down hill.