SQL Server 2008

  • 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.

  • 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
  • No, it is 64-bit.

  • 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

  • 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.

  • 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
  • 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

  • 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
  • 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