Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server 2008 Expand / Collapse
Author
Message
Posted Thursday, June 5, 2014 7:14 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 20, 2014 8:28 AM
Points: 54, Visits: 1,294
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.

Post #1577776
Posted Thursday, June 5, 2014 7:32 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
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 2008, MVP
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

Post #1577784
Posted Thursday, June 5, 2014 7:41 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 20, 2014 8:28 AM
Points: 54, Visits: 1,294
No, it is 64-bit.
Post #1577794
Posted Thursday, June 5, 2014 7:53 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 20, 2014 8:28 AM
Points: 54, Visits: 1,294
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
Post #1577809
Posted Thursday, June 5, 2014 8:01 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 20, 2014 8:28 AM
Points: 54, Visits: 1,294
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.
Post #1577815
Posted Thursday, June 5, 2014 12:53 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
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 2008, MVP
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

Post #1578017
Posted Thursday, June 5, 2014 2:03 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 20, 2014 8:28 AM
Points: 54, Visits: 1,294
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
Post #1578047
Posted Friday, June 6, 2014 12:43 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
As I said, I'd be looking for external memory pressure when that happens.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1578171
Posted Friday, June 6, 2014 11:28 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 20, 2014 8:28 AM
Points: 54, Visits: 1,294
Thank you, Gail. I will look for external memory pressure.
Post #1578426
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse