Error 17803, Severity: 20, State: 12

This is an annoying one.
I have 4 test boxes running our application. The application is a wervice
using ADO.Net to connect to SQL Server. The machines are all P4, 2.8GHZ CPU.
Windows Server 2003 Std. Edition, w/ all current patches (as of 10/13/04)
running SQL Server 2000 Developer Edition. SP3a. The machines are also
runnign Share Point Services and BIz Talk 2004, although neither of those are
doing anything at this time. Two of the machines have 1GB physical memory, 2
have 2GB.
We have one test case where have to process a 50MB message (SOAP post with
50MB payload). The problem, interestingly enough occurs on both of the
machines with 2GB. With a default install of SQL Server, configured to
dynamically allocate memory, the insert saving the 50MB message fails. SQL
Server logs "Error: 17803, Severity 20, State:12 Insufficient memory
available." Monitoring physical memory it doesn't appear that the box is
even close to out of memory. If I change the SQL server configuration to use
a fixed or minimum amount of memory (1GB) the insert works 8 out of 9 times,
but it still occasionally fails logging the same error. It makes no sense,
especially where it works 100% of the time on the two boxes with less memory.
Any help here would be appreciated.
Hi GGould,
The answer to your question lies in the the way SQL allocates memory.
Every process (on a 32bits machine) gets and 4 GB Virtual address space in
this address space 2 GB is devote to user, 2 GB for kernel allocations.
SQL server, when it starts with the default memory configuration will map
his executable and dlls into this 2 GB virtual user address space and will
allocate all physical memory in the system to it's Bpool data "cache" of 8K
buffers, leaving 256 MB of his virtual address space for his so called mem to
leave which ican be used for all memory allocations > 8 KB.
On a system with 2 GB SQL will allocate ~1.7 GB physical memory for its
bpool and .exe/dlls. Mapping all this into the virtual address space exactlu
leaves ~256 mb for the mem to leave area
On a s system with 1 GB, SQL will allocate ~.7 GB physical mmeory for bpool
and .exe/dlls. Mapping this memory into the virtual address space leaves
~1.256 mb for allocations ending up in the mem to leave area.
As for your action it's required to allocate a >8kb block SQL will allocate
such block and map it in to the memto leave area.
If you need a memory block > can fit in mem to leave area you get the
insufficient memory error.
To workaround on the 2 GB system you can use the -gxxx startup parametera nd
specify > 256 for xxx . At startup SQL will leave more space for the mem to
leave area at the cost of Bpool
Ab othe options is if you have a enterprice edition of the OS to use the
/3GB. take care however wjen using terminal server and make sure the machine
is a dedicated SQL server else don't use /3gb in the boot.ini.
Regards,
Jago
> This is an annoying one.
> I have 4 test boxes running our application. The application is a wervice
> using ADO.Net to connect to SQL Server. The machines are all P4, 2.8GHZ CPU.
> Windows Server 2003 Std. Edition, w/ all current patches (as of 10/13/04)
> running SQL Server 2000 Developer Edition. SP3a. The machines are also
> runnign Share Point Services and BIz Talk 2004, although neither of those are
> doing anything at this time. Two of the machines have 1GB physical memory, 2
> have 2GB.
> We have one test case where have to process a 50MB message (SOAP post with
> 50MB payload). The problem, interestingly enough occurs on both of the
> machines with 2GB. With a default install of SQL Server, configured to
> dynamically allocate memory, the insert saving the 50MB message fails. SQL
> Server logs "Error: 17803, Severity 20, State:12 Insufficient memory
> available." Monitoring physical memory it doesn't appear that the box is
> even close to out of memory. If I change the SQL server configuration to use
> a fixed or minimum amount of memory (1GB) the insert works 8 out of 9 times,
> but it still occasionally fails logging the same error. It makes no sense,
> especially where it works 100% of the time on the two boxes with less memory.
> Any help here would be appreciated.
|||Thanks,
That seems to have taken care of it.
"Jago" wrote:
[vbcol=seagreen]
> Hi GGould,
> The answer to your question lies in the the way SQL allocates memory.
> Every process (on a 32bits machine) gets and 4 GB Virtual address space in
> this address space 2 GB is devote to user, 2 GB for kernel allocations.
> SQL server, when it starts with the default memory configuration will map
> his executable and dlls into this 2 GB virtual user address space and will
> allocate all physical memory in the system to it's Bpool data "cache" of 8K
> buffers, leaving 256 MB of his virtual address space for his so called mem to
> leave which ican be used for all memory allocations > 8 KB.
> On a system with 2 GB SQL will allocate ~1.7 GB physical memory for its
> bpool and .exe/dlls. Mapping all this into the virtual address space exactlu
> leaves ~256 mb for the mem to leave area
> On a s system with 1 GB, SQL will allocate ~.7 GB physical mmeory for bpool
> and .exe/dlls. Mapping this memory into the virtual address space leaves
> ~1.256 mb for allocations ending up in the mem to leave area.
> As for your action it's required to allocate a >8kb block SQL will allocate
> such block and map it in to the memto leave area.
> If you need a memory block > can fit in mem to leave area you get the
> insufficient memory error.
> To workaround on the 2 GB system you can use the -gxxx startup parametera nd
> specify > 256 for xxx . At startup SQL will leave more space for the mem to
> leave area at the cost of Bpool
> Ab othe options is if you have a enterprice edition of the OS to use the
> /3GB. take care however wjen using terminal server and make sure the machine
> is a dedicated SQL server else don't use /3gb in the boot.ini.
>
> Regards,
>
> Jago
sql