SQL command blocking controller requests in C # MVC

1

I'm implementing in a C # MVC project, a routine to perform database restore.

I have a problem that when I start the restore, the requests of other controllers are stuck waiting for the SQL to finish.

What I found strange is that if you are in debug mode with breakpoint inside the actions it does not crash, but posted, crashes .

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. Image shows execution.

Additional data [Issue 2]

A colleague put it down that could be problem with queuing. However, if you start the restore by the local application and go in the published version, there is no problem blocking the transactions and correctly shows the percentage completed.

    
asked by anonymous 29.06.2018 / 19:00

1 answer

0

Restore will block other requests from the database, on your machine it works because the database should be smaller in size, already in production the size of the database should be larger, besides other factors such as processing, latency (if the bank is hosted elsewhere).

    
02.07.2018 / 21:44