memory allocation error

using sp_configure we inadvertanly set the network packet size too big, an
didn't get a warning. when we restarted the mssql service we found that the
sqlserv instance wouldn't come up.. we were able to go into the tools >>
options >>
advanced menu and reset the packet size to 4096, which did allow us to
bring up the sqlserv instance, but when we try to bring up sql analyzer to
change the sp_configure paramenter we get 'odbc msg 0 level 16 state1 sql
serve driver (memor allocation failure). We suspect that even though we
change the packet size to 4096 in the tools >> options >> advanced window it
doesnt change the sp_configure value. How can we do this without sql
analyzer ?
Don
Something is not right here. First of all, whatever the value you set for
'network packet size' usig sp_configure should not prevent the SQL instance
from coming up online. There is a max value which is 32767. If you try to set
a bigger value, SQL Server will reject it.
When you use Tools-> Options-->Advaced to change packet size, you are not
changing the option on the server side. Rather, you are changing a client
setting.
When your SDQL instance did not come online, what error message did you get?
Linchi
"don" wrote:

> using sp_configure we inadvertanly set the network packet size too big, an
> didn't get a warning. when we restarted the mssql service we found that the
> sqlserv instance wouldn't come up.. we were able to go into the tools >>
> options >>
> advanced menu and reset the packet size to 4096, which did allow us to
> bring up the sqlserv instance, but when we try to bring up sql analyzer to
> change the sp_configure paramenter we get 'odbc msg 0 level 16 state1 sql
> serve driver (memor allocation failure). We suspect that even though we
> change the packet size to 4096 in the tools >> options >> advanced window it
> doesnt change the sp_configure value. How can we do this without sql
> analyzer ?
> --
> Don
|||we get :odbc msg0 level 16 state 1 sqlserv driver(memory allocation failure)
trying to connect to sqlquery.. which means i cant run any sql commands to
reset sp_configure, unless you know of some other method of running that
process. Also when we try to start the sqlserver agent we get this error: 165
odc error 0, memory allocation failure [sqlstate HY001]
Don
"Linchi Shea" wrote:
[vbcol=seagreen]
> Something is not right here. First of all, whatever the value you set for
> 'network packet size' usig sp_configure should not prevent the SQL instance
> from coming up online. There is a max value which is 32767. If you try to set
> a bigger value, SQL Server will reject it.
> When you use Tools-> Options-->Advaced to change packet size, you are not
> changing the option on the server side. Rather, you are changing a client
> setting.
> When your SDQL instance did not come online, what error message did you get?
> Linchi
> "don" wrote:
|||we had to install ms sql server management studio (part of client tools) for
mssql 2005. This allowed us to reset the value in the sp_config procedure to
an acceptable value using the object explorer.
Don
"Linchi Shea" wrote:
[vbcol=seagreen]
> Something is not right here. First of all, whatever the value you set for
> 'network packet size' usig sp_configure should not prevent the SQL instance
> from coming up online. There is a max value which is 32767. If you try to set
> a bigger value, SQL Server will reject it.
> When you use Tools-> Options-->Advaced to change packet size, you are not
> changing the option on the server side. Rather, you are changing a client
> setting.
> When your SDQL instance did not come online, what error message did you get?
> Linchi
> "don" wrote:

memory allocation error

using sp_configure we inadvertanly set the network packet size too big, an
didn't get a warning. when we restarted the mssql service we found that the
sqlserv instance wouldn't come up.. we were able to go into the tools >>
options >>
advanced menu and reset the packet size to 4096, which did allow us to
bring up the sqlserv instance, but when we try to bring up sql analyzer to
change the sp_configure paramenter we get 'odbc msg 0 level 16 state1 sql
serve driver (memor allocation failure). We suspect that even though we
change the packet size to 4096 in the tools >> options >> advanced window it
doesnt change the sp_configure value. How can we do this without sql
analyzer '
--
DonSomething is not right here. First of all, whatever the value you set for
'network packet size' usig sp_configure should not prevent the SQL instance
from coming up online. There is a max value which is 32767. If you try to set
a bigger value, SQL Server will reject it.
When you use Tools-> Options-->Advaced to change packet size, you are not
changing the option on the server side. Rather, you are changing a client
setting.
When your SDQL instance did not come online, what error message did you get?
Linchi
"don" wrote:
> using sp_configure we inadvertanly set the network packet size too big, an
> didn't get a warning. when we restarted the mssql service we found that the
> sqlserv instance wouldn't come up.. we were able to go into the tools >>
> options >>
> advanced menu and reset the packet size to 4096, which did allow us to
> bring up the sqlserv instance, but when we try to bring up sql analyzer to
> change the sp_configure paramenter we get 'odbc msg 0 level 16 state1 sql
> serve driver (memor allocation failure). We suspect that even though we
> change the packet size to 4096 in the tools >> options >> advanced window it
> doesnt change the sp_configure value. How can we do this without sql
> analyzer '
> --
> Don|||we get :odbc msg0 level 16 state 1 sqlserv driver(memory allocation failure)
trying to connect to sqlquery.. which means i cant run any sql commands to
reset sp_configure, unless you know of some other method of running that
process. Also when we try to start the sqlserver agent we get this error: 165
odc error 0, memory allocation failure [sqlstate HY001]
--
Don
"Linchi Shea" wrote:
> Something is not right here. First of all, whatever the value you set for
> 'network packet size' usig sp_configure should not prevent the SQL instance
> from coming up online. There is a max value which is 32767. If you try to set
> a bigger value, SQL Server will reject it.
> When you use Tools-> Options-->Advaced to change packet size, you are not
> changing the option on the server side. Rather, you are changing a client
> setting.
> When your SDQL instance did not come online, what error message did you get?
> Linchi
> "don" wrote:
> >
> > using sp_configure we inadvertanly set the network packet size too big, an
> > didn't get a warning. when we restarted the mssql service we found that the
> > sqlserv instance wouldn't come up.. we were able to go into the tools >>
> > options >>
> > advanced menu and reset the packet size to 4096, which did allow us to
> > bring up the sqlserv instance, but when we try to bring up sql analyzer to
> > change the sp_configure paramenter we get 'odbc msg 0 level 16 state1 sql
> > serve driver (memor allocation failure). We suspect that even though we
> > change the packet size to 4096 in the tools >> options >> advanced window it
> > doesnt change the sp_configure value. How can we do this without sql
> > analyzer '
> >
> > --
> > Don|||we had to install ms sql server management studio (part of client tools) for
mssql 2005. This allowed us to reset the value in the sp_config procedure to
an acceptable value using the object explorer.
--
Don
"Linchi Shea" wrote:
> Something is not right here. First of all, whatever the value you set for
> 'network packet size' usig sp_configure should not prevent the SQL instance
> from coming up online. There is a max value which is 32767. If you try to set
> a bigger value, SQL Server will reject it.
> When you use Tools-> Options-->Advaced to change packet size, you are not
> changing the option on the server side. Rather, you are changing a client
> setting.
> When your SDQL instance did not come online, what error message did you get?
> Linchi
> "don" wrote:
> >
> > using sp_configure we inadvertanly set the network packet size too big, an
> > didn't get a warning. when we restarted the mssql service we found that the
> > sqlserv instance wouldn't come up.. we were able to go into the tools >>
> > options >>
> > advanced menu and reset the packet size to 4096, which did allow us to
> > bring up the sqlserv instance, but when we try to bring up sql analyzer to
> > change the sp_configure paramenter we get 'odbc msg 0 level 16 state1 sql
> > serve driver (memor allocation failure). We suspect that even though we
> > change the packet size to 4096 in the tools >> options >> advanced window it
> > doesnt change the sp_configure value. How can we do this without sql
> > analyzer '
> >
> > --
> > Don

memory - PAE / AWE...........

My server is a mssql2000 sp3 server in windows 2000
advanced server.
My server has 3 GB of memory. PAE is enables in the
boot.ini. My memory to mssql is unrestricted. In the task
manager, the memory of mssql shows as 2.7GB. I still have
page faults at 9,27,6683 for 21 days of non-reboot.
Is MSSQL really using 2.7 GB? DO I need more memory?
What is the 'awe enabled' option all about?
regards,
bharathIf you have only 3 GB then the only switch you need in boot.ini is /3GB, you
don't need /PAE. Assuming you have SQL2000 Enterprise Edition then SQL
Server will be able to use the memory. You don't need to enable awe when you
only have 3GB of RAM.Which counter are you using to determine page faults ?
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"bharath" <anonymous@.discussions.microsoft.com> wrote in message
news:0a1701c3d5b6$f9aec310$a101280a@.phx.gbl...
> My server is a mssql2000 sp3 server in windows 2000
> advanced server.
> My server has 3 GB of memory. PAE is enables in the
> boot.ini. My memory to mssql is unrestricted. In the task
> manager, the memory of mssql shows as 2.7GB. I still have
> page faults at 9,27,6683 for 21 days of non-reboot.
> Is MSSQL really using 2.7 GB? DO I need more memory?
> What is the 'awe enabled' option all about?
> regards,
> bharath

Memory

Hi,
How do i calculate de right memory for my MSSQL Server 2000, is there
any formula?
Thanks
Luis FoutoWhat edition of SQL Server do you have? What is your operating system?
SQL Server Standard Edition running on Win2k Standard can use a max of 2GB
of RAM.
How big are your databases?
What other applications (or services such as IIS) are running on the server?
Generally you want to allow SQL Server to use as much memory as it needs.
If you have other apps running and limited memory you may want to limit
memory usage somewhat.
--
Keith
"Luis Fouto" <luis.fouto@.highvalue.pt> wrote in message
news:uSZh2IDvEHA.1988@.TK2MSFTNGP12.phx.gbl...
> Hi,
> How do i calculate de right memory for my MSSQL Server 2000, is there
> any formula?
> Thanks
> Luis Fouto
>|||The Book SQL Server 2000 Administrators Companion has a
rough guide formula for the amount memory to be used by
SQL Server which goes something like
(500K * The Number of Users) + (Database prcoess memory)
The data process memory is to do with the data and log
cache sizes although unless you know a lot more on what
your going to have on it is going to be a bit hard to
calculate.
Personally I would do as Keith sugested, get as much of it
as you can, but remember stealing it is again the law in
most counties.
Peter
"Adam and Eve had many advantages but the principal one
was that they escaped teething."
Mark Twain
>--Original Message--
>Hi,
> How do i calculate de right memory for my MSSQL
Server 2000, is there
>any formula?
>Thanks
>Luis Fouto
>
>.
>

Memory

Hi,
How do i calculate de right memory for my MSSQL Server 2000, is there
any formula?
Thanks
Luis FoutoWhat edition of SQL Server do you have? What is your operating system?
SQL Server Standard Edition running on Win2k Standard can use a max of 2GB
of RAM.
How big are your databases?
What other applications (or services such as IIS) are running on the server?
Generally you want to allow SQL Server to use as much memory as it needs.
If you have other apps running and limited memory you may want to limit
memory usage somewhat.
Keith
"Luis Fouto" <luis.fouto@.highvalue.pt> wrote in message
news:uSZh2IDvEHA.1988@.TK2MSFTNGP12.phx.gbl...
> Hi,
> How do i calculate de right memory for my MSSQL Server 2000, is there
> any formula?
> Thanks
> Luis Fouto
>sql

Memory

Hi,
How do i calculate de right memory for my MSSQL Server 2000, is there
any formula?
Thanks
Luis Fouto
What edition of SQL Server do you have? What is your operating system?
SQL Server Standard Edition running on Win2k Standard can use a max of 2GB
of RAM.
How big are your databases?
What other applications (or services such as IIS) are running on the server?
Generally you want to allow SQL Server to use as much memory as it needs.
If you have other apps running and limited memory you may want to limit
memory usage somewhat.
Keith
"Luis Fouto" <luis.fouto@.highvalue.pt> wrote in message
news:uSZh2IDvEHA.1988@.TK2MSFTNGP12.phx.gbl...
> Hi,
> How do i calculate de right memory for my MSSQL Server 2000, is there
> any formula?
> Thanks
> Luis Fouto
>
sql