June 5, 2014 at 7:14 am
I have transaction log backup failures randomly. SQL Server Log says 'Memory constraints resulted reduced backup/restore buffer sizes. Proceding with 6 buffers of size 64KB.', and the SQL Server Agent Error Log has quite a few errors like 'Unable to start Job Manager thread for job xxx', '[298] SQLServer Error: 768, Client unable to establish connection [SQLSTATE 08001]', [298] SQLServer Error: 768, SSL Provider: Not enough memory is available to complete this request [SQLSTATE 08001]'.
I am really low on the memory? It has total of 32 GB, but I set maximum to 20. Task manager shows it is using 99% of physical memory. Memoryclerk-sqlbufferpool has 32GB for the Virtual Memory Reserved, and 20 GB for the Virtual Memory Committed.
Can anyone advice? Thanks in advance.
June 5, 2014 at 7:32 am
Backup buffers are outside of the buffer pool, so not part of the 20GB that max server memory sets
Is this 32 bit?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 5, 2014 at 7:41 am
No, it is 64-bit.
June 5, 2014 at 7:53 am
here is part of dbcc memorystatus result, Thanks.
Memory Manager KB
---------------------------------------- -----------
VM Reserved 33767416
VM Committed 21496136
Locked Pages Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0
(5 row(s) affected)
Memory node Id = 0 KB
---------------------------------------- -----------
VM Reserved 33763768
VM Committed 21492600
Locked Pages Allocated 0
MultiPage Allocator 73608
SinglePage Allocator 4318576
MEMORYCLERK_SQLBUFFERPOOL (node 0) KB
---------------------------------------- -----------
VM Reserved 33603584
VM Committed 21337344
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 416
CACHESTORE_SQLCP (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 3636928
MultiPage Allocator 19928
Buffer Pool Value
---------------------------------------- -----------
Committed 2621440
Target 2621440
Database 171481
Dirty 6796
In IO 0
Latched 0
Free 1910137
Stolen 539822
Reserved 0
Visible 2621440
Stolen Potential 1950546
Limiting Factor 17
Last OOM Factor 0
Page Life Expectancy 2510
Process/System Counts Value
---------------------------------------- --------------------
Available Physical Memory 680857600
Available Virtual Memory 8760916754432
Available Paging File 233734144
Working Set 22098804736
Percent of Committed Memory in WS 100
Page Faults 440734742
System physical memory high 1
System physical memory low 0
Process physical memory low 0
Process virtual memory low 0
(10 row(s) affected)
Procedure Cache Value
---------------------------------------- -----------
TotalProcs 40731
TotalPages 472307
InUsePages 941
June 5, 2014 at 8:01 am
From the perfmon, SQLServer:Memory Manager/Total ServerMemory shows 21GB of memory in use, that's what I set the maximum to. I don't where the rest of memory go.
June 5, 2014 at 12:53 pm
The backup buffers are not part of the 20GB allocated to the buffer pool. They are outside the buffer pool.
Trying to find out how the 20GB of buffer pool is allocated is pointless, the backup buffers aren't there.
It could be that Windows was under memory pressure and so there was no free memory (outside of the 20GB allocated to SQL) for the backup buffers. Shouldn't have virtual address space fragmentation on 64 bit, so that's probably not it. I'd look for signs of external memory pressure.
btw what does this return?
SELECT @@Version
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 5, 2014 at 2:03 pm
Thank you, Gail, here is the result for select @@version,
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.0 <X64> (Build 6002: Service Pack 2)
By the way, here is what I get when i run the following query, Thanks.
select SUM(virtual_memory_reserved_kb)/1024 as virtual_memory_reserved_mb from sys.dm_os_memory_clerkswhere type not like '%bufferpool%'
virtual_memory_reserved_mb
32892
June 6, 2014 at 12:43 am
As I said, I'd be looking for external memory pressure when that happens.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 6, 2014 at 11:28 am
Thank you, Gail. I will look for external memory pressure.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply