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

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 ar
e
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 us
e
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 t
o
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 C
PU.
> 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. SQ
L
> 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 time
s,
> 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 memo
ry.
> 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 8
K
> 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 exact
lu
> leaves ~256 mb for the mem to leave area
> On a s system with 1 GB, SQL will allocate ~.7 GB physical mmeory for bpoo
l
> 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 allocat
e
> 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 t
o
> 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 machi
ne
> is a dedicated SQL server else don't use /3gb in the boot.ini.
>
> Regards,
>
> Jago

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:
> 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.

Error : Cannot open user default database. Login failed.

I work with Microsoft visual studio 2005.
i get data from a table by ADO.NET :

SqlConnection sqlConn = new SqlConnection(strConnectionString);
sqlConn.Open();
.....

it works fine.

but when i open the SQL Server 2005 express edition and connect to the server (inside the same computer) to check something and then get back to the VS and run the code again ,the Open() function fails with the exception :

{"Cannot open user default database. Login failed.\r\nLogin failed for user 'DOMAIN\\asaf_a'."

disconnecting the server doesn't help and even closing the SQL Server 2005 application doesn't help.
Only restarting the computer enables the Open() command to work again.

Why ?

Every user has a default database specified. If you are using a SQLConnectinstring you are normally redirecting the user within the connectionstring to a specific database (Initial Catalog). By connecting with another tool which does not have the availbility to choose a starting database the user is redirected to its "home" / default database. If the database was dropped in the meantime, or the user was denied access / or even not granted, the login procedure will fail. You can set the user defaults database with using the procedure sp_defaultdb or by using any gui which can administer the logins.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Thanks jens,

i solved the problem.

It was my connection string all along. it was wrong. This was the problem.

I changed the connection string from :

string strConnectionString = @."Server=.\SQLTESTING;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\TestDB.mdf;Integrated Security=SSPI";

To :

string strConnectionString = @."Server=.\SQLTESTING;Integrated Security=TRUE;Database=TestDB";

now i can use (Open) the TestDB Database from the VS code even when the SQL Server 2005 is also connected to this database.

Thanks!