Warning: OPEN OBJECTS parameter may be too low.
-
Any help will be highly appreciated.
-
More info
It is a Win 2000 server with SQL Server 2000 SP3a, 2GB RAM. Dynamic allocated memory.
I am getting also messages at the crush time
2004-06-21 09:59:11.90 logon Login failed for user 'nsain'.
2004-06-21 10:00:30.70 spid10 Warning: OPEN OBJECTS parameter may be too low.
2004-06-21 10:00:30.70 spid10 Run sp_configure to increase the parameter value.
2004-06-21 10:00:30.84 spid10 Warning: OPEN OBJECTS parameter may be too low.
2004-06-21 10:00:30.84 spid10 Run sp_configure to increase the parameter value.
2004-06-21 10:01:45.14 spid14 Warning: OPEN OBJECTS parameter may be too low.
2004-06-21 10:01:45.14 spid14 Run sp_configure to increase the parameter value.
2004-06-21 10:03:10.48 spid54 Warning: OPEN OBJECTS parameter may be too low.
2004-06-21 10:03:10.48 spid54 Run sp_configure to increase the parameter value.
2004-06-21 10:03:12.53 spid3 Error: 701, Severity: 17, State: 127
2004-06-21 10:03:12.53 spid3 There is insufficient system memory to run this query..
also at the boot I am getting a
2004-06-21 09:38:01.48 server initdata: Warning: Could not set working set size to 834720 KB.
-
In order to get rid of "Open Objects" warnings, you can execute the sp_configure system
procedure with the following syntax:
exec sp_configure "open objects", <new value>
RECONFIGURE, shut down and restart SQL Server. Each open object takes up memory resources, so increase it in relatively small chunks (500) until the message stops appearing in the error log. Be careful - each "open object" requires approximately 70 bytes of memory, so by increasing the value of "open objects" by 1000, you will lose approximately 69K of memory.
It would be a good idea to investigate the reason for the warnings though before fixing them. Try running dbcc des - it will give you an idea about currently open objects and possible memory leaks.
dbcc traceon(3604)
go
dbcc des
go
Another way to check for the error source is to check your tempdb for # of objects there. If the # is rather large and you are using ODBC, uncheck the "Generate stored procedure for prepared statement" option ( it might create a lot of temp objects).
Also, if you have multiple copies of EM open, it will increase the # of open objects.
As for "working set size" error, verify that "Reserve physical memory for SQL Server" is unchecked ( Server >>Properties>>Memory tab).
Hope it helps...
-
I am working with Microsoft Support to solve the issue.
I don't want to hard code the number of open objects since that number should ajust itself dynamically.
The temp database is really small < 20 MB so I don't think is not an issue on the number of open objects, more of memory not getting allocated to SQL Server.
I will keep you posted.
-
Have you enougth swap?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply