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
8354 Views

Similar Articles

[PageSpeed] 48

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:

Address errors
I have 10 e-mails to send. The addresses look good but I get two errors. Sometimes they will all send but one will come back undeliverable. So I find the URL is good but the name is bad. Then with another group of 10 I hit 'SEND' and is says an address is immediately rejected. It may or may not tell me which is bad. The message is removed from the Outbox. Did I send the other 9? What is wrong with the address? What does the 'Tools' . 'Check names' do? Does it just check against the address book or does it check the validity of the addresses against internet URL...

Microsoft Money 2005 Internal Error
I keep getting the message "Microsoft Money has identified an internal error and needs to restart." When it restarts I get the same message. I've tried to go in through my .mny file and the same thing happens. Unfortunately, my backup opens, but it is old. Any ideas on how I can get into my current file?? In microsoft.public.money, Marion wrote: >I keep getting the message "Microsoft Money has identified an internal error >and needs to restart." When it restarts I get the same message. I've tried >to go in through my .mny file and the same thi...

Determining error associated with polynomial trend lines.
I have a plot that requires a polynomial trend line to the fourth power that has error associated with the y-axis values only. From this point on, I'm relying on the equation displayed by excel to further manipulate that data, which I have done and double checked with excel and it's corrrect. I'm having difficulty, however, in continuing the calculations of associated error. Is Excel capable of outputting the error associated with each coefficient of the generated trend line equation? If it is possible, how do you do it? - BJR See Help for the LINEST function. You mig...

mortgage amortization error
I made a large extra principal payment on my mortgage. MS Money produced an updated amortization schedule showing the new principal payment would be a much larger percentage of my total payment (that is, each payment would include much less interest than before). The actual allocation for the next payment on July 1 followed the original amortization schedule rather than the new one. In dollars, in June my monthly payment of $870 included $584 in principal. In July--after the large extra payment--the amortization schedule showed the July payment would be about $710 in princi...

550 Mailbox Unavailable Errors on Incoming Mail
Hi, We are getting a couple of domains having trouble sending to our domain on our SBS 2003 Server with Exchange 2003 SP1. The two domains are based in 1. Italy and 2. Nepal. This is the error they receive and we do not get their email. No address at our domain will receive email from these domains. Pls Help. I post the two examples below. They both appear to use Oulook Express but that doesn't seem to be the problem. We receive mail from all other domains just fine. Our MX record is: 10 mail.fpmt.org. [TTL=3601] IP=70.103.203.75 [TTL=3601] [US] which is the corect IP address. .7...

After install CRM 3.0 beta, logon error: Authentication Error.
I was installed Microsoft CRM 3.0 Beta(90 days trial PID)- Professional Edition But after installed CRM 3.0 beta, logon error: Authentication Error. I think the trial version should can be used. :( Any answers? "CEO" wrote: > I was installed Microsoft CRM 3.0 Beta(90 days trial PID)- Professional Edition > > But after installed CRM 3.0 beta, logon error: Authentication Error. > > I think the trial version should can be used. :( Due to NDA, questions related tot he Bea builds should be addressed in the private newsgroups. -- Matt Parks MVP - Microsoft CRM &...

Error
I want to hide the insert and format menu items when i begin my program and make them visible again before i close. This is my code for hiding them on open: Private Sub Workbook_Open() CommandBars("Worksheet Menu Bar").Controls ("Format").Visible = False CommandBars("Worksheet Menu Bar").Controls ("Insert").Visible = False End Sub ....but I'm getting Run-time error '91': Object variable or With block variable not set. Any ideas as to why? Thanks, mika. Try Applcation.CommandBars. HTH -Dave "mika." <conceptualness@ho...

#Error error in Form
Hi, I have a database in MS Access 97 which has a form consisting mainly of "Memo" fields. It is accessed by a number of people. Every now and then, if the database is left open or sometimes upon closing it and reopening it, it corrupts some of the fields on the form and displays #Error in the field instead of the actual text. You can then no longer edit any field on the form nor can you edit any field from the back-end in the table linked to the form. You cannot even copy the record directly from the table and paste it back in after removing the #Error message. As a result, you h...

Ribbon error following previous versions of my application
I have been working on an access database for while. I recently decided to edit the ribbon in order to add a new button (a spell check tool). I just added a few lines to the USysRibbons table. I did not do anything else. The button worked as expected for a while. Something eventually went wrong for some reason and I get the below error. However previous versions of my database (developed just before I edited the ribbon and consequently the USysRibbons table) no longer work. It looks that THIS ERROR IS FOLLOWING PREVIOUS VERSIONS OF MY DATABASE. The error I get is as follows: ...

Excel VBA
Hi I wrote a vba function that should return data to the excel spreadshee after querying the Oracle database. I have installed and created Microsoft ODBC connection for Oracle on my workstation. If I manually open excel workbook and navigate to Data, Get Externa Data, New Database Query, connect to the database, click on the SQ pushbutton and paste the query, click OK and hit the Return Dat pushbutton, the query return data to the spreadsheet without an issues. I recorded all the above steps in a Macro and wrote a similar vb function for extracting data for another table. I then reopened...

Error installing CRM 3.0 on SBS
Trying to Install CRM 3.0 onto Small Business Server Installed MS SQL Reporting Server 2000 with SP 2 When the CRM 3.0 tries to do its verification it is coming back with the following error message for Report Server: Setup failed to validate specified Reporting Services Report Server https://cheltenham.enate.net/ReportServer. Error: The request failed with HTTP status 401: Unauthorized. The user doing the installation is the Domain administrator. Report server is working correctly over Https. The report server is implemented on the same machine as I am trying to install CRM 3....

Synchronization Manager
hey everyone, just when you think you've heard all the SyncMgr questions, here's one for ya: i have Windows Server 2003, and Windows XP PCs, and every time i shutdown Windows XP, it Syncs no problem. But it keeps popping up saying that there were "no errors", and then i have to click "Close" before it shuts down. this is annoying, because i have to hang around while it syncs, so i can click on "Close" afterwards, instead of just shutting down and leaving. i have applied the following registry edits, so it would quit bugging me about mdb files:...

how to cancel same incoming and outgoing email, get error,
Have same email trying to send and receive at the same time. Get error, Task'mail.comcast.net - Teceiving' reported ettor (0x800421OE) : "Your mailbox in temporarily unavailable because another e-mail message is being delivered to it or another mail application is accessin it. The server responded: -ERR box locked' how do I stop this and how do I not send myself and email. Sherry JMF <Sherry JMF@discussions.microsoft.com> wrote: > Have same email trying to send and receive at the same time. Get > error, Task'mail.comcast.net - Teceiving' reported ...

CRM Workflow error: "Platform Evaluation Engine Error"
I have a rule setup that updates a custom entity. Most of the time now the rule is failing at a setup that updates a parent entity with the error: Platform Evaluation Engine Error. Workflow could not retrieve information to perform the 'update' action. Again, rerunning the rule manually and the error doesn't occur. Thanks! ...

Error in function: dAddPortMonitor, Error code, 126
I ran into this error today installing FRx 6.7 on a Vista workstation Error in function: dAddPortMonitor, Error code, 126 The only resolution I found was a Google search. The recommendation was to start by editing the registry. http://www.blackice.com/forum/viewtopic.php?t=438&highlight=126 Has anyone run into this situation? -- RobertShap Robert, There is a decent article that explains error code 126 (a .dll error) here: http://flashexperiments.insh-allah.com/ApacheError126.html I have also read that it is a 64-bit vs 32-bit error. If your Vista machine is 64-bi...

error in import outlook 2003 #3
Dear Sir or Madam Please be inform i could not import my *.pst file in my outlook 2003. however, i tired your way but it was not useful for me. and i recived below massage again: the file G:\Backup\Outlook.pst is not a personal floders file. I would appricated if you could help me in this matter. I look forward to hearing from ou best regards. Sam ...

importing
Hi, i'm importing contacts from a CSV file. we have 77 contacts to import. I've adjusted everything to match - all clomn heads get recognized automaticcally, i map the values for the adres type correctly, then i import. 61 succes, 16 failed. Ok, i check the error report, and got the message i feared... (i'm using dutch CRM so rough translation) The number of columns does not match the number of columns in the headline WHAT??? Last time i got this report on another import no one could find any errors in the rules in CSV. Only 7 errors, so i imported EXACTLY the same data ...

Sub error
I have created this macro but everytime I run it the Columns it hides is incorrect. It hides FGHIJK it should only hide GHI. Can anyone tell me why this is happening? Thank you for your help. Sub Done_Changes() ' ' Done_Changes Macro ' ' Range("G11:H50").Select Selection.Locked = True Selection.FormulaHidden = False ActiveWindow.SmallScroll Down:=42 Range("G54:H58").Select Selection.Locked = True Selection.FormulaHidden = False ActiveWindow.SmallScroll Down:=9 Range("G62:H74").Select Selection.Locked = T...

VB error
Hi, I have the code below that will create a new worksheet and rename it after todays date. However if the user trys to create a log again , ie for the same day, I get this error:- Run-time error 1004 cannot rename a sheet to the same name as another sheet, a referenced object or a workbook referenced by visual basic. Oh course what I want is a msg box advising the user that one already exsists ans asking if they wanna view it. the code I have Sub Create_log() Application.ScreenUpdating = False With Sheets("Log master") .Visible = True .Copy After:=She...

Error 36884 from source Schannel in System EventLog
On two servers in EventLog (System) are next new records: /------------------------------------8<----------------------------------\ Event Type: Error Event Source: Schannel Event Category: None Event ID: 36884 Date: 20.11.09 Time: 21:09:05 User: N/A Computer: CTX4 Description: The certificate received from the remote server does not contain the expected name. It is therefore not possible to determine whether we are connecting to the correct server. The server name we were expecting is HSDC01.nerv.cz. The SSL connection request has failed. The attached data contains th...

GDI.exe error in Publisher with HP printer
Printing from Publisher 2002 to a HP 960c deskjet. I keep getting "Mspub has caused an error in GDI.exe". I reinstalled new print drives. Does anyone know how to fix it? What is wrong? I dont' have the BDDenver font installed either! What version Windows? This is a fix for Windows 98 WD2000: IPF in Gdi.exe When Opening, Printing, or Repaginating Large Document http://support.microsoft.com/default.aspx?scid=kb;EN-US;220460 It is not Publisher specific but it maybe helpful -- Mary Sauer MS MVP http://office.microsoft.com/clipart/ http://www.mvps.org/msauer/getting_started.htm ...

Public Folder replication error
when I click on a public folder and goto properties in ESM I get this error The operation Failed because of an HTTP error 501 (Not Implemented) ID no: c1020af6 now I followed this http://support.microsoft.com/default.aspx?scid=kb;en- us;839744 and it does not fix the problem need some help so I Can remove my first exchange Server from the site ...

Runtime error!!!!!!
Hello, When I make click him in the button of new to do a new email appears to me the following error: Visual Microsoft C++ Runtime Library Program: Drive:\PROGRA....\Office\Outlook.exe Abnormal program termination I do click in OK and outlook is closed. Operating system Windows 2000 Server SP3 MS Office 2000, with Outlook 2000 without MR.. Thanks ...

Pivot Table Error message
I have created a pivot table in an Excel workbook. When I open the work book, I can a message telling me that there are errors that Excel has repaired and to save the workbook. There is also a log file that is as follows: Microsoft Excel File Repair Log Errors were detected in file 'C:\projects\marketing requests.xls' The following is a list of repairs: Repairs were made to PivotTable report 'PivotTable1' on '[marketing requests.xls]MarketingAnalysis'. but the message keeps happening each time I open the workbook, whether I choose to replace the cells or save...

Error Code 646 when updating?
Today (12/01/09), I tried to update Windows Vista. As a result, I got a message like this: "Some updates were not installed Failed: 6 updates Errors found: Code 646 Windows Update encountered an unknown error. I can't update my windows... How to solve this problem? "windy" wrote: > Today (12/01/09), I tried to update Windows Vista. As a result, I got a > message like this: > > "Some updates were not installed > > Failed: 6 updates > > Errors found: > Code 646 Windows Update encountered an unknown error. &...