Post reply

Transaction Log backu job sometimes fails with erooror: There is insufficient system memory in resource pool 'internal' to run this query.

  • February 27, 2018 at 2:06 pm

    #329438

    Hi

    We run SQL Server 20008R on Windows Server 2008R on HyperV
    Windows Machine is dedicated to run SQL Server production DB
    We have 32 GB RAM.
    We've started to get failure on Transaction Log backup job ( runs every 15 min) ,
    may be couple times per day. I don' see any time pattern, related to failure.
    The log reports 2 kind of errors:
    1.." failed with the following error: "There is insufficient system memory in resource pool 'internal' to run this query. BACKUP LOG is terminating abnormally."
    2. Message
     The step did not generate any output. The return value was unknown. The process exit code was 255. The step failed.

    Could you advice what we need to check about it.

    Thanks, Svetlana

  • February 27, 2018 at 2:32 pm

    #1981372

    How much of the 32 GB is SQL Server using?

  • February 27, 2018 at 2:52 pm

    #1981375

    Svetlana Mikhalevskaya - Tuesday, February 27, 2018 2:06 PM

    Hi

    We run SQL Server 20008R on Windows Server 2008R on HyperV
    Windows Machine is dedicated to run SQL Server production DB
    We have 32 GB RAM.
    We've started to get failure on Transaction Log backup job ( runs every 15 min) ,
    may be couple times per day. I don' see any time pattern, related to failure.
    The log reports 2 kind of errors:
    1.." failed with the following error: "There is insufficient system memory in resource pool 'internal' to run this query. BACKUP LOG is terminating abnormally."
    2. Message
     The step did not generate any output. The return value was unknown. The process exit code was 255. The step failed.

    Could you advice what we need to check about it.

    Thanks, Svetlana

    Is this 32 or 64 bit?
    What are the memory settings for SQL Server?
    Is the VM using dynamic or static memory?
    Are you setting buffercount or maxtransfer size in the backups?

    You may also want to look at the queries and some troubleshooting recommendations in this article:
    Troubleshooting SQL Server Memory

    Sue

  • February 27, 2018 at 4:56 pm

    #1981388

    We run 64 bit.
    Max Server memory set for 30 GB
    I don't know about about Static or Dynamic memory on HyperV side.
    I know, this machine has the highest priority, related  to manage resoursces
    from HyperV. But 32 RAM limitation comes from OS Windows 2008R server.
    The job is created as part of Maintenance Plan as backup task.
    Could you point me where I can check those settings: buffercount or maxtransfer size in the backups

    Thanks, Svetlana

  • February 27, 2018 at 7:44 pm

    #1981400

    Svetlana Mikhalevskaya - Tuesday, February 27, 2018 4:56 PM

    We run 64 bit.
    Max Server memory set for 30 GB
    I don't know about about Static or Dynamic memory on HyperV side.
    I know, this machine has the highest priority, related  to manage resoursces
    from HyperV. But 32 RAM limitation comes from OS Windows 2008R server.
    The job is created as part of Maintenance Plan as backup task.
    Could you point me where I can check those settings: buffercount or maxtransfer size in the backups

    Thanks, Svetlana

    Thanks for the additional information.
    With hyper-v, you can configure the guest for static memory, always has the memory allocated to it or dynamic where the host can take some from the guest when needed. That setting can make a difference.
    SQL Server having 30 GB of the 32 GB is probably not enough for the OS. There is no set rule on how much to allocate for the OS and how much for SQL Server - one of those where there are different theories and everyone is right. Best you can do is read some of the articles about it see what makes sense. Here are a couple to read:
    How much memory does my SQL Server actually need?
    How Much Memory Does Microsoft’s SQL Server Need?

    Maintenance plans don't have anywhere to set buffercount or maxtransfer size so that shouldn't play a part in the error.
    In addition to the article with the queries I first posted, you may want to query the ring buffers when you hit this to see what's going on with the memory. This article explains it well and has a query that shreds the XML and gives you pretty much all the memory issues:
    Using sys.dm_os_ring_buffers To Diagnose Memory Issues in SQL Server

    Sue

  • February 27, 2018 at 7:54 pm

    #1981402

    Sue, thank you for your reply.

    We are  kind new on HyperV.
    We discusses with our Networks Administrator the guest for static memory.
     Does it mean, that SQL can get more memory as guest over OS Windows limitation?

    Thanks, Svetlana

  • February 28, 2018 at 10:41 am

    #1981505

    Svetlana Mikhalevskaya - Tuesday, February 27, 2018 7:54 PM

    Sue, thank you for your reply.

    We are  kind new on HyperV.
    We discusses with our Networks Administrator the guest for static memory.
     Does it mean, that SQL can get more memory as guest over OS Windows limitation?

    Thanks, Svetlana

    If it's static, it means that memory won't be taken away from the guest when the host needs it.

    Sue

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply