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:
-
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)
-
How to resolve this? I don't think we can have migration in chunks of transactions.
Best Answer
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.