CODEWITHSUNDEEP
sql-serverazureazure-sql-database
Burt
Burt

Reputation: 7758

Azure - WebJob timeout when calling stored procedure

I have a webjob that calls a long running stored procedure that keeps timing out. Can anyone help please?

The web job is called using the following code:

    static void Main()
    {
        ApplicationDbContext context = new ApplicationDbContext();

        context.Database.CommandTimeout = 6000;

        context.PopulateJobTypeDescendants();

    }

The method on the context (ApplicationDbContext) is shown below:

    public void PopulateJobTypeDescendants()
    {
        Database.ExecuteSqlCommand("PopulateJobTypeDescendants");
    }

The following exception is raised when the web job is run. We have read that it could be related to the plan/DTUs on the server so we went from S1 -> S3, this still didn't solve the issue and the process bombs out after 45 seconds. The strange thing is that if I connect to azure sql db from SSMS and call the stored procedure it works fine.

[07/11/2016 22:25:02 > e2cf50: ERR ] Unhandled Exception: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. This failure occurred while attempting to connect to the routing destination. The duration spent while attempting to connect to the original server was - [Pre-Login] initialization=14; handshake=26; [Login] initialization=0; authentication=0; [Post-Login] complete=1; ---> System.ComponentModel.Win32Exception: The wait operation timed out

The connection string is shown below:

<add name="TempsContext" connectionString="Server=tcp:[XXX],1433;Database=temps_testing;User ID=[XXX];Password=[XXX];Trusted_Connection=False;Encrypt=True;Connection Timeout=600;" providerName="System.Data.SqlClient" />

Upvotes: 0

Views: 1228

Answers (1)

divega
divega

Reputation: 6476

It is possible that this is caused by some inconsistencies in how EF propagates the value of CommandTimeout to the commands its creates, e.g. to do database initialization or to obtain versioning information from the server.

It should be possible to use command interception as a workaround, e.g.:

using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Infrastructure.Interception;

namespace CommandTimeOutBug
{
    class Program
    {
        static void Main(string[] args)
        {
            DbInterception.Add(new MyInterceptor());

            using (var context = new ApplicationDbContext())
            {

                context.Database.CommandTimeout = 6000;

                context.PopulateJobTypeDescendants();
            }
        }
    }

    public class ApplicationDbContext : DbContext
    {

        public void PopulateJobTypeDescendants()
        {
           Database.ExecuteSqlCommand("PopulateJobTypeDescendants");
        }
    }

    public class MyInterceptor: DbCommandInterceptor
    {

        public override void NonQueryExecuting(DbCommand command, 
            DbCommandInterceptionContext<int> interceptionContext)
        {
            command.CommandTimeout = 6000;
            base.NonQueryExecuting(command, interceptionContext);
        }

        public override void ReaderExecuting(DbCommand command, 
            DbCommandInterceptionContext<DbDataReader> interceptionContext)
        {
            command.CommandTimeout = 6000;
            base.ReaderExecuting(command, interceptionContext);
        }

        public override void ScalarExecuting(DbCommand command, 
            DbCommandInterceptionContext<object> interceptionContext)
        {
            command.CommandTimeout = 6000;
            base.ScalarExecuting(command, interceptionContext);
        }
    }
}

I have created a bug at https://github.com/aspnet/EntityFramework6/issues/24 to track this.

Upvotes: 1

Related Questions