SqlServer2005 SE SP2 - Windows2003 EE SP2
Here's the facts:
Server has 8gb.
/PAE is in the boot.ini
Lock pages in memory option has been set to on with permissions for the SQL
login
Server was re-booted
Use AWE to allocate memory was set via SSMS
I can't increase the Max Server Memory via SSMS
Any help would be appreciated.
Thanks
RonCan you RUN the following and post the output of the max server memory
statement?
Is your concern that you can use SSMS? Or can't set the max memory?Not
trying to be obtuse, just wondering where your concern is.
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'max server memory' ;
GO
sp_configure 'show advanced options', 0
RECONFIGURE
GO
"Ron" wrote:
> SqlServer2005 SE SP2 - Windows2003 EE SP2
> Here's the facts:
> Server has 8gb.
> /PAE is in the boot.ini
> Lock pages in memory option has been set to on with permissions for the SQL
> login
> Server was re-booted
> Use AWE to allocate memory was set via SSMS
> I can't increase the Max Server Memory via SSMS
> Any help would be appreciated.
> Thanks
> Ron
>|||Do you need Enterprise Edition or will this work in Standard Edition?
"Joe" wrote:
> Can you RUN the following and post the output of the max server memory
> statement?
> Is your concern that you can use SSMS? Or can't set the max memory?Not
> trying to be obtuse, just wondering where your concern is.
> sp_configure 'show advanced options', 1
> RECONFIGURE
> GO
> sp_configure 'max server memory' ;
> GO
> sp_configure 'show advanced options', 0
> RECONFIGURE
> GO
> "Ron" wrote:
> > SqlServer2005 SE SP2 - Windows2003 EE SP2
> >
> > Here's the facts:
> >
> > Server has 8gb.
> > /PAE is in the boot.ini
> > Lock pages in memory option has been set to on with permissions for the SQL
> > login
> > Server was re-booted
> > Use AWE to allocate memory was set via SSMS
> >
> > I can't increase the Max Server Memory via SSMS
> >
> > Any help would be appreciated.
> >
> > Thanks
> >
> > Ron
> >|||The Sql should be version independant.
If you are referring to the amount of memory that is supported in SQL 2005
that is generally controlled by the version of the OS.
Let's see how much is being consumed:
select counter_name, cntr_value / 1024.0 / 1024.0 as GB, *
from master..sysperfinfo
where object_name = 'SQLServer:Memory Manager'
and counter_name in ('Target Server Memory (KB)', 'Total
Server Memory (KB)')
Then post the max server memory result
"Ron" wrote:
> Do you need Enterprise Edition or will this work in Standard Edition?
> "Joe" wrote:
> > Can you RUN the following and post the output of the max server memory
> > statement?
> > Is your concern that you can use SSMS? Or can't set the max memory?Not
> > trying to be obtuse, just wondering where your concern is.
> > sp_configure 'show advanced options', 1
> > RECONFIGURE
> > GO
> > sp_configure 'max server memory' ;
> > GO
> > sp_configure 'show advanced options', 0
> > RECONFIGURE
> > GO
> >
> > "Ron" wrote:
> >
> > > SqlServer2005 SE SP2 - Windows2003 EE SP2
> > >
> > > Here's the facts:
> > >
> > > Server has 8gb.
> > > /PAE is in the boot.ini
> > > Lock pages in memory option has been set to on with permissions for the SQL
> > > login
> > > Server was re-booted
> > > Use AWE to allocate memory was set via SSMS
> > >
> > > I can't increase the Max Server Memory via SSMS
> > >
> > > Any help would be appreciated.
> > >
> > > Thanks
> > >
> > > Ron
> > >|||Joe,
Here's the results:
Target Server Memory (KB)
1.505187988281 SQLServer:Memory Manager
Target
Server Memory (KB)
1578304 65792
SQL can only access 1.5gb out of 8gb on the server. The /PAE switch is in
boot.ini and lock pages set.
I tried using SSMS to check on AWE and bump up Max memory, but Max memory
won't go past 2gb and SQL still only takes 1.5GB.
Do I need to run sp_config instead of using SSMS? Also the server is now in
production, so would a recycle be required?
Thanks
Ron
"Joe" wrote:
> The Sql should be version independant.
> If you are referring to the amount of memory that is supported in SQL 2005
> that is generally controlled by the version of the OS.
> Let's see how much is being consumed:
> select counter_name, cntr_value / 1024.0 / 1024.0 as GB, *
> from master..sysperfinfo
> where object_name = 'SQLServer:Memory Manager'
> and counter_name in ('Target Server Memory (KB)', 'Total
> Server Memory (KB)')
> Then post the max server memory result
> "Ron" wrote:
> > Do you need Enterprise Edition or will this work in Standard Edition?
> >
> > "Joe" wrote:
> >
> > > Can you RUN the following and post the output of the max server memory
> > > statement?
> > > Is your concern that you can use SSMS? Or can't set the max memory?Not
> > > trying to be obtuse, just wondering where your concern is.
> > > sp_configure 'show advanced options', 1
> > > RECONFIGURE
> > > GO
> > > sp_configure 'max server memory' ;
> > > GO
> > > sp_configure 'show advanced options', 0
> > > RECONFIGURE
> > > GO
> > >
> > > "Ron" wrote:
> > >
> > > > SqlServer2005 SE SP2 - Windows2003 EE SP2
> > > >
> > > > Here's the facts:
> > > >
> > > > Server has 8gb.
> > > > /PAE is in the boot.ini
> > > > Lock pages in memory option has been set to on with permissions for the SQL
> > > > login
> > > > Server was re-booted
> > > > Use AWE to allocate memory was set via SSMS
> > > >
> > > > I can't increase the Max Server Memory via SSMS
> > > >
> > > > Any help would be appreciated.
> > > >
> > > > Thanks
> > > >
> > > > Ron
> > > >|||On Apr 3, 6:45=A0am, Ron <R...@.discussions.microsoft.com> wrote:
> Joe,
> Here's the results:
> Target Server Memory (KB) =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ==A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
> =A0 =A0 =A0 =A0 1.505187988281 =A0SQLServer:Memory Manager =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ==A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Targ=et
> Server Memory (KB) =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 1578304 65792
> SQL can only access 1.5gb out of 8gb on the server. =A0The /PAE switch is =in
> boot.ini and lock pages set.
> I tried using SSMS to check on AWE and bump up Max memory, but Max memory
> won't go past 2gb and SQL still only takes 1.5GB.
> Do I need to run sp_config instead of using SSMS? =A0Also the server is no=w in
> production, so would a recycle be required?
> Thanks
> Ron
>
> "Joe" wrote:
> > The Sql should be version independant. =A0
> > If you are referring to the amount of memory that is supported in SQL 20=05
> > that is generally controlled by the version of the OS.
> > Let's see how much is being consumed:
> > select counter_name, cntr_value / 1024.0 / 1024.0 as GB, *
> > =A0 =A0 =A0 =A0 from master..sysperfinfo
> > =A0 =A0 =A0 =A0 where object_name =3D 'SQLServer:Memory Manager'
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 and counter_name in ('Target Server Memo=ry (KB)', 'Total
> > Server Memory (KB)')
> > Then post the max server memory result
> > "Ron" wrote:
> > > Do you need Enterprise Edition or will this work in Standard Edition?
> > > "Joe" wrote:
> > > > Can you RUN the following and post the output of the max server memo=ry
> > > > statement?
> > > > Is your concern that you can use SSMS? =A0Or can't set the max memor=y?Not
> > > > trying to be obtuse, just wondering where your concern is.
> > > > sp_configure 'show advanced options', 1
> > > > RECONFIGURE
> > > > GO
> > > > sp_configure 'max server memory' ;
> > > > GO
> > > > sp_configure 'show advanced options', 0
> > > > RECONFIGURE
> > > > GO
> > > > "Ron" wrote:
> > > > > SqlServer2005 SE SP2 - Windows2003 EE SP2
> > > > > Here's the facts:
> > > > > Server has 8gb. =A0
> > > > > /PAE is in the boot.ini
> > > > > Lock pages in memory option has been set to on with permissions fo=r the SQL
> > > > > login
> > > > > Server was re-booted
> > > > > Use AWE to allocate memory was set via SSMS
> > > > > I can't increase the Max Server Memory via SSMS
> > > > > Any help would be appreciated.
> > > > > Thanks
> > > > > Ron- Hide quoted text -
> - Show quoted text -
What do you mean when you say it won't go above 2GB? If you are
looking at the following number: 2147483647 in the option for Max
Memory - remember, that number is in MB which is 2097151.9990234375
GB, much larger than 2GB :)
If you want to use 6GB set it to: 6144 MB.
I would also include the /3GB switch in the boot.ini with only 8GB
available. The general rule is: more than 4GB but less than 12GB
include /3G - over 12GB don't include /3GB because the OS needs more
than 1GB of memory available when you have more than 12GB of memory
available.
HTH,
Jeff|||All of my SQL Servers show a max memory of 2147483647, though by default non
go beyond 2gb.
This SQL Server can't seem to go past 1.5gb, thought the server has 8gb.
Do I need Enterprise Edition?
"Jeffrey Williams" wrote:
> On Apr 3, 6:45 am, Ron <R...@.discussions.microsoft.com> wrote:
> > Joe,
> >
> > Here's the results:
> > Target Server Memory (KB)
> >
> > 1.505187988281 SQLServer:Memory Manager
> > Target
> > Server Memory (KB)
> >
> > 1578304 65792
> >
> > SQL can only access 1.5gb out of 8gb on the server. The /PAE switch is in
> > boot.ini and lock pages set.
> >
> > I tried using SSMS to check on AWE and bump up Max memory, but Max memory
> > won't go past 2gb and SQL still only takes 1.5GB.
> >
> > Do I need to run sp_config instead of using SSMS? Also the server is now in
> > production, so would a recycle be required?
> >
> > Thanks
> >
> > Ron
> >
> >
> >
> > "Joe" wrote:
> > > The Sql should be version independant.
> > > If you are referring to the amount of memory that is supported in SQL 2005
> > > that is generally controlled by the version of the OS.
> > > Let's see how much is being consumed:
> > > select counter_name, cntr_value / 1024.0 / 1024.0 as GB, *
> > > from master..sysperfinfo
> > > where object_name = 'SQLServer:Memory Manager'
> > > and counter_name in ('Target Server Memory (KB)', 'Total
> > > Server Memory (KB)')
> >
> > > Then post the max server memory result
> >
> > > "Ron" wrote:
> >
> > > > Do you need Enterprise Edition or will this work in Standard Edition?
> >
> > > > "Joe" wrote:
> >
> > > > > Can you RUN the following and post the output of the max server memory
> > > > > statement?
> > > > > Is your concern that you can use SSMS? Or can't set the max memory?Not
> > > > > trying to be obtuse, just wondering where your concern is.
> > > > > sp_configure 'show advanced options', 1
> > > > > RECONFIGURE
> > > > > GO
> > > > > sp_configure 'max server memory' ;
> > > > > GO
> > > > > sp_configure 'show advanced options', 0
> > > > > RECONFIGURE
> > > > > GO
> >
> > > > > "Ron" wrote:
> >
> > > > > > SqlServer2005 SE SP2 - Windows2003 EE SP2
> >
> > > > > > Here's the facts:
> >
> > > > > > Server has 8gb.
> > > > > > /PAE is in the boot.ini
> > > > > > Lock pages in memory option has been set to on with permissions for the SQL
> > > > > > login
> > > > > > Server was re-booted
> > > > > > Use AWE to allocate memory was set via SSMS
> >
> > > > > > I can't increase the Max Server Memory via SSMS
> >
> > > > > > Any help would be appreciated.
> >
> > > > > > Thanks
> >
> > > > > > Ron- Hide quoted text -
> >
> > - Show quoted text -
> What do you mean when you say it won't go above 2GB? If you are
> looking at the following number: 2147483647 in the option for Max
> Memory - remember, that number is in MB which is 2097151.9990234375
> GB, much larger than 2GB :)
> If you want to use 6GB set it to: 6144 MB.
> I would also include the /3GB switch in the boot.ini with only 8GB
> available. The general rule is: more than 4GB but less than 12GB
> include /3G - over 12GB don't include /3GB because the OS needs more
> than 1GB of memory available when you have more than 12GB of memory
> available.
> HTH,
> Jeff
>|||If this is SQL server 2005 you do not need EE. If it is 2000 you do.
You can use TSQL instead of the GUI.
To check for AWE you would run
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'AWE enabled' ;
go
--if the above returns 1 then AWE is eanbled if 0 it is not
--to enable run
sp_configure 'AWE enabled', 1;
go
RECONFIGURE
GO
--now to set 6 gb ofr max ram
sp_configure 'max server memory', 6144 ;
GO
RECONFIGURE
GO
sp_configure 'show advanced options', 0
RECONFIGURE
GO
Need to ask some obvious questions you are in the system admin role on the
DB server ?
from this link:
http://msdn2.microsoft.com/en-us/library/ms190731.aspx
the following:
Windows Server 2003, Standard Edition supports physical memory up to 4
gigabytes (GB).
Windows Server 2003, Enterprise Edition supports physical memory up to 32 GB.
Windows Server 2003, Datacenter Edition supports physical memory up to 64 GB.
And finally
You must restart the instance of SQL Server for AWE to take effect.
"Ron" wrote:
> All of my SQL Servers show a max memory of 2147483647, though by default non
> go beyond 2gb.
> This SQL Server can't seem to go past 1.5gb, thought the server has 8gb.
> Do I need Enterprise Edition?
> "Jeffrey Williams" wrote:
> > On Apr 3, 6:45 am, Ron <R...@.discussions.microsoft.com> wrote:
> > > Joe,
> > >
> > > Here's the results:
> > > Target Server Memory (KB)
> > >
> > > 1.505187988281 SQLServer:Memory Manager
> > > Target
> > > Server Memory (KB)
> > >
> > > 1578304 65792
> > >
> > > SQL can only access 1.5gb out of 8gb on the server. The /PAE switch is in
> > > boot.ini and lock pages set.
> > >
> > > I tried using SSMS to check on AWE and bump up Max memory, but Max memory
> > > won't go past 2gb and SQL still only takes 1.5GB.
> > >
> > > Do I need to run sp_config instead of using SSMS? Also the server is now in
> > > production, so would a recycle be required?
> > >
> > > Thanks
> > >
> > > Ron
> > >
> > >
> > >
> > > "Joe" wrote:
> > > > The Sql should be version independant.
> > > > If you are referring to the amount of memory that is supported in SQL 2005
> > > > that is generally controlled by the version of the OS.
> > > > Let's see how much is being consumed:
> > > > select counter_name, cntr_value / 1024.0 / 1024.0 as GB, *
> > > > from master..sysperfinfo
> > > > where object_name = 'SQLServer:Memory Manager'
> > > > and counter_name in ('Target Server Memory (KB)', 'Total
> > > > Server Memory (KB)')
> > >
> > > > Then post the max server memory result
> > >
> > > > "Ron" wrote:
> > >
> > > > > Do you need Enterprise Edition or will this work in Standard Edition?
> > >
> > > > > "Joe" wrote:
> > >
> > > > > > Can you RUN the following and post the output of the max server memory
> > > > > > statement?
> > > > > > Is your concern that you can use SSMS? Or can't set the max memory?Not
> > > > > > trying to be obtuse, just wondering where your concern is.
> > > > > > sp_configure 'show advanced options', 1
> > > > > > RECONFIGURE
> > > > > > GO
> > > > > > sp_configure 'max server memory' ;
> > > > > > GO
> > > > > > sp_configure 'show advanced options', 0
> > > > > > RECONFIGURE
> > > > > > GO
> > >
> > > > > > "Ron" wrote:
> > >
> > > > > > > SqlServer2005 SE SP2 - Windows2003 EE SP2
> > >
> > > > > > > Here's the facts:
> > >
> > > > > > > Server has 8gb.
> > > > > > > /PAE is in the boot.ini
> > > > > > > Lock pages in memory option has been set to on with permissions for the SQL
> > > > > > > login
> > > > > > > Server was re-booted
> > > > > > > Use AWE to allocate memory was set via SSMS
> > >
> > > > > > > I can't increase the Max Server Memory via SSMS
> > >
> > > > > > > Any help would be appreciated.
> > >
> > > > > > > Thanks
> > >
> > > > > > > Ron- Hide quoted text -
> > >
> > > - Show quoted text -
> >
> > What do you mean when you say it won't go above 2GB? If you are
> > looking at the following number: 2147483647 in the option for Max
> > Memory - remember, that number is in MB which is 2097151.9990234375
> > GB, much larger than 2GB :)
> >
> > If you want to use 6GB set it to: 6144 MB.
> >
> > I would also include the /3GB switch in the boot.ini with only 8GB
> > available. The general rule is: more than 4GB but less than 12GB
> > include /3G - over 12GB don't include /3GB because the OS needs more
> > than 1GB of memory available when you have more than 12GB of memory
> > available.
> >
> > HTH,
> >
> > Jeff
> >
sql
