Entity Framework: Automatic migration failed on huge DB

Question

I am using EF 6.1.1, and my database has three million records and migration has to change the precision of a column of type decimal. And the migration timeout window set to max value, CommandTimeout = Int32.MaxValue;

I was testing this scenario in Azure SQL Server, and after trying around 90 minutes it ends up with an exception, but no migration done on database.

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.

Answer 1

You get this error because you are running out of transaction log space. Your transaction log is likely filling up because you are doing this in one transaction. Depending on the number of columns in your table the transaction log can take up considerable disk space for 3,000,000 records. The easiest solution, if possible, is temporarily allocate more disk space to your transaction log. Alternatively you could consider batching your migration, although point 2 suggests this is not an acceptable solution for you.

Answer 2

If you are on SQL Azure (server name like *.database.windows.net) there is a transaction log limit of 2 GB on a v11 server. If you use the newer v12 version of SQL Azure the transaction log limit is extremely large.

I would suggest trying out v12 if you are on v11, if you are on v12 already you will have to look at batching your migration if possible.

Answer 3

I found that DbMigration.Sql(my_sql, true) method can help me to avoid putting all commands into a one transaction. (https://msdn.microsoft.com/en-us/library/system.data.entity.migrations.dbmigration.sql(v=vs.113).aspx)

So, I can control the size of transaction (number of records involved in to transaction).

my_sql can have own BEGIN TRAN/COMMIT