Out of Memory Exception While loopin through batches

MSobey

I am having a problem when trying to script the data out of an existing database. (Code Below)
I only need the data, I am sending it to a server that already has an empty database that I want to populate with this data.

I am encountering a “system.outofmemoryexception” exception.
It occurs when I am looping through the batches in the execution block.
The block has 758,981 blocks.

Any recommendations you could give me would be appreciated very much.

Thanks,

Mike
Code:
block = provider.GetMigrationSQL(session, True)

'cleanup some memory
provider = Nothing
sourceDB = Nothing
If Not session Is Nothing Then
session.Dispose()
End If


'Call the Garbage collector before we start to make sure we have all the memory possible
GC.Collect()

I get the “system.outofmemoryexception” in this for loop.
'Loop through the batches
For lngCurrentBatch = 0 To block.BatchCount - 1
strTemp = block.GetBatch(lngCurrentBatch).Contents
''Strip out the comment block
'intBeginComment = InStr(strTemp, "/*")
'intEndComment = InStr(strTemp, "*/")
'If intBeginComment > 0 Then
' strTemp = Mid(strTemp, intEndComment + 6, strTemp.Length)
'End If
'Strip out Set XACT_ABORT ON statement
strTemp = strTemp.Replace("SET XACT_ABORT ON" & vbCrLf, vbNullString)
'Strip out Set ARITHABORT ON statement
strTemp = strTemp.Replace("SET ARITHABORT ON" & vbCrLf, vbNullString)
'Strip out Begin and Commit Transaction
strTemp = strTemp.Replace("BEGIN TRANSACTION" & vbCrLf, vbNullString)
strTemp = strTemp.Replace("COMMIT TRANSACTION" & vbCrLf, vbNullString)
'Strip out binary declaration
strTemp = strTemp.Replace("DECLARE @ptrval binary(16)" & vbCrLf, vbNullString)


While InStr(strTemp, vbCrLf & vbCrLf) > 0
strTemp = strTemp.Replace(vbCrLf & vbCrLf, vbNullString)
End While

'strip out any single vbcrlf
If strTemp = vbCrLf Then
strTemp = strTemp.Replace(vbCrLf, vbNullString)
End If

If strTemp.Length = 4 And InStr(strTemp, "GO" & vbCrLf) > 0 Then
'Strip out Go Statement
strTemp = strTemp.Replace("GO" & vbCrLf, vbNullString)
End If

If strTemp.Length > 1 Then
sScript.Append(strTemp)
strLastString = strTemp
End If
Next

If lngCurrentBatch = block.BatchCount Then
If strTemp <> strLastString And strLastString <> vbNullString Then
sScript.Append(vbCrLf & "GO" & vbCrLf)
strLastString = vbNullString
End If
End If

'Assume the best
blnAllWritten = True

Dim msImage As System.IO.MemoryStream
While drURLs.Read
Dim arParms() As SqlParameter = New SqlParameter(5) {}
msImage = StringToStream(sScript.ToString)
'msImage = StringToStream(block.ToString)
msImage = CompressStream(msImage)
msImage = EncryptStream(msImage, "ENCRYPTION123456")
End While

Brian Donahue

Hello,

Can you run this using the Visual Studio debugger and find the line where the exception occurs?

My bet is on the While loop. I'm leery about nesting loops because of the effect that they have on the program stack.

MSobey

I have found out that the exception occurs because I am exceeding the maximum capacity of the stringbuilder class (Int32.MaxCapacity).

Also, I saved the execution block to a file and the file was 387mb. This is too large for us to send, is there an easy way to break the execution block into several smaller packets that I could send?

We are wanting to send data only, we do not want to send schema with it.

Thanks,
Mike

Brian Donahue

Have you looked into using SQL Packager? This can automatically break the synchronization into 100MB files.

MSobey

I have looked at SQL Packager, but will the SQL Packager send data only?

I found the example that shows how to use the SQL Packager to send schema and data, but for it to work for us, we need it to send only the data.

Brian Donahue

You can just do the data only with Packager, if that's all you need.

MSobey

Brian, I really appreciate all of your help so far.

Could you point me in the direction of an example to automate Packager sending data only?

Thanks.
Mike

Brian Donahue

In code, when you are in the loop that sets the 'Selected' property for each object, set that property to 'false' for all schema objects. Then add the whole list of tables to the comparisonsettings array when generating the data and that should migrate the data.