Number of Concurrent users

[Krystoff]

Hello all,

I am testing out an Access Project hooked to a SQL Server 2000 Database. So far, everything works great except for the number of concurrent users. It gets locked out when I get somewhere around 12. The version of SQL server is Personal Edition and I am using Access 2000.

I don't really have very users setup (I don't think) but Windows 2000 is veryifying all the users correctly and letting them login. I just need to have around 20 - 25 concurrent users.

Any help? Do you need more information? Whats going on?

Any help is appreciated!

Chris

 

[tlbroadbent]

Personal Edition is not meant to be used as a departmental database. Read the documentation on Personal Edition.

Both the SQL Server 2000 Personal Edition and SQL Server 2000 Desktop Engine have a concurrent workload governor that limits the performance of the database engine when more than 5 batches are executed concurrently. For more information about the concurrent workload governor, see SQL Server 2000 Databases on the Desktop. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Krystoff]

Terry,

Thanks for the quick response.

I read up on the SQL Server 2000 Databases on the Desktop but since I am new, I don't really know what a batch is. It also says I will just see degraded performance as I go above the limit but I don't see any performance loss (Visible at least) when 10 users are using the database. Its just when I try to add the 11, 12, or 13th person the Access Project hangs and gives me a No Such Interface Supported error message and doesn't connect.

Ill read more on DBCC CONCURRENCYVIOLATION as well.

Chris

 

[Krystoff]

Terry,

Just another quick note. I checked the DBCC CONCURRENCYVIOlATION Statement and it came back that I haven't gone over the limit on Batch statements.

Is there something else that limits the number of users that are allowed to logon? Like number of users allowed to use the guest account? Could that be a problem?

Chris

 

[Krystoff]

What I mean by hooked is that it is an Access Project (.ADP) It is a new feature for Access 2000 (New then anyways) that allows it to connect directly to a SQL Server or MSDE Database. In the Project, you can directly manipulate Tables, Views, and Stored Procedures. The Forms, Macros, Pages, Reports and Modules are all on the Access Project. So every time they open the Access Project, I can look in the current activity and see them logged onto the server with 2 process ID's and depending on what they are doing, 3 process ID's.

Does that help?

 

[tlbroadbent]

Execute sp_configure to determine the maximum number of connections configured in SQL Server. If the maximum is less than the desired number, then use sp_configure to increase the number.

set nocount on
exec sp_configure "show advanced options",1
reconfigure with override
--set number of connections to 50
exec sp_configure "user connections",50
--display setting
exec sp_configure "user connections" If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[SemperFiDownUnda]

Could it be possible that you are seeing a OS Level connection limit?