Post reply

ERROR MESSAGE: There is insufficient system memory in resource pool 'internal' to run this query.

  • May 20, 2010 at 9:54 am

    #224804

    I am getting the message

    "There is insufficient system memory in resource pool 'internal' to run this query."

    periodically.

    I am running Server 2003 R2 64-bit and SQL Server 2008 64-bit with 9GB RAM. I have 2 SQL instances running. Instance A is used frequently for ad hoc reporting and some batch processes. Instance B is very rarely used for ad hoc reporting - it could be idle for days at a time. Both instances have Data Collector running separately, which I believe utilizes CLR. Resource governor is not being used and I do not have min or max server memory set for either instance.

    Based on other research, I have tried putting -g384 in startup string for both instances and "lock pages in memory" set for the service account that runs sqlservr. but the problem continues to crop up in instance B every few days during off hours. Nothing is running then besides the Data Collector procedures.

    Any information on what to look for or how to fix this would be much appreciated. Thanks

  • May 20, 2010 at 3:30 pm

    #1170471

    Consider setting max memory values for the instances. Leave some memory for the operating system (+- 2 GB)

  • May 20, 2010 at 4:00 pm

    #1170493

    What do you recommend? I have 9GB. Instance A is usually often and Instance B is hardly used, but needs to be able to process nonetheless.

  • May 25, 2010 at 2:09 am

    #1171904

    Have a look at the performance counters (windows)

    SQLServer:Memory Manager: Total Server Memory (KB) : total memory for instance

    SQLServer:Memory Manager: Target Server Memory (KB) : wanted memory for instance

    they give an indication of what sql server needs to run optimally.

    http://www.sql-server-performance.com/tips/performance_main.aspx

    A wild guess 5 GB for instance A, 2GB Instance B, 2GB OS

    btw, what is the patch version of your instance?

  • May 25, 2010 at 11:01 am

    #1172240

    I just installed CU7 on both instances, so the patch level is 10.0.2766.0 (X64).

    I have assigned the 'lock pages in memory' right to the 'network service' account that runs sqlservr.exe.

    I set the startup trace flag -T845

    So far, nothing seems to have any impact. I'll have to try changing the 'max server memory' settings after hours today. Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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