locked
Insufficient Memory error after enabling mirroring. RRS feed

  • Question

  • I have successfully mirrored most of our databases across two servers without a witness but when adding the last few I get the following error constantly:
    There is insufficient system memory in resource pool 'internal' to run this query.
    This error is generated by the secondary server. I have looked at the memory allocated and found it to be more than sufficient.
    The only way to stop the error from occuring is to stop the SQL service and start it again.
    I am running SQL Server 2008 Standard on windows server 2003 x86, the server is running 4GB Ram.
    I have tried activating AWE but when I try to add the MSSQLSRV account to the 'Lock Pages In Memory' I find that the 'Add user or Group' button is greyed out. (I have administrator permissions and am accessing the server through remote desktop).
    The following is the result of a memory consumption report.
    Any Ideas?
        Memory Usage By Components            
    Component Type Allocated memory(KB) Virtual Memory Reserved(KB) Virtual Memory Committed(KB) AWE Memory Allocated(KB) Shared Memory Reserved(KB) Shared Memory Committed(KB) 
    MEMORYCLERK_SQLUTILITIES 80,144 133,336 133,336 0 0 0
    MEMORYCLERK_SOSNODE 11,336 0 0 0 0 0
    MEMORYCLERK_SQLSTORENG 6,776 2,624 2,624 0 0 0
    MEMORYCLERK_SQLGENERAL 4,520 0 0 0 0 0
    USERSTORE_OBJPERM 3,024 0 0 0 0 0
    USERSTORE_SCHEMAMGR 1,536 0 0 0 0 0
    OBJECTSTORE_SNI_PACKET 1,288 0 0 0 0 0
    CACHESTORE_SQLCP 864 0 0 0 0 0
    MEMORYCLERK_SQLCONNECTIONPOOL 816 0 0 0 0 0
    USERSTORE_DBMETADATA 688 0 0 0 0 0
    CACHESTORE_SYSTEMROWSET 520 0 0 0 0 0
    MEMORYCLERK_SQLSERVICEBROKER 520 0 0 0 0 0
    MEMORYCLERK_SQLBUFFERPOOL 504 1,654,240 29,248 0 0 0
    OBJECTSTORE_SERVICE_BROKER 408 0 0 0 0 0
    USERSTORE_TOKENPERM 320 0 0 0 0 0
    OBJECTSTORE_LOCK_MANAGER 312 1,024 1,024 0 0 0
    MEMORYCLERK_XE_BUFFER 0 4,224 4,224 0 0 0
    MEMORYCLERK_FULLTEXT_SHMEM 0 0 0 0 192 192
    Thursday, October 15, 2009 1:56 AM

Answers

  • Hi,

    Based on the report above, you don’t enable AWE successfully. To work around the issue, perform the following steps:
    1. Make sure you are a member of system administrators.
       a. In Computer Management, expand Local Users and Groups, and then expand Groups.
       b. In the detail pane, double click Administers.
       c. Check your account in the Administers members list
    2. To enable PAE, use the /PAE switch in the Boot.ini file. In Windows Server 2003, PAE is automatically enabled only if the server is using hot-add memory devices. In this case, you do not have to use the /PAE switch on a system that is configured to use hot-add memory devices.
    3. Enable the Lock Pages in Memory Option (Windows)
    http://msdn.microsoft.com/en-us/library/ms190730.aspx
    4. Configure the awe enabled Option (SQL Server Management Studio)
    http://msdn.microsoft.com/en-us/library/ms190961.aspx

    If there are any more questions, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Friday, October 16, 2009 8:27 AM

All replies

  • Is resource governor's enabled.? If yes, What's the configuration for the "internal" resource pool? Have a look at the connect site https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=342696

    Thanks, Leks
    Thursday, October 15, 2009 2:25 AM
    Answerer
  • No, resource governor is not enabled.
    Before error:

    MEM Manager:
    VM Reserved 1897272
    VM Committed 273352
    AWE Allocated 0
    Reserved Memory 1024
    Reserved Memory In Use 0

    After Error:
    MEM Manager:
    VM Reserved 1904640
    VM Committed 276608
    AWE Allocated 0
    Reserved Memory 1024
    Reserved Memory In Use 0
    Thursday, October 15, 2009 2:52 AM
  • Hi,

    Based on the report above, you don’t enable AWE successfully. To work around the issue, perform the following steps:
    1. Make sure you are a member of system administrators.
       a. In Computer Management, expand Local Users and Groups, and then expand Groups.
       b. In the detail pane, double click Administers.
       c. Check your account in the Administers members list
    2. To enable PAE, use the /PAE switch in the Boot.ini file. In Windows Server 2003, PAE is automatically enabled only if the server is using hot-add memory devices. In this case, you do not have to use the /PAE switch on a system that is configured to use hot-add memory devices.
    3. Enable the Lock Pages in Memory Option (Windows)
    http://msdn.microsoft.com/en-us/library/ms190730.aspx
    4. Configure the awe enabled Option (SQL Server Management Studio)
    http://msdn.microsoft.com/en-us/library/ms190961.aspx

    If there are any more questions, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Friday, October 16, 2009 8:27 AM
  • Thanks for the suggestions, I eventually got the AWE applied by enabling the 'Lock pages in Memory' local policy setting. It was a domain policy restriction that disabled the option to add users and groups to the 'Lock pages in Memory' local policy.

    Again, thanks for the suggestions.
    Monday, October 19, 2009 9:08 PM