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: