OutOfMemoryException. Urgent, immediate attention requested.

hemantgaur

Hello,

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.

Brian Donahue

Hello,

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.

hemantgaur

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.

hemantgaur

This is what I did to fix the above issue.

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

to
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"

Try
If resultsReaderDict Is Nothing Then
resultsReaderDict = New Dictionary(Of String, ResultsStore.Reader)
End If
If resultsReaderDict.ContainsKey(tableNameKey) Then
Return resultsReaderDict.Item(tableNameKey)
Else
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.