Post reply

how to increase resource pool memory

  • July 15, 2014 at 11:35 pm

    #397063

    Hi,

    I have a database server with SQL Server 2008R2 32 bit and Windows Server 2008 32 bit, having total of 4GB RAM. I have set Min memory as 250MB and Max memory as 1500MB for SQL Server.

    When I ran a file of Insert Script of around 23MB using SQLCMD, it is throwing an exception as "There is insufficient system memory in resource pool 'internal' to run this query. I checked multi-page memory using "select sum(multi_pages_kb)/1024 as [MultiPage Memory, MB] from sys.dm_os_memory_clerks" query and it is returning 17MB.

    Can anyone please guide me how to increase resource pool memory, so that I can run the insert script?

    Thanks & Regards,

    Kumar Anand

  • July 15, 2014 at 11:52 pm

    #1729723

    On a 4GB machine, I'd let SQL Server have a max of 3GB (3000MB) instead of just 1.5GB (1500mb).

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 16, 2014 at 9:21 pm

    #1730092

    A couple of things, look at your local security settings to ensure the OS is not taking memory[/url] from SQL.

    Also, before changing MAX memory setting, you need to use perfmon and monitor available bytes counter to ensure the OS has enough memory, at at minimum anything above say 100-200mb could be reallocated to SQL.

    Assuming you have AWE enabled?

Viewing 3 posts - 1 through 2 (of 2 total)

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