Not enough storage is available to complete this operation

• ally_r

  Right there with Babe

  Points: 723

  More actions

  June 2, 2010 at 8:52 am

  #140636

  Hi,

  Thanks for taking the time to read this.

  SQL Server 2005 SP3

  Windows Server 2003 32bit

  The SQL server has a linked server which is used to query a catalog created by the windows indexing service. This has indexed a directory containing approx 1TB of files and the index is around 10GB in size.

  I can query the index directly through the indexing service section of computer management but when querying through sql server get the following error.

  OLE DB provider "MSIDXS" for linked server "****" returned message "Not enough storage is available to complete this operation.".

  Msg 7399, Level 16, State 1, Line 28

  The OLE DB provider "MSIDXS" for linked server "****" reported an error. The provider ran out of memory.

  I have checked VASummary and the Total avail memory is 21464KB, which to me seems very low based on what I have read in the memory chapter of "SQL Server 2008 Internals and Troubleshooting".

  Shouldn't this memory be released when it is not in use and should therefore be available again?

  If anyone has any suggestions that would be great.

  Thanks

  Ally

• Lowell

  SSC Guru

  Points: 323482

  More actions

  June 2, 2010 at 9:15 am

  #1175367

  take a look at this thread: someone was running into a similar situation:

  http://qa.sqlservercentral.com/Forums/Topic930970-149-1.aspx

  without seeing your specific query against the linked server, i bet it is the same issue:

  i know linked server queries are slow because if you join a local table to a remote table, the entire remote table is downloaded into local temp, and then the joins are applied.

  I think if you were to change your query to use a subselect or CTE featuring the OPENQUERY command for the linked server info, and then do your join, it would be faster.

  the issue is this example:

  select *

  from LinkedServer.Databasename.dbo.MillionRowTable

  where something=somethingelse

  --vs

  SELECT *

  FROM OPENQUERY(LinkedServer, 'select * from MillionRowTable where something=somethingelse');

  take a look at your query agaisnt your linked server, is it doing the above?

  the reason: the first query gets the million rows locally into temp, then does the filtering, where the openquery does the work on the linkedserver, and returns just the results.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• ally_r

  Right there with Babe

  Points: 723

  More actions

  June 2, 2010 at 9:23 am

  #1175375

  Hi Lowell,

  Thanks for your prompt response.

  Unfortunately that doesn't look like the issue I'm experiencing. I have narrowed it down to the query below.

  declare @IndexServerRes table (path varchar(500) primary key,rank float)

  insert into @IndexServerRes

  select * from OpenQuery(FileStore, 'SELECT path, rank From scope() WHERE contains(contents, ''"low cost"'')')

  Any other ideas?

  Thanks

  Ally