SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server 2008


SQL Server 2008

Author
Message
Grace09
Grace09
Old Hand
Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)

Group: General Forum Members
Points: 336 Visits: 1710
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218587 Visits: 46278
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Grace09
Grace09
Old Hand
Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)

Group: General Forum Members
Points: 336 Visits: 1710
No, it is 64-bit.
Grace09
Grace09
Old Hand
Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)

Group: General Forum Members
Points: 336 Visits: 1710
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
Grace09
Grace09
Old Hand
Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)

Group: General Forum Members
Points: 336 Visits: 1710
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218587 Visits: 46278
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Grace09
Grace09
Old Hand
Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)

Group: General Forum Members
Points: 336 Visits: 1710
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218587 Visits: 46278
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Grace09
Grace09
Old Hand
Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)

Group: General Forum Members
Points: 336 Visits: 1710
Thank you, Gail. I will look for external memory pressure.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search