SQLServer Error: 802, 701, 733

SQL Server 2008 on Win2008 R2 x64 Server, 8 GB RAM - running virtualized on 
VMWare ES-Server

Every sunday some maintainance jobs are scheduled to run. Rebuilding indices 
and statistics fail with messages:

[298] SQLServer Error: 701, There is insufficient system memory in resource 
pool 'internal' to run this query. [SQLSTATE 42000]
....
[298] SQLServer Error: 802, There is insufficient memory available in the 
buffer pool. [SQLSTATE 42000] (SaveAllSchedules)


After this the server is no longer reachable:
[298] SQLServer Error: 773, SQL Server Network Interfaces: The requested 
security package does not exist [SQLSTATE 08001]
....
[298] SQLServer Error: 773, A network-related or instance-specific error has 
occurred while establishing a connection to SQL Server. Server is not found 
or not accessible. Check if instance name is correct and if SQL Server is 
configured to allow remote connections. For more information see SQL Server 
Books Online. [SQLSTATE 08001]

A reboot of the server solves the situation, however the maintainance tasks 
are not completed.

Any idea, what�s going on?

Thanks for any assistance,
Bernd Maierhofer
www.nutritional-software.at 


0
Bernd
8/3/2010 6:54:45 AM
sqlserver.server 1327 articles. 0 followers. Follow

18 Replies
12274 Views

Similar Articles

[PageSpeed] 35

Bernd Maierhofer (bernd.maierhofer@dato.at) writes:
> SQL Server 2008 on Win2008 R2 x64 Server, 8 GB RAM - running virtualized
> on VMWare ES-Server 
> 
> Every sunday some maintainance jobs are scheduled to run. Rebuilding
> indices and statistics fail with messages: 
> 
> [298] SQLServer Error: 701, There is insufficient system memory in
> resource pool 'internal' to run this query. [SQLSTATE 42000] 
> ...
> [298] SQLServer Error: 802, There is insufficient memory available in the 
> buffer pool. [SQLSTATE 42000] (SaveAllSchedules)
> 
> 
> After this the server is no longer reachable:
> [298] SQLServer Error: 773, SQL Server Network Interfaces: The requested 
> security package does not exist [SQLSTATE 08001]
> ...
> [298] SQLServer Error: 773, A network-related or instance-specific error
> has occurred while establishing a connection to SQL Server. Server is
> not found or not accessible. Check if instance name is correct and if
> SQL Server is configured to allow remote connections. For more
> information see SQL  Server 
> Books Online. [SQLSTATE 08001]
> 
> A reboot of the server solves the situation, however the maintainance
> tasks are not completed. 
 
I don't have much idea of what is going on, but what if you reboot the 
server *before* the maintenance job? That may give a clue of whether
the maintenance job itself is causing the problem, or if it something that 
adds up with the sins of the past week.

If I am to make a guess, you have a memory leak because of an extended
stored procedure, OPENXML or similar.

What is in those maintenance job up the job that fails?


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
8/3/2010 7:10:12 PM
Rebuiding indices and statistics.

The DB is abeout 30 GB having 2 Tables containing most of the data as well 
as most indices.

Is it possible that the rebuild of the indices is done in one transaction, 
and separating it into smaller peaces would succeed? But in the past this 
was rather a problem with space and not with memory.

Maybe a service allocationg memory without releasing?

tx Bernd

"Erland Sommarskog" <esquel@sommarskog.se> schrieb im Newsbeitrag 
news:Xns9DC9D75ABAE88Yazorman@127.0.0.1...
> Bernd Maierhofer (bernd.maierhofer@dato.at) writes:
>> SQL Server 2008 on Win2008 R2 x64 Server, 8 GB RAM - running virtualized
>> on VMWare ES-Server
>>
>> Every sunday some maintainance jobs are scheduled to run. Rebuilding
>> indices and statistics fail with messages:
>>
>> [298] SQLServer Error: 701, There is insufficient system memory in
>> resource pool 'internal' to run this query. [SQLSTATE 42000]
>> ...
>> [298] SQLServer Error: 802, There is insufficient memory available in the
>> buffer pool. [SQLSTATE 42000] (SaveAllSchedules)
>>
>>
>> After this the server is no longer reachable:
>> [298] SQLServer Error: 773, SQL Server Network Interfaces: The requested
>> security package does not exist [SQLSTATE 08001]
>> ...
>> [298] SQLServer Error: 773, A network-related or instance-specific error
>> has occurred while establishing a connection to SQL Server. Server is
>> not found or not accessible. Check if instance name is correct and if
>> SQL Server is configured to allow remote connections. For more
>> information see SQL  Server
>> Books Online. [SQLSTATE 08001]
>>
>> A reboot of the server solves the situation, however the maintainance
>> tasks are not completed.
>
> I don't have much idea of what is going on, but what if you reboot the
> server *before* the maintenance job? That may give a clue of whether
> the maintenance job itself is causing the problem, or if it something that
> adds up with the sins of the past week.
>
> If I am to make a guess, you have a memory leak because of an extended
> stored procedure, OPENXML or similar.
>
> What is in those maintenance job up the job that fails?
>
>
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: 
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> 


0
Bernd
8/4/2010 5:45:21 AM
Maybe this
http://support.microsoft.com/kb/982854/en-us

is the cause. I�m investigating.

B.



"Bernd Maierhofer" <bernd.maierhofer@dato.at> schrieb im Newsbeitrag 
news:O8ws7g5MLHA.1624@TK2MSFTNGP06.phx.gbl...
> Rebuiding indices and statistics.
>
> The DB is abeout 30 GB having 2 Tables containing most of the data as well 
> as most indices.
>
> Is it possible that the rebuild of the indices is done in one transaction, 
> and separating it into smaller peaces would succeed? But in the past this 
> was rather a problem with space and not with memory.
>
> Maybe a service allocationg memory without releasing?
>
> tx Bernd
>
> "Erland Sommarskog" <esquel@sommarskog.se> schrieb im Newsbeitrag 
> news:Xns9DC9D75ABAE88Yazorman@127.0.0.1...
>> Bernd Maierhofer (bernd.maierhofer@dato.at) writes:
>>> SQL Server 2008 on Win2008 R2 x64 Server, 8 GB RAM - running virtualized
>>> on VMWare ES-Server
>>>
>>> Every sunday some maintainance jobs are scheduled to run. Rebuilding
>>> indices and statistics fail with messages:
>>>
>>> [298] SQLServer Error: 701, There is insufficient system memory in
>>> resource pool 'internal' to run this query. [SQLSTATE 42000]
>>> ...
>>> [298] SQLServer Error: 802, There is insufficient memory available in 
>>> the
>>> buffer pool. [SQLSTATE 42000] (SaveAllSchedules)
>>>
>>>
>>> After this the server is no longer reachable:
>>> [298] SQLServer Error: 773, SQL Server Network Interfaces: The requested
>>> security package does not exist [SQLSTATE 08001]
>>> ...
>>> [298] SQLServer Error: 773, A network-related or instance-specific error
>>> has occurred while establishing a connection to SQL Server. Server is
>>> not found or not accessible. Check if instance name is correct and if
>>> SQL Server is configured to allow remote connections. For more
>>> information see SQL  Server
>>> Books Online. [SQLSTATE 08001]
>>>
>>> A reboot of the server solves the situation, however the maintainance
>>> tasks are not completed.
>>
>> I don't have much idea of what is going on, but what if you reboot the
>> server *before* the maintenance job? That may give a clue of whether
>> the maintenance job itself is causing the problem, or if it something 
>> that
>> adds up with the sins of the past week.
>>
>> If I am to make a guess, you have a memory leak because of an extended
>> stored procedure, OPENXML or similar.
>>
>> What is in those maintenance job up the job that fails?
>>
>>
>> -- 
>> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>>
>> Links for SQL Server Books Online:
>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
>> SQL 2000: 
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>
>
> 


0
Bernd
8/4/2010 6:14:29 AM
Bernd Maierhofer (bernd.maierhofer@dato.at) writes:
> Maybe this
> http://support.microsoft.com/kb/982854/en-us
> 
> is the cause. I�m investigating.
 
Does not seem to be related to indexing, but if there are fulltext 
queries running simultaneous, the reindexing could be the victim.

> Is it possible that the rebuild of the indices is done in one transaction, 
> and separating it into smaller peaces would succeed? But in the past this 
> was rather a problem with space and not with memory.

No, this is very unlikely. Your database is 30 GB which is fairly modest.



-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
8/4/2010 7:39:18 PM
> Does not seem to be related to indexing, but if there are fulltext
> queries running simultaneous, the reindexing could be the victim.
Not that I know of. But I will have a look at this.

> No, this is very unlikely. Your database is 30 GB which is fairly modest.
30 GB is not one of the largest DBs, but "fairly modest" is very dependent 
of your point of view. AFAIK 30 GB ranges in the top 20% of MSSQL database 
sizes.

tx Bernd


0
Bernd
8/5/2010 7:43:55 AM
Bernd Maierhofer (bernd.maierhofer@dato.at) writes:
> 30 GB is not one of the largest DBs, but "fairly modest" is very dependent 
> of your point of view. AFAIK 30 GB ranges in the top 20% of MSSQL database 
> sizes.
 
Depends on what it is 20% of. Maybe 80% of all SQL Server databases 
are less than 30GB. However, I don't think that 80% of the data that
lives in SQL Server databases lives in databases less than 30 GB.

I believe most of the production databases of the system I work with are
> 30 GB, the biggest is some 700-800 GB by now.

In any case, the point is that SQL Server does not choke because you
reindex a 30GB database. So I suspect that there is something else going 
on at your server.

There are a couple of DMVs to investigate memory usage, but it is not
entirely easy to grasp. And if the memory eater is a black hole like an
extended stored procedure, it is even more difficult to find.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
8/5/2010 6:51:21 PM
> Depends on what it is 20% of. Maybe 80% of all SQL Server databases
> are less than 30GB. However, I don't think that 80% of the data that
> lives in SQL Server databases lives in databases less than 30 GB.
:-) Well said!


> In any case, the point is that SQL Server does not choke because you
> reindex a 30GB database. So I suspect that there is something else going
> on at your server.
This is, what I believin , too. However, this is a plain new server, and I 
did nothing but assembling a maintainance job by clicking the options. 
Scheduled for sunday. And on sunday - peng.

There are no SP running.

I disabled the jobs and will see what happens.

Thanks for your input!

Bernd 


0
Bernd
8/6/2010 6:23:47 AM
Bernd Maierhofer (bernd.maierhofer@dato.at) writes:
> This is, what I believin , too. However, this is a plain new server, and I 
> did nothing but assembling a maintainance job by clicking the options. 
> Scheduled for sunday. And on sunday - peng.
 
That's an interesting piece of information. That makes it less
likely there is something within SQL Server stealing the memory.

Have you checked the SQL Server error log, if there are any interesting
messages, like about part of the memory being swapped out? Or anything
else that explains why SQL Server stops responding.

Do you know if the service account has the permission Lock Pages in 
Memory? And exactly which edition and version (from @@version) do
you have?

Also, it could be interesting to schedule this query to run every 10 
minuutes or so, and insert the data into a table:

SELECT physical_memory_in_use_kb,locked_page_allocations_kb,
       page_fault_count, memory_utilization_percentage,
       available_commit_limit_kb, process_physical_memory_low,
       process_virtual_memory_low
FROM sys.dm_os_process_memory;



-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
8/6/2010 9:07:43 PM
Sorry for not answering - I was out of office.

I�ll come back on this later.

tx Bernd

"Erland Sommarskog" <esquel@sommarskog.se> schrieb im Newsbeitrag 
news:Xns9DCCEB473AC91Yazorman@127.0.0.1...
> Bernd Maierhofer (bernd.maierhofer@dato.at) writes:
>> This is, what I believin , too. However, this is a plain new server, and 
>> I
>> did nothing but assembling a maintainance job by clicking the options.
>> Scheduled for sunday. And on sunday - peng.
>
> That's an interesting piece of information. That makes it less
> likely there is something within SQL Server stealing the memory.
>
> Have you checked the SQL Server error log, if there are any interesting
> messages, like about part of the memory being swapped out? Or anything
> else that explains why SQL Server stops responding.
>
> Do you know if the service account has the permission Lock Pages in
> Memory? And exactly which edition and version (from @@version) do
> you have?
>
> Also, it could be interesting to schedule this query to run every 10
> minuutes or so, and insert the data into a table:
>
> SELECT physical_memory_in_use_kb,locked_page_allocations_kb,
>       page_fault_count, memory_utilization_percentage,
>       available_commit_limit_kb, process_physical_memory_low,
>       process_virtual_memory_low
> FROM sys.dm_os_process_memory;
>
>
>
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: 
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> 


0
Bernd
8/23/2010 7:25:42 AM
Some facts:

Running in a virtualized environment (VMWare ESX Server)
4 processor 64 Bit Intel Xeon with8 GB
Windows Server 2008 R2

select @@version reports:

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)   Mar 29 2009 10:11:52 
Copyright (c) 1988-2008 Microsoft Corporation  Standard Edition (64-bit) on 
Windows NT 6.1 <X64> (Build 7600: ) (VM)

SELECT physical_memory_in_use_kb,locked_page_allocations_kb,
page_fault_count, memory_utilization_percentage,
available_commit_limit_kb, process_physical_memory_low,
process_virtual_memory_low
FROM sys.dm_os_process_memory; reports:

physical_memory_in_use_kb locked_page_allocations_kb page_fault_count 
memory_utilization_percentage available_commit_limit_kb 
process_physical_memory_low process_virtual_memory_low
7325664 0 7719678 98 7805304 0 0

SQLServer service runs as LocalSystem

We were able to track down when the problem starts. The logs around this 
contain these entries:

.... infos about memory
02:29:31 spid51 Failes allocate pages FAIL_PAGE_ALLOCATION1
02.59:31 spid51 Memory manger ....  VM reserved 8484080 VM Commited 8465792
....
02:59:34 spid51 Error 701 /17 / 123
02:59:34 spid51 There is insufficient memory in resource pool internal to 
run this query
....

At the moment we belive that a service tries to allocate memory and fails 
doing so, but is not releasing the already allocated memory - so blocking 
further connections to the server. But this does not really make sense.

Thanks for every input.

Bernd



"Bernd Maierhofer" <bernd.maierhofer@dato.at> schrieb im Newsbeitrag 
news:uQoBmRpQLHA.456@TK2MSFTNGP06.phx.gbl...
> Sorry for not answering - I was out of office.
>
> I�ll come back on this later.
>
> tx Bernd
>
> "Erland Sommarskog" <esquel@sommarskog.se> schrieb im Newsbeitrag 
> news:Xns9DCCEB473AC91Yazorman@127.0.0.1...
>> Bernd Maierhofer (bernd.maierhofer@dato.at) writes:
>>> This is, what I believin , too. However, this is a plain new server, and 
>>> I
>>> did nothing but assembling a maintainance job by clicking the options.
>>> Scheduled for sunday. And on sunday - peng.
>>
>> That's an interesting piece of information. That makes it less
>> likely there is something within SQL Server stealing the memory.
>>
>> Have you checked the SQL Server error log, if there are any interesting
>> messages, like about part of the memory being swapped out? Or anything
>> else that explains why SQL Server stops responding.
>>
>> Do you know if the service account has the permission Lock Pages in
>> Memory? And exactly which edition and version (from @@version) do
>> you have?
>>
>> Also, it could be interesting to schedule this query to run every 10
>> minuutes or so, and insert the data into a table:
>>
>> SELECT physical_memory_in_use_kb,locked_page_allocations_kb,
>>       page_fault_count, memory_utilization_percentage,
>>       available_commit_limit_kb, process_physical_memory_low,
>>       process_virtual_memory_low
>> FROM sys.dm_os_process_memory;
>>
>>
>>
>> -- 
>> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>>
>> Links for SQL Server Books Online:
>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
>> SQL 2000: 
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>
>
> 


0
Bernd
8/23/2010 10:59:19 AM
Bernd Maierhofer (bernd.maierhofer@dato.at) writes:
> We were able to track down when the problem starts. The logs around this 
> contain these entries:
> 
> ... infos about memory
> 02:29:31 spid51 Failes allocate pages FAIL_PAGE_ALLOCATION1
> 02.59:31 spid51 Memory manger ....  VM reserved 8484080 VM Commited 
> 8465792
> ...
> 02:59:34 spid51 Error 701 /17 / 123
> 02:59:34 spid51 There is insufficient memory in resource pool internal to 
> run this query
> ...
> 
> At the moment we belive that a service tries to allocate memory and fails 
> doing so, but is not releasing the already allocated memory - so blocking 
> further connections to the server. But this does not really make sense.
 
Given that you say this a new server, and there is nothing on it, it does
indeed sound strange.

I have two recommendations:

1) Get hold of at least Cumulative Update 2 for SQL 2008 SP1. This should
   enable "Lock pages in memory" also for Standard Editions. (This used to 
   be Enterprise Edition only.) If this is a new server not yet in 
   production, the best may be to get the CTP of SQL 2008 SP2.

2) Set Max Server Memory to 6000, that is to 6GB. It's usually wise to
   leave space to the OS.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
8/23/2010 6:10:22 PM
> Given that you say this a new server, and there is nothing on it, it does
> indeed sound strange.

"Nothing on it" is not entirely correct. There are some services, accessing 
the SQL Server, wich are provided by us. They allocate memory and are 
supposed to release it. They do once a day their job.

But even IF a service allocates memory, the sqlserver is supposed to give it 
to the service, isn�it?

> I have two recommendations:
>
> 1) Get hold of at least Cumulative Update 2 for SQL 2008 SP1. This should
>   enable "Lock pages in memory" also for Standard Editions. (This used to
>   be Enterprise Edition only.) If this is a new server not yet in
>   production, the best may be to get the CTP of SQL 2008 SP2.

Ok, tx.

> 2) Set Max Server Memory to 6000, that is to 6GB. It's usually wise to
>   leave space to the OS.

This is what I already did. But again - sqlserver is supposed to release 
memory as it is needed, isn��t it?

Logging of memeory as you suggested:
logdate physical_memory_in_use_kb locked_page_allocations_kb 
page_fault_count memory_utilization_percentage available_commit_limit_kb 
process_physical_memory_low process_virtual_memory_low

2010-08-24 09:15:01.013 7327452 0 2272884 100 13310292 0 0
....
2010-08-24 08:35:00.900 7181348 0 2137792 100 13491724 0 0
....
2010-08-24 06:55:00.340 6442540 0 1748722 100 14231676 0 0
....
2010-08-24 05:25:00.910 5716024 0 1466485 100 14952304 0 0
....
2010-08-24 04:20:00.373 3610852 0 932039 100 17063432 0 0
....
2010-08-24 03:45:00.153 203392 0 77447 100 20480740 0 0
....
2010-08-24 02:35:00.590 201844 0 72155 100 20482716 0 0
....
2010-08-23 23:55:00.187 192608 0 58525 100 20338060 0 0
....
2010-08-23 22:45:00.197 167076 0 42454 100 20508368 0 0
.... <<<------------ Server rebooted
2010-08-23 22:40:00.760 7180384 0 8227263 99 16766868 0 0
....
2010-08-23 21:15:00.853 7244128 0 8221198 100 16488424 0 0
.... <<<----------- Service stops
2010-08-23 21:10:00.863 7244108 0 8220862 100 16459864 0 0
.... <<----------- Service runs
2010-08-23 20:40:00.500 7470260 0 8212730 100 16254636 0 0
.... <<<-------------- Service starts
2010-08-23 18:55:00.570 7128380 0 8112073 99 16655556 0 0
....
2010-08-23 13:12:30.677 7376536 0 7763188 99 7942032 0 0



I appreciate your assistance.

tx Bernd 


0
Bernd
8/24/2010 7:25:40 AM
Bernd Maierhofer (bernd.maierhofer@dato.at) writes:
> "Nothing on it" is not entirely correct. There are some services,
> accessing the SQL Server, wich are provided by us. They allocate memory
> and are supposed to release it. They do once a day their job. 
> 
> But even IF a service allocates memory, the sqlserver is supposed to
> give it to the service, isn�it? 

More precisely if there is memory pressure, SQL Server should yield 
memory.

But that depends on what for memory has been allocated. If memory has
been allocated for the buffer pool, SQL Server will release the memory.
But if memory has been allocated for other purposes, SQL Server may
not be able to release it.

Everything points to that you have a memory leak somewhere. Here are a 
couple of possible culprits:

o  Extended stored procedures.
o  Linked servers.
o  sp_OAxxxx
o  Unsafe assemblies.
o  OPENXML where you fail to call sp_xml_removedocument.

Data to investigate is the output from DBCC MEMORYSTATUS and the DMVs 
sys.dm_os_memory_clerks and sys.dm_os_memory_buffer. Try to google for SQL
Server MVP Glenn Berry to see if he has a blog. I know he has a couple of
good queries to examine memory usage. 



-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
0
Erland
8/24/2010 10:45:56 AM
> Everything points to that you have a memory leak somewhere.
I am not sure if this leak has to be inside mssql server (SP, ..) or if it 
can be an misbehaving service. But to now I did not found any. :-(

> Here are a
> couple of possible culprits:
>
> o  Extended stored procedures.
None, but initially installed.
> o  Linked servers.
None.
> o  sp_OAxxxx
None.
> o  Unsafe assemblies.
None.
> o  OPENXML where you fail to call sp_xml_removedocument.
None.

> Data to investigate is the output from DBCC MEMORYSTATUS and the DMVs
> sys.dm_os_memory_clerks and sys.dm_os_memory_buffer. Try to google for SQL
> Server MVP Glenn Berry to see if he has a blog. I know he has a couple of
> good queries to examine memory usage.
Tx.

If there is a memory leak, I should find some memory related values to 
constantly increase/decrease. Which counters in performance monitor or 
inside MSSQL would you recommend to monitor?

BM 


0
Bernd
8/24/2010 11:54:51 AM
Bernd Maierhofer (bernd.maierhofer@dato.at) writes:
> I am not sure if this leak has to be inside mssql server (SP, ..) or if it 
> can be an misbehaving service. But to now I did not found any. :-(

The services are separate processes from SQL Server, so they cannot take 
away memory from SQL Server's address space. However, you should configure 
Max memory for SQL Server with the services in regard. If they need 2GB of
memory, you should set SQL Server to use at max 4GB, maybe 4.5GB.
 
> If there is a memory leak, I should find some memory related values to 
> constantly increase/decrease. Which counters in performance monitor or 
> inside MSSQL would you recommend to monitor?
 
I would be more interested to see the output from DBCC MEMORYSTATUS and the
DMVs I related. In short, you need to investigate what SQL Server uses 
the memory to.




-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
0
Erland
8/24/2010 1:58:45 PM
This is just to give a follow up:

The only thing I did up to now is setting the SQLServers MaxMem to 6 GB and 
increasing virtual memory. Now everything works as intendened. This is good.

However I still have no explanation. This bad.

Thanks again for your assistance!

Bernd

"Erland Sommarskog" <esquel@sommarskog.se> schrieb im Newsbeitrag 
news:Xns9DDEA28CEE535Yazorman@127.0.0.1...
> Bernd Maierhofer (bernd.maierhofer@dato.at) writes:
>> I am not sure if this leak has to be inside mssql server (SP, ..) or if 
>> it
>> can be an misbehaving service. But to now I did not found any. :-(
>
> The services are separate processes from SQL Server, so they cannot take
> away memory from SQL Server's address space. However, you should configure
> Max memory for SQL Server with the services in regard. If they need 2GB of
> memory, you should set SQL Server to use at max 4GB, maybe 4.5GB.
>
>> If there is a memory leak, I should find some memory related values to
>> constantly increase/decrease. Which counters in performance monitor or
>> inside MSSQL would you recommend to monitor?
>
> I would be more interested to see the output from DBCC MEMORYSTATUS and 
> the
> DMVs I related. In short, you need to investigate what SQL Server uses
> the memory to.
>
>
>
>
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx 


0
Bernd
8/27/2010 5:49:55 AM
Bernd Maierhofer (bernd.maierhofer@dato.at) writes:
> The only thing I did up to now is setting the SQLServers MaxMem to 6 GB
> and increasing virtual memory. Now everything works as intendened. This
> is good. 
 
Apparently SQL Server tried to bite off more than it could chew.

One thing to wacth for with VMware is the baloon driver. VMware permits
you to give the virtual machines more memory in total than the host actually
has. To have guests shrink their working sets, it uses the baloon driver,
which just eats memory, forcing everything else on the machine to yield.
This is why Lock Pages in Memory is good.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
8/27/2010 9:11:35 PM
> One thing to wacth for with VMware is the baloon driver. VMware permits
> you to give the virtual machines more memory in total than the host 
> actually
> has. To have guests shrink their working sets, it uses the baloon driver,
> which just eats memory, forcing everything else on the machine to yield.
> This is why Lock Pages in Memory is good.

I see. Thank�s.

Bernd 


0
Bernd
8/30/2010 3:37:19 PM
Reply:

Similar Artilces:

Error message when deleting a customer
When trying to delete an old customer in QuickSell 2000, I get the error message "this customer cannot be deleted because this customer has open work orders, backorders, quotes or transactions on hold". I've looked in the old manual and tried to do some searches on the internet but I have not come up with any solution. Thank You Just as it says, there is an open order or transaction on hold for that customer. Do a "recall" on all 3 types of orders, and check the holds - there's probably something in there for them. Note you might not wish you delete custo...

Error creating dates
I can't create new dates in Microsoft Outlook Calendary When i do double click nothing happens... Any idea? Thanks in advance. Reboot your computer. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, Virgulilla asked: | I can't create new dates in Microsoft Outlook Calendary | | When i do double click nothing happens... | Any idea? | | Thanks in advance. ...

DCOM ERROR
DCOM got error "Overlapped I/O operation is in progress. " and was unable to logon RECORD\IUSR_MAIL in order to run the server: {3D14228D-FBE1-11D0-995D-00C04FD919C1} What I DO? ...

Exchange user remote keeps geting 503 errors
I've got a sales rep out on vacation - he brought his office laptop with him (outlook 2003). We have Exchange 2003 back in the office and before he left we set his earthlink account (POP) to be his default. He's authenticating to the POP server to sent out but keeps gettings on 4/26/2006 5:44 PM 503 No recipient(s). errors. Any thoughts you may have out there? Thanks, Brad What smtp addresses are you mailboxes stamped with? Any non-routable like user@domain.local? Nue "Brad Palmer" <brad_palmer28@hotmail.com> wrote in message news:mjW3g.76782...

money 2005 install error
I have two account on xp pro sp2, Administrator and my user account which is an Administrator account. Before the Money 2005 Deluxe install starts, it alerts with an error that I must sign on with power user or administrator privileges??? I have logged on directly as Administrator and still get the error. Created a new admin account, same error?? Any ideas?? well, who would have though microsoft hard coded the "Program Files" directory into the install checks? Since I have my program files on a different partition I had to create a dummy "Program Files" folder on t...

Subform causing OLE error
I have a subform whose function is to associate a contact with the record on the main form. This works if editing an existing contact/project association, but I get an OLE error when trying to add a new contact. What I <think> the problem is is that there is nothing that adds a new record to my ContactProject table. How do I add a new record when selecting the dropdown from an existing list? On a related note, how do I delete a record when I no longer have a Project/Contact relationship? ...

HQ Client Errors everytime 401 is run
Hi Thia I have a problem with HQ client at one of the stores freezing and then giving a "just in time debugging" everytime a 401 is run. Any ideas what may be causing this or how to fix it? -- Cheers Topi This is a multi-part message in MIME format. ------=_NextPart_000_006B_01CA985B.75A8E7D0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Topi, At the store, try running a Database re-index and then a check in SO = Admin. Try running a re-install of Client in Add/Remove, making sure that you = are runnin...

ODBC error with no help
I use Excel to download data from DB2 via an Access database query. Excel calls a query in an Access database which gets data from an OBDC connection to a DB2 database on an AS/400. When the query is a straight SELECT query against one file, it works fine. But when I change the query to a pass-through query so that the work is done on the AS/400 against live files; Excel comes back with: Reserved Error (|); there is no message for this error. Access works fine and can run the queries. It's just that Excel bombs. Any suggestions? "Norm" wrote ... > I use Excel t...

Dynazip error and HQ server errors
Hi, Was wondering if anybody encountered Dynazip error in HQ client during data uploads? Also, sometimes HQ server will have connections that stayed on even after the HQ client disconnects, I will have to manually close it by right-clicking on it. Does anybody encounter this before? If any experts out there knows anything about this, please reply. Thanks. Raymond Raymond, I would recommend updating the to the latest version of RMS. 1.3 was released around Dec. 1 and most of the changes had to do with making HQ much more stable. -- = "Raymond Cheok" <raymondcheok@fare...

To many fields defined error message
I am currently using access 2003 and when I am attempting to bring multiple queries together into one... I am getting a To many fields defind error message. Below is my SQL that I currently have. I am going to want to add another 30 fields from 3 other queries to this one as well. SELECT [Employee Hierarchy - Current Month].[Performance Year], [Employee Hierarchy - Current Month].[Performance Month], [Employee Hierarchy - Current Month].ATTUID, [Employee Hierarchy - Current Month].Skill, [Employee Hierarchy - Current Month].Ramp, [TC-All-Weeks].[TC-Week1-Step2].[Trans and Co...

ERROR: Combining GL Accounts with Professional Services Tools Libr
Accounts modify okay; but during a combine single account or an ‘import’ multiple account combine, the following appears and terminates the session: [Microsoft][ODBC SQL Server Driver][SQL Server]Can not alter replicated object ‘[dbo].[RM00101]’to ‘with encrypted’.[Microsoft][ODBC SQL Server Driver][SQL Server]The transaction ended in the trigger. The batch has been aborted. Has anyone ever experienced this and found a solution? Thanks in advance… Dirk ...

DLookup error
I'm trying to reference a control on a subreport in a DLookup formula. This is my formula =(DLookUp("[txtletter]","[tblComplaintnbr]","[txtYear]=Reports![rptcomplaintsPage1]![rptcomplaints].[txtYear]") & [txtrefnbr]) Where rptcomplaintsPage1 is the main report and rptcomplaints is the subreport and the control txtyear is on the subreport. can anyone see where I'm going wrong? I've tried using a ! before txtyear and that doesn't work either. Thanks Tony Solved I missed out the last Report! before the control name and I've been loo...

Compile Errors with Different versions of Excel
I have a spreadsheet created in Excel 2000 that has many macros in it. When the sheet is opened in Excel 2003 a compile error occurs. I am trying to make the sheet for use by many different people at different locations. Does anyone know of any common issues that may be causing this. It is hard to duplicate a compile error, I am not at the location with the problem. Any suggestions would be appreciated. Thanks, Also, There are no Add-Ins selected, so this is not the problem. Paul "PStephens" wrote: > I have a spreadsheet created in Excel 2000 that has many macros in i...

Error Message #10
I just upgraded my Excel program to 2003. When I open the program, I ge the following message C:\ProgramFiles\MicrosoftOfficer\Office11\Library\MobileDB.XLA i missing. Incomplete Installation! However, I close that window and the program seems to work fine. How d I eliminate this message? Thanks ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com References to and suggested fixes here at google search results. Maybe find something relevant. This has cropped up lately with th...

Error at send detail report of Mailbox Management
Hi team!!! I have this error at the moment of send the report of the Mailbox Management: Event Type: Error Event Source: MSExchangeSA Event Category: Mailbox Management Event ID: 9313 Date: 13/11/2005 Time: 0:39:12 User: N/A Computer: Server Name Description: Microsoft Exchange Server Mailbox Manager failed to send the following message: Subject: The Microsoft Exchange Server Mailbox Manager on Server Name completed processing successfully. Recipient: abc@domain.com Error: c1050000 For more information, see Help and Support Center at http://go.microso...

distribution list error #2
Holly Did you ever get an answer to your "unexpected error" problem? Jim ...

Link Error: Unresolved external GetThisClass
I am writing an MDI application based generally on the CHKBOOK sample. I need to have several views of the same document. I started out creating a single view and got that working then made a copy of the first view and did a replace on the class name to create the second view. At this point the second view is still identical to the first until I get the mechanism working the way I want it. The problem is that I keep getting the following link error once the second view is added: PCR.obj : error LNK2019: unresolved external symbol "public: static struct CRuntimeClass * __stdcall CByDayVie...

Error when trying to view reports.
When a user (even a CRM Administrator) tries to access any report from reporting services they get the following error: The property 'nullable' of report parameter 'CRM_Locale' is set to false. However, the corresponding parameter (default) value is null or contains an empty string. (rsProcessingError) I can open the reporting server's site in a browser seperate from CRM and view the list of reports but I get the same error. I have tried it from multiple workstations and multiple users all logged into the domain. Any suggestions? Mike Chilson ACP, Inc. is this ...

Error report
HELP!!!!!!!!!!!!! I've been getting this warning now everytime I try to send an email from Outlook: Task 'Hotmail - Sending' reported error (0x80004005) : 'The operation failed.' Does anyone know what this means? ...

Exchange Server Event Errors
I need help troubleshooting a couple event errors with our 2000 Exhange server. Schannel (ID 36871), SMTPSVC (ID 418), and Service Control Manager (ID 7031): these errors have been happening pretty often, and we have to keep restarting the service. What can we do to correct these errors? The first can be normal if you installed an SSL cert from an external source (like Verisign). The last error is just re-interation fo the SMTP problem. For the SMTP error (418) does this resource help? http://eventid.net/display.asp?eventid=418&eventno=1085&source=smtpsvc&phase=1 "reyr...

Numeric Field Overflow Error
Hello, I have a table in my database that is liked to an excel file. The data type of every field of the linked table is 'text'. I have created another table that I will append the information to via an append query. This secondary table has the same fields and the same data types - all 'text'. When I try to run the append query, it may work one time and then error out saying, "Numeric Field Overflow." My question is - How do I fix this error so that the query will run consistently? Why does it sometimes append and othertimes give me the 'Num...

Compile error in hidden module : MenuHandling
Hi a spreadsheet that was created in excel 2000 , on opening in excel 2003 the compile error comes up. and stops all macros from running . any ideas how to fix this? I used to use this a lot and not sure if i can roll back to excel 2000 (corp. IT policies) marc marccozijn@hotmail.com I've never heard of this "menuhandling" error. But the "compile error in hidden module" means that there is an error in one of the VBA routines in a workbook's project that is protected. You could do some detective work to find the culprit. Chip Pearson has some notes on how to ...

validation error 313
When I charge an account "tender" requires a dollar value or the "ok" remains grayed out. However, when the amount is entered and ok is clicked I get the message "validation error 313--account tender is invalid because customer does not have an account" What am I doing wrong? The customer is set up in ar. Hi Sam, You need to set up Account Types (\Database\Account Types) and connect it to the particular customer(\Customer Properties\Account Information tab) you are using...also you need to define a credit limit for that customer. Tnx. \Franco E-mail: fman...

what is cannot complete operation error code 0x80040155
my clip art is gone how do I get it back You need to re-register the Ole32.dll Start, run, type in the open box regsvr32 ole32.dll If that doesn't work... You receive a "Clipart cannot complete the operation" error message when you try to insert clip art in an Office XP program or in an Office 2003 program http://support.microsoft.com/kb/889885/en-us -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com http://officebeta.iponet.net/en-us/publisher/FX100649111033.aspx "dredrhino" <dredrhino@discussions.microsoft...

Integration Manager Error Messages #2
When using Integration Manager for GP 10, I received this error message: 5: FAF0005 INTERNAL Insert Failed 0. Seconds DOC 5 ERROR: Microsoft.Dynamics.GP.eConnect: Number = 10400 Stored Procedure taCreateAssetBook : Description = If LTD Depreciation Amount (LTDDEPRAMT) is entered, Depreciation to Date (DEPRTODATE) can not be greater than the last day of the current fiscal year for this book Are there any known resolutions? Tunesia, I don't believe this issue has anything to do with IM or the eConnect adapter, but rather with the procedures followed by your accounting sta...