There is insufficient system memory in resource pool 'internal' to run this query RRS feed

  • Question

  • Having serious trouble since migrating to sql 2014 from sql 2008 connecting to DB2 using what was in place before. 

    Sql 2008 had two linked servers since they had difference purposes. Both worked.

    • one using MS OLE DB Provider for DB2 with this connection string: @provstr=N'NetLib=TCPIP;NetAddr=x.x.x.x;RemoteLU=ServerName;LocalLU=LOCAL;ModeName=QPCSUPP;InitCat=ServerName;Default Schema=QGPL;PkgCol=QGPL;Commit=NONE;IsoLvl=NC;BINASCHAR=TRUE;Data Source=DSName'
    • one using MS OLE DB Provider for ODBC Drivers

    Now on SQL 2014 with DB2OLEDBV5 installed neither of these linked servers set up identically will work from same applications or SSRS reports without returning "There is insufficient system memory in resource pool 'internal' to run this query"

    Using Test Connection from Mgmt Studio works on both with the exception of the OLE connection not translating CCSID=037 correctly to string from bit but neither work outside of SQL. 

    • Edited by spenopolis Wednesday, April 5, 2017 8:14 PM
    Wednesday, April 5, 2017 8:13 PM

All replies

  • What happens if you run a simple query like:

       SELECT * FROM DB2SERVER.catalog.sch.smalltbl

    I would guess that the queries that SSRS and ASP .NET runs are more complex and the issue is related to changes in query plans from SQL 2008 to SQL 2014.

    Assuming that a plain simple query works, this is the next step for your troubleshooting. First make sure that you have applied SP2 for SQL 2014.

    Then enable trace flag 4199. (This flag enables all optimizer hotfixes.)

    If 4199 does not have any effect, switch the compatibility mode of the database to 110. This selects the old cardinality estimator. (The new CE might have cause a shakeup in the plans.)

    Wednesday, April 5, 2017 9:14 PM