none
Restoring failed: There is insufficient system memory in resource pool 'internal' RRS feed

  • Question

  • Hi,

    I am going through all those similar questions but non of them solved my issue. 

    Our database is lost by mistake, now we need to recover the database from the backup. The backup file is 4.23 GB and when I am trying to restore the database after 40 minutes, I am getting the error 701, There is insufficient system memory in resource pool 'internal' to run this query. Restored failed and when i close it, the state for the DB is still "Restoring".

    I did all these challenges but no use, please advice me how to retreive my DB back it is very important and vital.

    • enabled the "AWE"
    • enbale/disable the resource governor
    • changed the server
    • re-install SQL server

    Please help me to sort out the issue.

    Regards

    Wednesday, January 15, 2014 7:42 AM

All replies

  • Did you check is there any long running queries?

    Thanks

    Ramesh. M

    Wednesday, January 15, 2014 8:05 AM
  • Hello,

    Did you followed procedure mentioned in below article

    http://msdn.microsoft.com/en-us/library/dn465874(v=sql.120).aspx#bkmk_resolverecoveryfailures

    Can you paste output of below

    select @@version
    ---below query will work only in 2008 and above
    select
    (physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
    (locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
    (total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
    process_physical_memory_low,
    process_virtual_memory_low
    from sys.dm_os_process_memory
    Can you read errorlog and see if you can find any message related to OOM error


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    Wednesday, January 15, 2014 9:06 AM
    Moderator
  • Wednesday, January 15, 2014 9:16 AM
    Moderator
  • Hi,

    Thanks for your help. My server is Microsoft SQL Server 2008 R2 (SP1) (Intel X86) -  Developer Edition on Windows NT 6.1 <X86>  

    Memory_usedby_Sqlserver_MB = 118

    Locked_pages_used_Sqlserver_MB = 0

    Total_VAS_in_MB = 2047

    process_physical_memory_low = 0

    process_virtual_memory_low = 0

    I update the service pack but still the same issue. I couldn't change the value of MAX_MEMORY_PERCENT in resource pool 'internal' ( not allowed ) . I install SQL server 64 bit on another 64 bit operating system as well but this time I got another error. The backup file can not be added it gives Sql server timeout error. ( this work for another smaller backup file) . I configure the query  timeout but not solved. What you suggest now?


    Thursday, January 16, 2014 8:01 AM
  • Hi,

    Thanks for your help. My server is Microsoft SQL Server 2008 R2 (SP1) (Intel X86) -  Developer Edition on Windows NT 6.1 <X86>  

    Memory_usedby_Sqlserver_MB = 118

    Locked_pages_used_Sqlserver_MB = 0

    Total_VAS_in_MB = 2047

    process_physical_memory_low = 0

    process_virtual_memory_low = 0

    I update the service pack but still the same issue. I couldn't change the value of MAX_MEMORY_PERCENT in resource pool 'internal' ( not allowed ) . I install SQL server 64 bit on another 64 bit operating system as well but this time I got another error. The backup file can not be added it gives Sql server timeout error. ( this work for another smaller backup file) . I configure the query  timeout but not solved. What you suggest now?


    Thursday, January 16, 2014 8:01 AM
  • Hi,

    Thanks for your help. My server is Microsoft SQL Server 2008 R2 (SP1) (Intel X86) -  Developer Edition on Windows NT 6.1 <X86>  

    Memory_usedby_Sqlserver_MB = 118

    Locked_pages_used_Sqlserver_MB = 0

    in resource pool 'internal' ( not allowed ) . I install SQL server 64 bit on another 64 bit operating system as well but this time I got another error. The backup file can not be added it gives Sql server timeout error. ( this work for another smaller backup file) . I configure the query  timeout but not solved. What you suggest now?


    SP1 for SQL server 2008 R2 is not the latest but the latest is SP2. Please apply it when you move ahead.

    Since you had 32 bit this error can come.I cannot reproduce the error neither I know what all queries or processes were running when you got OOM error during restore so its hard to tell .Did you set value for MAX server memory in sp_configure.How much physical RAM do you have ?

    Can you post complete error when you tried to restore backup on 64 bit. Also make sure you cannot restore backup from 2008 R2 to lower versions.( like 2008, 2005)


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    Thursday, January 16, 2014 8:12 AM
    Moderator
  • Thank You . I am updating the SP2 for 32 bit operating system lets see I will get the error again or not. The 64 bit version gives the error is:

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------
    ADDITIONAL INFORMATION:

    Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
    The backup or restore was aborted. (Microsoft SQL Server, Error: -2)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=-2&LinkId=20476

    ----------------

    I should say the firewall is already off. The error is coming when i am choosing the backup file from the wizard (before start restoring even) but as I said i could have restore the older backup with 3.09 G but the latest backup is 4.23 G. and i need to restore this file. 

    Thursday, January 16, 2014 8:56 AM
  • Afrooz,

    Disable AWE , Re-Start SQL Server with -g512 and then restore the backup. You should be able to restore.

    Reason in http://mssqlwiki.com/2013/03/05/sql-server-g/


    Thank you,

    Karthick P.K |My blogs|My Scribbles|Twitter|My Facebook Group|

    www.Mssqlwiki.com

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Thursday, January 16, 2014 8:56 AM
    Moderator

  • Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
    The backup or restore was aborted. (Microsoft SQL Server, Error: -2)


    Hello,

    For timeout you can see below link.

    http://go4answers.webhost4life.com/Example/sql-backup-timeout-expired-178065.aspx


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    Thursday, January 16, 2014 9:29 AM
    Moderator
  • Hi,

    1. Check the information in the DBCC MEMORYSTATUS output and verify what consumes large amount of memory.

    DBCC MEMORYSTATUS : How is Stolen Potential calculated  

    http://sqlactions.com/2013/02/16/dbcc-memorystatus-how-is-stolen-potential-calculated/

    2. I suggest you enlarge the Max Server memory for the instance.

    Thanks.


    Tracy Cai
    TechNet Community Support

    Thursday, January 16, 2014 10:16 AM
    Moderator