SQL command blocking controller requests in C# MVC

Question

I am implementing in a C# MVC project, a routine to run the database Store.

I’m having trouble that when I start doing the Store, other requests controllers are locked, waiting for SQL to finish.

What I found strange is that if it is in debug mode with breakpoint dentros actions does not lock, but published, latch.

My controller

public class DBController : AsyncController 
{

    string configCoxenao = "Data Source=VSRV-SQL2016; Initial Catalog=master; User Id=sa;Password=pass; MultipleActiveResultSets=True";

    public ActionResult SQLServer()
    {
        var lista = new List<string>();
        var listaFinal = new List<string>();

        using (SqlConnection conexao = new SqlConnection(configCoxenao))
        {
            var sql = "select * from sys.databases where name not in ('master', 'tempdb', 'model', 'msdb')";
            var sqlFilial = "select top 1 EF_NOME from [{0}].dbo.FILIAL";

            conexao.Open();

            var cmd = new SqlCommand(sql, conexao);                
            using (var reader = cmd.ExecuteReader())
            {                    
                while (reader.Read())
                {
                    lista.Add(reader["name"].ToString());
                }
            }

            foreach (var item in lista)
            {

                try
                {
                    var cmdFilial = new SqlCommand(String.Format(sqlFilial, item), conexao);
                    using (var readerFilial = cmdFilial.ExecuteReader())
                    {
                        while (readerFilial.Read())
                        {
                            listaFinal.Add("<b>" + item + "</b> - " + readerFilial["EF_NOME"].ToString());
                        }
                    }
                }
                catch
                {
                    listaFinal.Add(item);
                }
            }                
        }

        return View(listaFinal);
    }

    public async Task<ActionResult> SQLServerRestore()
    {
        var lista = new List<string>();

        using (SqlConnection conexao = new SqlConnection(configCoxenao))
        {
            var sql =
                "select " +
                "  session_id as SPID, " +
                "  command, " +
                "  SUBSTRING(a.text, 19, CHARINDEX(' ', a.text, 19) - 19) as BancoDeDados, " +
                "  convert(varchar(10), start_time,  103) + ' ' + convert(varchar(10), start_time,  114) DataHoraInicio, " +
                "  percent_complete PercComplentado, " +
                "  convert(varchar(10), dateadd(second,estimated_completion_time/1000, getdate()), 103) + ' ' + " +
                "  convert(varchar(10), dateadd(second,estimated_completion_time/1000, getdate()), 114)  as DataHoraEstmidaFim " +
                "from " +
                "  sys.dm_exec_requests r  " +
                "  CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a  " +
                "where  " +
                "  r.command in ('BACKUP DATABASE','RESTORE DATABASE') ";
            conexao.Open();

            var cmd = new SqlCommand(sql, conexao);
            using (var reader = await cmd.ExecuteReaderAsync())
            {
                while (reader.Read())
                {
                    lista.Add(
                        "<b>" + reader["BancoDeDados"].ToString() + "</b> " +
                        reader["PercComplentado"].ToString() + " %<br />" +
                        "Data e Hora Início: " +
                        reader["DataHoraInicio"].ToString() + " / " +
                        "Data e Hora Fim Estimada: " + 
                        reader["DataHoraEstmidaFim"].ToString()
                    );
                }
            }
        }
        return View(lista);
    }

    public async Task<ActionResult> SQLServerExecutaRestore(string Arquivo, string Base)
    {
        using (SqlConnection conexao = new SqlConnection(configCoxenao))
        {
            var sql = String.Format("exec master.dbo.RestoreDataBase @arquivo = '{0}', @banco = '{1}'", Arquivo, Base);
            conexao.Open();

            var cmd = new SqlCommand(sql, conexao);
            await cmd.ExecuteReaderAsync();
        }
        return Json("Ok", JsonRequestBehavior.AllowGet);
    }
}

Additional data

I am connecting in the master database to do SQL and in Microsoft SQL Server Managemment Studio this does not occur. Picture shows the execution.

Additional data [Edition 2]

A colleague put down that could be trouble with rowing. But if you start the Store from the local application and go to the published version, there is no problem blocking transactions and correctly shows the completed percentage.

Answer 1

The Store will block the other requests of the database, on your machine it works because the database must have a smaller size, already in production the size of the database should be bigger, besides other factors such as processing, latency (if the database is hosted in another location).