none
buffer pool exhausted iterating through rows from a stored procedure RRS feed

  • Question

  • Hi all;

    We have a case where we call a stored procedure and then iterate through the rows returned 1 at a time. About row 7,000 (out of 64,000) we get:

    System.Data.SqlClient.SqlException occurred
      Message="There is insufficient memory available in the buffer pool."
      Source=".Net SqlClient Data Provider"
      ErrorCode=-2146232060
      Class=17
      LineNumber=13
      Number=802
      Procedure="usp_Reports_IDR_UKCollectV3"
      Server="JESSICA\\SQLSERVER2008"
      State=20
      StackTrace:
           at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
           at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
           at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
           at System.Data.SqlClient.SqlDataReader.HasMoreRows()
           at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
           at System.Data.Common.DbEnumerator.MoveNext()
           at WindwardReportsDrivers.net.windward.datasource.ado.AdoDataSource.AdoNode.AdoIterator.next() in C:\src\Jenova\Dev\Merge\DotNetEngine\WindwardReportsDrivers\net\windward\datasource\ado\AdoDataSource.cs:line 3128
    

    Is there something special we need to do when iterating through a DbDataReader? Our code is as follows:

    IDbConnection connection = createConnection();
    IDbCommand cmd = BuildCommand(select, connection, parameters);
    DbDataReader reader = (DbDataReader) cmd.ExecuteReader(CommandBehavior.Default); 
    

    thanks - dave


    The Programming Olympics - Code Wars
    Monday, November 28, 2011 7:09 PM

Answers

  • Is there something special we need to do when iterating through a DbDataReader? Our code is as follows:

    IDbConnection connection = createConnection();
    IDbCommand cmd = BuildCommand(select, connection, parameters);
    DbDataReader reader = (DbDataReader) cmd.ExecuteReader(CommandBehavior.Default); 
    

     

    I don't think your use of the DbDataReader is the culprit.  The 802 error occurs on the server side so I think this question is more appropriate for the SQL Server Database Engine forum (http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine).  See the SQL Server Books Online description of this error: http://msdn.microsoft.com/en-us/library/aa337354.aspx.  The results of DBCC MEMORYSTATUS can help identify the cause.

    Is your DBA involved with troubleshooting?

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Monday, November 28, 2011 7:30 PM

All replies

  • Is there something special we need to do when iterating through a DbDataReader? Our code is as follows:

    IDbConnection connection = createConnection();
    IDbCommand cmd = BuildCommand(select, connection, parameters);
    DbDataReader reader = (DbDataReader) cmd.ExecuteReader(CommandBehavior.Default); 
    

     

    I don't think your use of the DbDataReader is the culprit.  The 802 error occurs on the server side so I think this question is more appropriate for the SQL Server Database Engine forum (http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine).  See the SQL Server Books Online description of this error: http://msdn.microsoft.com/en-us/library/aa337354.aspx.  The results of DBCC MEMORYSTATUS can help identify the cause.

    Is your DBA involved with troubleshooting?

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Monday, November 28, 2011 7:30 PM
  • The database belongs to a customer of ours. I'll ask them to follow up on the DB engine forum.

    thanks - dave


    The Programming Olympics - Code Wars
    Monday, November 28, 2011 7:50 PM