Return to Answer

I'm guessing the 'memory left' output you posted is from Christian Bolton's VAS usage analysis script. Is that correct? If so, you have only 17-18 MB available in the memtoleave area, which is likely to cause a problem at some point and could cause all or most of the error messages that you pasted.

This query from Jonathan Kehayias here will give you an idea of what is using memory in the memtoleave area:

SELECT type, virtual_memory_committed_kb, multi_pages_kb 
FROM sys.dm_os_memory_clerks 
WHERE virtual_memory_committed_kb > 0 OR multi_pages_kb > 0

The post also explains memtoleave well.

Whether the size of the caches you asked about is a problem depends on a number of things. Since this is a 32-bit instance, one of the more relevant things to consider is whether the allocations for those caches are allocated using the single-page allocator, which uses the buffer pool, or the multi-page allocator, which prior to SQL Server 2012 doesn't use the buffer pool and instead consumes memory in the memtoleave area. You have about 13 GB allocated to the buffer pool, so a few hundred MB allocated to this or that isn't necessarily going to cause a problem. However, a few hundred MB in the memtoleave area is enough to cause some of the errors you listed.

Kehayias' post explains these concepts as well as your options for solving the problem if it is indeed memtoleave exhaustion.

I would focus mainly on what is starving the memtoleave area, as well as what overall memory availability in Windows looks like, along with general measures of memory availability in SQL Server such as page life expectancy.

I would also consider whether the problem helps make a business case for migrating to a more recent 64-bit version of SQL Server, as this avoids memtoleave issues unless the whole server is simply running out of memory. Understanding and managing SQL Server's memory usage is significantly simpler on 64-bit builds, especially on SQL Server 2012 where all allocations go through the buffer pool. You may also want to consider applying SP4 as there are a number of known bugs in SP3.

I'm guessing the 'memory left' output you posted is from Christian Bolton's VAS usage analysis script. Is that correct? If so, you have only 17-18 MB available in the memtoleave area, which is likely to cause a problem at some point and could cause all or most of the error messages that you pasted.

This query from Jonathan Kehayias here will give you an idea of what is using memory in the memtoleave area:

SELECT type, virtual_memory_committed_kb, multi_pages_kb 
FROM sys.dm_os_memory_clerks 
WHERE virtual_memory_committed_kb > 0 OR multi_pages_kb > 0

The post also explains memtoleave well.

Whether the size of the caches you asked about is a problem depends on a number of things. Since this is a 32-bit instance, one of the more relevant things to consider is whether the allocations for those caches are allocated using the single-page allocator, which uses the buffer pool, or the multi-page allocator, which prior to SQL Server 2012 doesn't use the buffer pool and instead consumes memory in the memtoleave area. You have about 13 GB allocated to the buffer pool, so a few hundred MB allocated to this or that isn't necessarily going to cause a problem. However, a few hundred MB in the memtoleave area is enough to cause some of the errors you listed.

Kehayias' post explains these concepts as well as your options for solving the problem if it is indeed memtoleave exhaustion.

I would focus mainly on what is starving the memtoleave area, as well as what overall memory availability in Windows looks like, along with general measures of memory availability in SQL Server such as page life expectancy.

I would also consider whether the problem helps make a business case for migrating to a 64-bit version of SQL Server, as this avoids memtoleave issues unless the whole server is simply running out of memory. Understanding and managing SQL Server's memory usage is significantly simpler on 64-bit builds, especially on SQL Server 2012 where all allocations go through the buffer pool. You may also want to consider applying SP4 as there are a number of known bugs in SP3.

I'm guessing the 'memory left' output you posted is from Christian Bolton's VAS usage analysis script. Is that correct? If so, you have only 17-18 MB available in the memtoleave area, which is likely to cause a problem at some point and could cause all or most of the error messages that you pasted.

This query from Jonathan Kehayias here will give you an idea of what is using memory in the memtoleave area:

SELECT type, virtual_memory_committed_kb, multi_pages_kb 
FROM sys.dm_os_memory_clerks 
WHERE virtual_memory_committed_kb > 0 OR multi_pages_kb > 0

The post also explains memtoleave well.

Whether the size of the caches you asked about is a problem depends on a number of things. Since this is a 32-bit instance, one of the more relevant things to consider is whether the allocations for those caches are allocated using the single-page allocator, which uses the buffer pool, or the multi-page allocator, which prior to SQL Server 2012 doesn't use the buffer pool and instead consumes memory in the memtoleave area. You have about 13 GB allocated to the buffer pool, so a few hundred MB allocated to this or that isn't necessarily going to cause a problem. However, a few hundred MB in the memtoleave area is enough to cause some of the errors you listed.

Kehayias' post explains these concepts as well as your options for solving the problem if it is indeed memtoleave exhaustion.

I would focus mainly on what is starving the memtoleave area, as well as what overall memory availability in Windows looks like, along with general measures of memory availability in SQL Server such as page life expectancy.

I would also consider whether the problem helps make a business case for migrating to a more recent 64-bit version of SQL Server. Understanding and managing SQL Server's memory usage is significantly simpler on 64-bit builds, especially on SQL Server 2012 where all allocations go through the buffer pool. You may also want to consider applying SP4 as there are a number of known bugs in SP3.

I'm guessing the 'memory left' output you posted is from Christian Bolton's VAS usage analysis script. Is that correct? If so, you have only 17-18 MB available in the memtoleave area, which is likely to cause a problem at some point and could cause all or most of the error messages that you pasted.

This query from Jonathan Kehayias here will give you an idea of what is using memory in the memtoleave area:

SELECT type, virtual_memory_committed_kb, multi_pages_kb 
FROM sys.dm_os_memory_clerks 
WHERE virtual_memory_committed_kb > 0 OR multi_pages_kb > 0

The post also explains memtoleave well.

Whether the size of the caches you asked about is a problem depends on a number of things. Since this is a 32-bit instance, one of the more relevant things to consider is whether the allocations for those caches are allocated using the single-page allocator, which uses the buffer pool, or the multi-page allocator, which prior to SQL Server 2012 doesn't use the buffer pool and instead consumes memory in the memtoleave area. You have about 13 GB allocated to the buffer pool, so a few hundred MB allocated to this or that isn't necessarily going to cause a problem. However, a few hundred MB in the memtoleave area is enough to cause some of the errors you listed.

Kehayias' post explains these concepts as well as your options for solving the problem if it is indeed memtoleave exhaustion.

I would focus mainly on what is starving the memtoleave area, as well as what overall memory availability in Windows looks like, along with general measures of memory availability in SQL Server such as page life expectancy.

I would also consider whether the problem helps make a business case for migrating to a more recent 64-bit version of SQL Server, as this avoids memtoleave issues unless the whole server is simply running out of memory. Understanding and managing SQL Server's memory usage is significantly simpler on 64-bit builds, especially on SQL Server 2012 where all allocations go through the buffer pool. You may also want to consider applying SP4 as there are a number of known bugs in SP3.

I'm guessing the 'memory left' output you posted is from Christian Bolton's VAS usage analysis script. Is that correct? If so, you have only 17-18 MB available in the memtoleave area, which is likely to cause a problem at some point and could cause all or most of the error messages that you pasted.

This query from Jonathan Kehayias here will give you an idea of what is using memory in the memtoleave area:

SELECT type, virtual_memory_committed_kb, multi_pages_kb 
FROM sys.dm_os_memory_clerks 
WHERE virtual_memory_committed_kb > 0 OR multi_pages_kb > 0

The post also explains memtoleave well.

Whether the size of the caches you asked about is a problem depends on a number of things. Since this is a 32-bit instance, one of the more relevant things to consider is whether the allocations for those caches are allocated using the single-page allocator, which uses the buffer pool, or the multi-page allocator, which prior to SQL Server 2012 doesn't use the buffer pool and instead consumes memory in the memtoleave area. You have about 13 GB allocated to the buffer pool, so a few hundred MB allocated to this or that isn't necessarily going to cause a problem. However, a few hundred MB in the memtoleave area is enough to cause some of the errors you listed.

Kehayias' post explains these concepts as well as your options for solving the problem if it is indeed memtoleave exhaustion.

I would focus mainly on what is starving the memtoleave area, as well as what overall memory availability in Windows looks like, along with general measures of memory availability in SQL Server such as page life expectancy.

I would also consider whether the problem helps make a business case for migrating to a more recent 64-bit version of SQL Server. Understanding and managing SQL Server's memory usage is significantly simpler on 64-bit builds, especially on SQL Server 2012 where all allocations go through the buffer pool. I would also plan for applying SP4 as there are a number of known bugs in SP3.

I'm guessing the 'memory left' output you posted is from Christian Bolton's VAS usage analysis script. Is that correct? If so, you have only 17-18 MB available in the memtoleave area, which is likely to cause a problem at some point and could cause all or most of the error messages that you pasted.

This query from Jonathan Kehayias here will give you an idea of what is using memory in the memtoleave area:

SELECT type, virtual_memory_committed_kb, multi_pages_kb 
FROM sys.dm_os_memory_clerks 
WHERE virtual_memory_committed_kb > 0 OR multi_pages_kb > 0

The post also explains memtoleave well.

Whether the size of the caches you asked about is a problem depends on a number of things. Since this is a 32-bit instance, one of the more relevant things to consider is whether the allocations for those caches are allocated using the single-page allocator, which uses the buffer pool, or the multi-page allocator, which prior to SQL Server 2012 doesn't use the buffer pool and instead consumes memory in the memtoleave area. You have about 13 GB allocated to the buffer pool, so a few hundred MB allocated to this or that isn't necessarily going to cause a problem. However, a few hundred MB in the memtoleave area is enough to cause some of the errors you listed.

Kehayias' post explains these concepts as well as your options for solving the problem if it is indeed memtoleave exhaustion.

I would focus mainly on what is starving the memtoleave area, as well as what overall memory availability in Windows looks like, along with general measures of memory availability in SQL Server such as page life expectancy.

I would also consider whether the problem helps make a business case for migrating to a more recent 64-bit version of SQL Server. Understanding and managing SQL Server's memory usage is significantly simpler on 64-bit builds, especially on SQL Server 2012 where all allocations go through the buffer pool. You may also want to consider applying SP4 as there are a number of known bugs in SP3.