Timeout with a large migration?

[webframp]

Dealing with a migration for a table having several million rows at the moment and things seem to break down, it dies with this:

Failure: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
Erroring script was run in a transaction and was rolled back.
Failure: The wait operation timed out

Is timeout something adjustable, or is there a recommended approach to dealing with this sort of case?

[webframp]

in our case the following diff seems to work, terrible perhaps but it's a huge migration:

diff --git a/source/AliaSQL.Core/Services/Impl/QueryExecutor.cs b/source/AliaSQL.Core/Services/Impl/QueryExecutor.cs
index 2165260..460654e 100644
--- a/source/AliaSQL.Core/Services/Impl/QueryExecutor.cs
+++ b/source/AliaSQL.Core/Services/Impl/QueryExecutor.cs
@@ -40,6 +40,7 @@ namespace AliaSQL.Core.Services.Impl
                 using (var command = new SqlCommand())
                 {
                     command.Connection = connection;
+                    command.CommandTimeout = 1800;

                     var scripts = SplitSqlStatements(sql);

@@ -80,6 +81,7 @@ namespace AliaSQL.Core.Services.Impl
                     using (var command = new SqlCommand())
                     {
                         command.Connection = connection;
+                        command.CommandTimeout = 1800;
                         var scripts = SplitSqlStatements(sql);
                         foreach (var splitScript in scripts)
                         {
@@ -187,4 +189,4 @@ namespace AliaSQL.Core.Services.Impl


     }
-}

[webframp]

scratch that, it still didn't help

[ericdc1]

Try CommandTimeout = 0 and let me know the outcome.

[webframp]

setting timeout to 0 worked, the process took about 45 minutes but it succeeded. Maybe a /notimeout flag or something could be used?

[Deleted user]

Ran into the same timeout issue during a production upgrade last night. Is there any other solution that doesn't require us to make a source change?

[ericdc1]

This is now in the latest version here and Nuget. Thanks for the feedback.

[louiskruger4c]

We have run into a similar problem when running "CREATE NONCLUSTERED INDEX" on a large table.

We get the following error in the AliaSQL log output:
Failure: The transaction associated with the current connection has completed but has not been disposed. The transaction must be disposed before the connection can be used to execute SQL statements.
Erroring script was run in a transaction and was rolled back.

The SQLCommand timeout has been increased to maximum by setting it to 0, but we think the TransactionScope (default timeout 1 minute according to internet) is timing out.

We are going to try to use --NOTRANSACTION as a work around since we are only rebuilding indexes, but for UPDATE statements on this table we will need another solution.

Can you suggest anything we could try/test?

[louiskruger4c]

Using --NOTRANSACTION worked, so it seems like it is indeed the TransactionScope timeout that is affecting us.

However, it is still not safe to use this solution with UPDATE statements, is it?