2

I am using Entity Framework 6.1.1. The database has three million records; migration has to change the precision of a column of type decimal. The migration timeout window is set to the maximum value, CommandTimeout = Int32.MaxValue;

I was testing this scenario on Azure SQL Database. After trying for around 90 minutes, it ends up with an exception, but no migration done.

Exception details:

The session has been terminated because of excessive transaction log space usage. Try modifying fewer rows in a single transaction.

My questions are:

  1. Is it the expected behavior to take more time for migration of this type on a huge DB? (Because after changing the precision it has to convert the existing value and save it back, and it has to be repeated on 3 million records)

  2. How to resolve this? I don't think we can have migration in chunks of transactions.

1 Answer 1

1

The simple answer is don't use Entity Framework for this sort of thing. You need to write a SQL migration script, test it, and then deploy it onto your production system. And yes - changing a table with 3M rows will take more time and resources than migrating a table with 3 rows.

There are a few techniques for achieving this. First, judging by the error message, you're trying to do this in a single transaction. Better to add a new column of the correct precision, copy values to it in batches of, say, 50,000 at a time, then drop the old column and rename the new one.

If the system has to remain operational during the migration you can get creative with views and triggers.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.