What are the challenges you face when working across database platforms? Take the survey

OutOfMemoryException. Urgent, immediate attention requested.


I have an urgent and crucial issue, and request your looking into this as soon as possible. Thank you.

Issue is I am getting System.OutOfMemoryException after I run the following:
block(0) = provider.GetMigrationSQL(session, New SelectionDelegate(AddressOf SyncRecordForInsertsAndDelete_Current), True)

I watched the the task manager, Performance Tab, and every time the execution goes into "SyncRecordForInsertsAndDelete_Current" function, it loses physical memory permanently and eventually throws in above mentioned exception. Approximately (2MB on each invocation)

I explicitly started setting my objects in the function to nothing and even call dispose on resultsReader object (ResultsStore.Reader) with no difference in the final outcome. It still fails.

The table I am comparing is large and have 88 columns, but I have noticed memory loss in smaller tables as well. It just takes more time to fail on a smaller table.

We have got few issues with red-gate as you would see from my earlier posts. Fortunately they all have been resolved. This seems to be the last hurdle as everything else has been fixed or solved. End seems to be near.

Red-gate is a core component of our data migration project. This would be a show stopper if it cannot be fixed.

This is a million dollar project overall, so please expedite it as soon as possible.

Thank you.


  • Options

    I don't think that the memory leak you're seeing is in the SQL Comparison SDK code. It's probably down to the creation of objects inside your selection delegate function. Maybe memory profiling it would help? http://www.red-gate.com/products/ants_memory_profiler

    Setting objects to Nothing in VB doesn't always free up the memory, especially if they are being referenced by other objects.
  • Options
    Well, I reduced the selection delegate to just one line of code:

    Dim resultsReader As ResultsStore.Reader

    resultsReader = m_TableDifferences(syncRecordObject.TableName).ResultsStore.GetReader()

    And watched performance tab in task manager. (Just as in FilterSQLExample in your API example)

    The physical memory kept on reducing on every invocation and did not come back till End Using for the database was called. For bigger table it gave OutOfmemoryException as I have mentioned earlier.
    It might not be a leak, but I think it retains the memory till the database connection is closed. (Similar to SQLDataReader object in ADO.NET).

    So the example that you have given would not work for large databases where many tables are being compared.
  • Options
    This is what I did to fix the above issue.

    Replaced line that i mentioned above.
    resultsReader = m_TableDifferences(syncRecordObject.TableName).ResultsStore.GetReader()

    resultsReader = GetResultStoreReader(syncRecordObject.TableName)

    and this is what my GetResultStoreReader function looks like:

    Private Function GetResultStoreReader(ByVal tableNameKey As String) As ResultsStore.Reader
    Dim moduleName As String : moduleName = PROG_ID & "GetResultStoreReader"

    If resultsReaderDict Is Nothing Then
    resultsReaderDict = New Dictionary(Of String, ResultsStore.Reader)
    End If
    If resultsReaderDict.ContainsKey(tableNameKey) Then
    Return resultsReaderDict.Item(tableNameKey)
    resultsReaderDict.Add(tableNameKey, m_TableDifferences(tableNameKey).ResultsStore.GetReader())
    Return resultsReaderDict.Item(tableNameKey)
    End If

    Catch ex As Exception
    ex.Source = moduleName & " - " & ex.Source
    Throw ex
    End Try

    End Function

    This is how I declared resultsReaderDict
    Private resultsReaderDict As Dictionary(Of String, ResultsStore.Reader) = Nothing

    by doing this, I reduced the execution of the culprit statement FROM being called for every row of every table that is being compared TO just the distinct list of table names that i am comparing.

    This solved my problem.

    Hope it helps someone else.
Sign In or Register to comment.