SP_configure changes -How
-
Hi folks
Having a bit of a problem with an application . msg is "SQl server is unable to allocate sufficient memory"
I'm moving the databases from 1 server to another and this is cropping up. I've run a sp_configure and displaythe values. 2 things were different they are min server memory which I changed and retry and got the same error.
The other value is the max pocket size. The old location shows max pocket size being 65536. The new network pocket size max is 32767
I can't find out how to change the max size. I don't even know where this value came from considering thatI used the same cd to create both SQLservers
I tried the following:
*****************************************************************************
exec sp_configure 'network packet size', 512, 65536
go
reconfigure
go
the result was
Server: Msg 8144, Level 16, State 2, Procedure sp_configure, Line 0
Procedure or function sp_configure has too many arguments specified.
*********************************************************************************
I know that sp_configure only expects 1 parm and when I run the sp_configure with 1 parm it applies the change to the config value and the run values.
can anyone help??????
-
From BOL: "
Note Do not change the packet size unless you are certain that it will improve performance. For most applications, the default packet size is best."
I do not think that changing this setting will help with your memory problem. You said that you are trying to move a database, how are you doing this? You should try a backup/restore or a detach/attach to move your database. Can you post the results of your SP_Configure as well as your memory/SQL Server version/OS version?
-
Hi john.
Thanks for taking the time to respond.
Background:
The old server is a windows nt 4.0(1381) with a sqlserver database of 7.00.623. The application runs on the same server.
The application has been install on a new server with the OS of windows 2003 and the lastest sp.
The database has been installed on a different server with a OS of windows 2000 5.00.2195 sp4 The database is SQL server 2000 sp3
The database was loaded using the DB recovery procedure from a database backup from the old server.
The application can link to the database, and the database is up and running. However when a transaction is executed the following message is received." Error s1000 10000,0 SQl server unable to allocate sufficient memory "
I ran the sp_configure command against the new server and listed the data below:
********************************************************************
affinity mask -2147483648 2147483647 0 0
allow updates 0 1 0 0
awe enabled 0 1 0 0
c2 audit mode 0 1 0 0
cost threshold for parallelism 0 32767 5 5
Cross DB Ownership Chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
default full-text language 0 2147483647 1033 1033
default language 0 9999 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 2147483647 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 466 466
max text repl size (B) 0 2147483647 65536 65536
max worker threads 32 32767 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 466 466
nested triggers 0 1 1 1
network packet size (B) 512 32767 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 0 0
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 600 600
scan for startup procs 0 1 0 0
set working set size 0 1 0 0
show advanced options 0 1 1 1
two digit year cutoff 1753 9999 2049 2049
user connections 0 32767 0 0
user options 0 32767 0 0
**********************************************************************
If I'm heading down the wrong road please point me in the correct direction to fix my problem.
-
Ah, I thought you were getting the memory error while trying to move the database. Now it makes more sense. Look at your Max Server Memory setting. You have it set to 466 MB. This setting needs bumped up; 466 MB is not enough memory. How much RAM to you have installed on your SQL box? Is your SQL box dedicated to SQL Server, or do you have other applications/processes running on your SQL box?
-
HI ....
I've increase tne max server memory to 2147483647 but still having a problem.
I also install MDAC 2.8 sp1 - no change
here is the error again in full.
-
continue
Error s1000 (10000, 0)
intersolv)(ODBC SQL server driver)(Microsoft SQL server) Unable to allocate sufficient memory. Unable to allocate sufficient memory -
I don't know if this is part of the problem:
Note SQL Server 7.0 system databases are not compatible with SQL Server 2000. Do not attach SQL Server 7.0 master, model, msdb or distribution databases to SQL Server 2000.
http://support.microsoft.com/kb/224071/en-us
-SQLBill
-
Is 2GB all you have installed on the server? How much memory was allocated to SQL Server on your old server? Is this new server servicing more databases than the old server?
You may need to run Windows Performance monitor with some of the SQL Server Memory counters to figure out what is happening in your memory.
-
I did not do a detach/reattach. I create the database in SQL2000 and did a recovery from a backup.
thanks for the reply
-
The old server has database using 2780 MB.
The new server only has 745 MB.
Configure info on the old server/new:
OLD server
affinity mask 0 147483647 0 0
allow updates 0 1 0 0
cost threshold for parallelism 0 32767 5 5
cursor threshold -1 2147483647 -1 -1
default language 0 9999 0 0
default sortorder id 0 255 52 52
extended memory size (MB) 0 2147483647 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 1600000 0 0
language in cache 3 100 3 3
language neutral full-text 0 1 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max async IO 1 255 32 32
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 16 16
max text repl size (B) 0 2147483647 65536 65536
max worker threads 10 1024 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 16 16
nested triggers 0 1 1 1
network packet size (B) 512 65535 512 512
open objects 0 2147483647 0 0
priority boost 0 1 0 0
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 5 5
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 0 0
resource timeout (s) 5 2147483647 10 10
scan for startup procs 0 1 1 1
set working set size 0 1 0 0
show advanced options 0 1 1 1
spin counter 1 2147483647 10000 0
time slice (ms) 50 1000 100 100
two digit year cutoff 1753 9999 2049 2049
Unicode comparison style 0 2147483647 96609 196609
Unicode locale id 0 2147483647 1033 1033
user connections 0 32767 0 0
user options 0 4095 0 0
*****************************************
New Server
affinity mask -2147483648 2147483647 0 0
allow updates 0 1 0 0
awe enabled 0 1 0 0
c2 audit mode 0 1 0 0
cost threshold for parallelism 0 32767 5 5
Cross DB Ownership Chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
default full-text language 0 2147483647 1033 1033
default language 0 9999 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 2147483647 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 2147483647 2147483647
max text repl size (B) 0 2147483647 65536 65536
max worker threads 32 32767 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 1015 1015
nested triggers 0 1 1 1
network packet size (B) 512 32767 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 0 0
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 600 600
scan for startup procs 0 1 0 0
set working set size 0 1 0 0
show advanced options 0 1 1 1
two digit year cutoff 1753 9999
user connections 0 32767 0 0
user options 0 32767 0 0
*****************************************
I have tested the application against an old development database and it still works fine so I have ruled out that we have a problem with the software running on W2K3.
-
"The new server only has 745 MB."
745 MB?? You need more memory. If you've retested and everything works fine with more memory (on your old server) and you are getting memory related errors with only 745 MB of memory, you need to add more memory. SQL Server reserves approx. 384 MB of available memory for overhead purposes, so that leaves you with less than 400 MB. This is not enough memory, even for a test server. Run Windows PerfMon and watch the SQL Server Memory>>Target Memory and SQL Server Memory>>Total Memory counters. You should see the Target Memory down around 400 MB and I would guess the Total Memory would be the same.
-
Success at last.
John thanks for the help.
I adjusted the memory sizes and the error still continued.
After banging my head on the wall for several hours I decided to change the network packet size from 4096 to 512. The application worked like a charm.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply