'Optimised for 8 Concurrent Users' - What does this mean?

[papic1972]

Hi all,

I'm fairly new in the world of SQL Server 2000. My setup is a SQL Server backend on a server & at any given time can have up to 10 users using an Access 2003 Frontend.

I have been checking my error logs & have received the following error msg:

'This SQL Server has been optimized for 8 concurrent queries. This limit has been exceeded by 1 queries and performance may be adversely affected.'

What does this actually mean? Am i doing something wrong? Can I increase this optimisation to more queries? Will I be causing problems in doing so?

Any help would be fantastic!
Thanks.

 

[unclerico]

does this application use MSDE or SQL Server as the backend??

 

[Catadmin]

Do you actually use Access as a front end or an Access .ADP?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[SQLBill]

It means your SQL Server is setup to only allow 8 queries at one time (that's not USERS, that's queries. One user can create several queries).

As asked previously, which version are you using?

-SQLBill

Posting advice: FAQ481-4875

 

[mrdenny]

Since the user hasn't responsed I would assume that it's MSDE and that he's running up against the similutious user limit that MSDE has.

You'll want to upgrade to full blown SQL Server Workgroup/Standard/Enterprise Editions, or SQL Server 2005 Express Edition.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[papic1972]

Hi,

Sorry, I've been offline for the past couple days.

The backend is SQL Server 2000.
The frontend is Access, NOT ADP.

Is the '8 queries' limit a default setting in SQL.
I need to know is there a way to adjust this to more queries without causing future problems to my database.

 

[mrdenny]

The 8 query limit should only come in MSDE, not full blown SQL Server.

Log into the SQL Server and run:

SELECT 'SQL Server '
+ CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - '
+ CAST(SERVERPROPERTY('productlevel') AS VARCHAR) + ' ('
+ CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')

Please post back the results.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[papic1972]

Hi MrDenny,

I'm a newbie to SQL. Where do I run your code from?

 

[Catadmin]

You run any T-SQL code from the Query Analyzer client tool. You should be able to find it under the Start-Programs-Microsoft SQL Server folder.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[papic1972]

OK, I tried that & get the following error:

Server: Msg 105, Level 15, State 1, Line 4
Unclosed quotation mark before the character string ')

'.
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near ')

'.

What do I do now?

 

[SQLBill]

You don't need all the converts, try this:

SELECT SERVERPROPERTY('productversion'),
       SERVERPROPERTY('productlevel'),
       SERVERPROPERTY('edition')

-SQLBill

Posting advice: FAQ481-4875

 

[papic1972]

OK,

That's better, it outputted the following:


8.00.194 RTM Personal Edition


So, is there an issue with increasing the number of queries?

 

[Catadmin]

Personal Edition is a one person only version of SQL Server. You need Standard or Enterprise if you want more concurrent queries than 8.

Sorry, there's no fix other than to get an different version.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[SQLBill]

Plus you have the 'out of the box' version (194). You should download and install the latest service packs. At least get SP3a installed. There are severe security issues with pre-SP3a versions (SLAMMER WORM).

-SQLBill

Posting advice: FAQ481-4875

 

[Leonos]

When this message occurs (optimized for 8 concurrent etc.), does the 9th query fail, or will this query wait for one query to be closed ?
Or does it run and take some more CPU ?

 

[Catadmin]

The 9th query should fail because it can't get a connection.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Leonos]

Does this limitation also count for an update statment.
Thus an update statment is count as a concurrent query ?

Is do get timeouts when I have reached this limit, but when another query has finished, the waiting query / update will succeed.

 

[mrdenny]

Yes.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com