Processing Thousands of SqlCommands using a SqlTransaction Causes Memory Exception

Question

I've written a custom replication function in a standard C# windows forms app with a SQL Server 2008 Express database. It basically pulls down a set of sql statements that need to be executed against a subscriber database. On a complete refresh this can run up to 200k+ statements that need to be executed.

I processing these statements inside a code block as shown below:

using (SqlConnection connection = ConnectionManager.GetConnection())
{
     connection.Open();

     SqlTransaction transaction = connection.BeginTransaction();

     // Process 200k+ Insert/Update/Delete statements using SqlCommands

     transaction.Commit
}

What I'm finding is that my applications memory usage remains pretty stable at around 40mb for the first 30k statements. After which it suddenly seems to jump to around 300mb and then grows until I hit a OutOfMemory exception.

Is the method I'm using even possible, can I process that many statements inside a single transaction? I would assume I should be able to do this. If there is a better way I'd love to here it. I need this to be transactional otherwise a partial replication would result in a broken database.

Thanks.

EDIT:

After restarting my computer I managed to get a full 200k+ replication to go through. Even though it did at one point grow in memory usage to 1.4Gb after the replication completed the memory usage dropped all the way back to 40mb. Which leads me to conclude that something inside my loop that processes the commands is causing the growth in memory perhaps.

Answer 1

Are you Disposing your forms and the disposable controls before closing?

Wrap all Disposable objects in Using Statement. Click here for more details

---

Don't open/close the Connection over and over again, instead send the data to database in single Transaction. Click here for more details

---

Still your application is holding tooo much memory then you need a Doctor like Red Gate Ants Memory Profiler. Click here to see more details about it

---

can I process that many statements inside a single transaction?

You have below options to do this...

1. Bulk insert and oprate the records in Stored Proc.
2. Prepare XML and send the string in Database.
3. Send the Read only DataTable in the Sql Server through Stored Proc

Sample Stored Proc

Begin Try
    Set NoCount ON
    Set XACT_Abort ON
    Begin TRan
        --Your queries
    Commit Tran

Begin Tran

Begin Catch
    Rollback Tran
End Catch

Make sure to Dispose the objects once not in use.

---

It should be like this

using (SqlConnection connection = new SqlConnection())
{
    connection.Open();
    using (SqlTransaction transaction = connection.BeginTransaction())
    {
        transaction.Commit();
    }
}

Did you verify the SqlCommand also?

using (SqlCommand cmd = new SqlCommand())
{
}