catlantis
asked on
SQL server ent 2000 AWE setup win2003 ent 32bit
Hello all,
We are having troubles getting the SQL 2000 to address more memory.
The 2 environments are both win2003 enterprise 32 bit running sql 2000 enterprise (both have sp4, one has the post sp4 hotpatches).
OS has 12 gb and /PAE and /3GB are set. And there is ample memory available for SQL at start up time.
SQL config settings:
min server memory (MB) = 3084
max server memory (MB) = 3084 (would like to bump this up in both cases).
set working set size = 0 (required by vendor)
awe enabled = 1
The service account is running with the "Lock Page in Memory" permission.
And there is ample memory.
Systems have been restarted multiple times.
But we always get the error
"Warning: unable to allocate 'min server memory' of 3084MB."
And SQL server appears to dynamically allocate memory up to about 2.7GB.
The sql error log never reads "Address Windowing Extension enabled"
Nor is there an error telling us it cannot use extended memory.
I am clearly missing a step here, but not sure what.
Any ideas?
Thanks for your help.
(The good news is the business finally decided to go to a current 64 bit environment, the bad is that is it is not for another 6 months).
We are having troubles getting the SQL 2000 to address more memory.
The 2 environments are both win2003 enterprise 32 bit running sql 2000 enterprise (both have sp4, one has the post sp4 hotpatches).
OS has 12 gb and /PAE and /3GB are set. And there is ample memory available for SQL at start up time.
SQL config settings:
min server memory (MB) = 3084
max server memory (MB) = 3084 (would like to bump this up in both cases).
set working set size = 0 (required by vendor)
awe enabled = 1
The service account is running with the "Lock Page in Memory" permission.
And there is ample memory.
Systems have been restarted multiple times.
But we always get the error
"Warning: unable to allocate 'min server memory' of 3084MB."
And SQL server appears to dynamically allocate memory up to about 2.7GB.
The sql error log never reads "Address Windowing Extension enabled"
Nor is there an error telling us it cannot use extended memory.
I am clearly missing a step here, but not sure what.
Any ideas?
Thanks for your help.
(The good news is the business finally decided to go to a current 64 bit environment, the bad is that is it is not for another 6 months).
ASKER
Hi Daniel.
I did apply this to one of the servers in question.
Also I applied
http://support.microsoft.com/kb/916287
"A cumulative hotfix package is available for SQL Server 2000 Service Pack 4 build 2187"
Maybe one over wrote the other? I will see if I can appy 899761 again
thanks
I did apply this to one of the servers in question.
Also I applied
http://support.microsoft.com/kb/916287
"A cumulative hotfix package is available for SQL Server 2000 Service Pack 4 build 2187"
Maybe one over wrote the other? I will see if I can appy 899761 again
thanks
ASKER
Yup, re-tried the patch from KB899761
Still doing the same thing, not allocating.
Although I am not asking for more than 1/2 the available memory, so the kb should not apply.
Still doing the same thing, not allocating.
Although I am not asking for more than 1/2 the available memory, so the kb should not apply.
Can you double check that your operating system can see all RAM?
By running following quey in cmd you can chceck whether /3GB is working:
Run following and confirm that you have set awe enabled and did not set Set working set size option.
By running following quey in cmd you can chceck whether /3GB is working:
wmic /node:<server_name> OS get MaxProcessMemorySize
It should be higher than 2097024 (KB).Run following and confirm that you have set awe enabled and did not set Set working set size option.
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure
GO
sp_configure 'show advanced options', 0
RECONFIGURE
GO
Please also check whether you have set startup parameters, especially -g.
ASKER
HI Daniel,
Thanks for your message.
The value I get for my test box is: 3145600
The sql values are :
awe enabled 0 1 1 1
set working set size 0 1 0 0
So awe set, and set working size is not. (last value column is run_value)
We are not running any start up parameters other than the standard ones for the master db and error logs.
I am not sure about setting a parameter on our prd box
We are running SAP on top of this. And they say:
"SAP does not require or generally recommend to modify any SQL Server startup parameter."
I will certainly try the option in our QAS box. If that works, will get confirmation from SAP on setting it on PRD (although they have been extremely unhelpful on this topic :) )
Thanks!
Chris
Thanks for your message.
The value I get for my test box is: 3145600
The sql values are :
awe enabled 0 1 1 1
set working set size 0 1 0 0
So awe set, and set working size is not. (last value column is run_value)
We are not running any start up parameters other than the standard ones for the master db and error logs.
I am not sure about setting a parameter on our prd box
We are running SAP on top of this. And they say:
"SAP does not require or generally recommend to modify any SQL Server startup parameter."
I will certainly try the option in our QAS box. If that works, will get confirmation from SAP on setting it on PRD (although they have been extremely unhelpful on this topic :) )
Thanks!
Chris
Don't set set working set and startup options. I wanted you to confirm it is turned off.
Please verify options with this guide on options:
http://www.sql-server-performance.com/sql_server_performance_audit5.asp
Please verify options with this guide on options:
http://www.sql-server-performance.com/sql_server_performance_audit5.asp
ASKER
I have not modified anything yet. (But I do have a qa box to tinker with if needed).
We have the settings as indicated set for years.
Settings determined by max memory size speced by SAP:
"min server memory (MB)" = "max server memory (MB)" = 3084
"set working set size" = 0
"awe enabled" = 1
Deviations from default installation as directed by SAP:
"network packet size" = 8192
"priority boost" = 0
"max degree of parallelism" = 1 (recommends setting this dynamically on when aggregates occur, off when oltp users come online)
"max worker threads" = 255
Kept as default as recommended by SAP:
"affinity mask" = 0
"c2 audit mode" = 0
"cost threshold for parallelism" = 5
"cursor threshold" = -1
"default full-text language" = 1033
"default language" = 0
"fill factor (%)" = 0
"index create memory (KB)" = 0
"lightweight pooling" = 0
"locks" = 0
"max text repl size (B)" = 65536
"media retention" = 0
"min memory per query (KB)" = 1024
"nested triggers" = 1
"open objects" = 0
"query governor cost limit" = 0
"query wait (s)" = -1
"recovery interval (min)" = 0
"remote access" = 1
"remote login timeout (s)" = 20
"remote proc trans" = 0
"remote query timeout (s)" = 600
"scan for startup procs" = 0
"two digit year cutoff" = 2049
"user connections" = 0
"user options" = 0
And also the boot.ini has /3GB and /PAE
We have the settings as indicated set for years.
Settings determined by max memory size speced by SAP:
"min server memory (MB)" = "max server memory (MB)" = 3084
"set working set size" = 0
"awe enabled" = 1
Deviations from default installation as directed by SAP:
"network packet size" = 8192
"priority boost" = 0
"max degree of parallelism" = 1 (recommends setting this dynamically on when aggregates occur, off when oltp users come online)
"max worker threads" = 255
Kept as default as recommended by SAP:
"affinity mask" = 0
"c2 audit mode" = 0
"cost threshold for parallelism" = 5
"cursor threshold" = -1
"default full-text language" = 1033
"default language" = 0
"fill factor (%)" = 0
"index create memory (KB)" = 0
"lightweight pooling" = 0
"locks" = 0
"max text repl size (B)" = 65536
"media retention" = 0
"min memory per query (KB)" = 1024
"nested triggers" = 1
"open objects" = 0
"query governor cost limit" = 0
"query wait (s)" = -1
"recovery interval (min)" = 0
"remote access" = 1
"remote login timeout (s)" = 20
"remote proc trans" = 0
"remote query timeout (s)" = 600
"scan for startup procs" = 0
"two digit year cutoff" = 2049
"user connections" = 0
"user options" = 0
And also the boot.ini has /3GB and /PAE
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Daniel,
Lowering the memory down on min server memory (MB) seems to work
I now see:
Address Windowing Extensions enabled.
in the error log.
I no longer see the "Warning: unable to allocate 'min server memory' of 3084MB" error.
And while sql has not yet allocated the amount, I will watch it today to see if it grows.
Thanks.
I reference the following for anybody here on the same SAP environment:
NW 640, sql server 2000 ent, win2003 ent (all 32 bit).
SAP note 327494 reads to set <mindb> = <maxdb> when setting awe enabled.
This appears incorrect.
Even SAP support recommends this setting directly as such, and it does not work.
Thanks, Daniel. I will set yours as the accepted solution shortly.
Chris
Lowering the memory down on min server memory (MB) seems to work
I now see:
Address Windowing Extensions enabled.
in the error log.
I no longer see the "Warning: unable to allocate 'min server memory' of 3084MB" error.
And while sql has not yet allocated the amount, I will watch it today to see if it grows.
Thanks.
I reference the following for anybody here on the same SAP environment:
NW 640, sql server 2000 ent, win2003 ent (all 32 bit).
SAP note 327494 reads to set <mindb> = <maxdb> when setting awe enabled.
This appears incorrect.
Even SAP support recommends this setting directly as such, and it does not work.
Thanks, Daniel. I will set yours as the accepted solution shortly.
Chris
I'm glad your problem is solved.
Take care,
Daniel
Take care,
Daniel
http://support.microsoft.com/kb/899761
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=7C407047-3F1F-48B8-9E4C-DC32875E1961&displaylang=en