Hangfire causes Timeout expired exception when executing SQL Code to optimzie database - ASP.NET Core

Question

I have the following code setup as a scheduled task:

public class OptimizeDatabase : IJob {

    #region Constructor
    public OptimizeDatabase(DataContext dataContext) {
        DbContext = dataContext;
    }
    #endregion

    #region Fields
    private readonly DataContext DbContext;
    #endregion

    #region Methods
    public async Task Execute() {
        Stopwatch stopWatch = new Stopwatch();
        stopWatch.Start();
        string result = "Ok";
        try {
            // Rebuild Indexes
            DbContext.Database.ExecuteSqlCommand("EXEC sp_MSforeachtable \"ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=OFF)\"");
            // Update Statistics
            DbContext.Database.ExecuteSqlCommand("EXEC sp_updatestats;");
        }
        catch (Exception ex) {
            result = ex.Message + Environment.NewLine + ex.StackTrace;
        }
        stopWatch.Stop();
        DbContext.TaskLogs.Add(new TaskLog {
            Date = DateTime.Now,
            ElapsedSeconds = stopWatch.Elapsed.TotalSeconds,
            Result = result,
            Task = "Optimize Database"
        });
        await DbContext.SaveChangesAsync();
    }
    #endregion
}

And it's configured to run in Startup.cs

RecurringJob.AddOrUpdate<OptimizeDatabase>(x => x.Execute(), Cron.Daily(10));

All other scheduled tasks execute without issue, however, this one always throws the following error:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

Any ideas or insights are appreciated.

Query you are running is not completing withing the default CommandTimeout value, you can increase the CommandTimeout to a higher number if you can't further optimize the query. — PSK, Jan 20 '19 at 06:05

score 2 · Answer 1 · edited Nov 18 '21 at 13:42

The answer to your question is pretty simple. You are trying to rebuild the indices for all tables in your database and executing this statement via a hangfire job. The Hangfire jobs now try to rebuild the index for their own tables and that creates a deadlock.

You have to rebuild the indices explicitly for all your tables one after one like:

 ALTER INDEX ALL ON [dbo].[A] REBUILD;
 ALTER INDEX ALL ON [dbo].[B] REBUILD;

score 0 · Answer 2 · edited Nov 18 '21 at 13:42

The timeout issue is because one of the queries is taking longer that it should.

In .Net there are 2 timeout as far as I know, the connection timeout (ConnectionTimeout Property) and the command timeout (CommandTimeout Property). Both timeout default time is 30 seconds.

I recommend you to:

Run your queries at the SQL Management Studio to have an idea about the time needed to run both queries in sequences as your code shows.
Set the connection timeout and command timeout when the amount of second from the previous run at SQL Management Studio. If the timeout continues to appears, try adding more time to each timeout, add 30 seconds to each timeout until you find the minimal needed time to execute your queries. Once you find it, add 30 seconds extra to each timeout just to be sure.

Taking part of your code, the change would be something like:

try {
        // Change CommandTimeout
        DbContext.Database.CommandTimeout = 120;
        // Rebuild Indexes
        DbContext.Database.ExecuteSqlCommand("EXEC sp_MSforeachtable \"ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=OFF)\"");
        // Update Statistics
        DbContext.Database.ExecuteSqlCommand("EXEC sp_updatestats;");
    }
    catch (Exception ex) {
        result = ex.Message + Environment.NewLine + ex.StackTrace;
    }

You can take a look at this article, it explains good scenarios of possible timeout causes https://stackoverflow.com/a/8603111/2654879 .

score 0 · Answer 3 · answered Oct 25 '22 at 15:32

We had face same issue, where we have to do somany table operations in a single transaction scope. The hangfire jobs get failed with the same error when multiple jobs runs parallel. The way we solved this issue is by increase the command timeout value(By default it's 30 second)

Hangfire causes Timeout expired exception when executing SQL Code to optimzie database - ASP.NET Core

3 Answers3