0

In connectivity of SQL database using ADO.Net C#, I am getting exceptions randomly while working with thousands of data simultaneously which is being executed in thread such as

  • A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The wait operation timed out.)

  • The client was unable to establish a connection because of an error during connection initialization process before login. Possible causes include the following: the client tried to connect to an unsupported version of SQL Server; the server was too busy to accept new connections; or there was a resource limitation (insufficient memory or maximum allowed connections) on the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

  • Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

My connection string is as 

<add name="ConnDBString" connectionString="datasource;Initial Catalog=dbname;pooling=true;connection lifetime=120;Max Pool Size=1000" providerName="System.Data.SqlClient"/>

In reference of other questions I have optimized my code for connection establishment like below

public static int ExecuteNonQuery(string commandText, CommandType commandType, ref List<SqlParameter> parameters)
    {
        int result = 0;
        if (!string.IsNullOrEmpty(commandText))
        {
            using (var cnn = new SqlConnection(Settings.GetConnectionString()))
            {                    
                var cmd = cnn.CreateCommand();
                cmd.CommandText = commandText;
                cmd.CommandType = commandType;
                cmd.CommandTimeout = Convert.ToInt32(Settings.GetAppSetting("CommandTimeout") ?? "3600");
                cmd.Parameters.AddRange(parameters.ToArray());
                cnn.Open();
                result = cmd.ExecuteNonQuery();
                cmd.Dispose();                    
            }
        }
        return result;
    }

Please advise.

Pinki
  • 71
  • 1
  • 10
  • Did you database server run out of RAM? – mjwills Dec 19 '18 at 10:25
  • Yes, it does sometimes. – Pinki Dec 19 '18 at 10:50
  • So that sounds pretty consistent with your second bullet point? – mjwills Dec 19 '18 at 10:51
  • Is there any other possibilities for causing those issues instead of RAM lacking? – Pinki Dec 19 '18 at 11:21
  • Yes - you could have more connections to the database than the server could handle, for example. There are heaps of reasons why it could occur. – mjwills Dec 19 '18 at 11:38
  • @mjwills I have tried this in higher configuration but still this issues are occurring. – Pinki Dec 22 '18 at 05:53
  • As per my requirement, I have to import bulk of data simultaneously, so that it's suffering from connection and pool lacking. Is there any other way to achieve it or any other alternative? – Pinki Dec 22 '18 at 05:56
  • If you need to do bulk insert operations, why do you execute them one by one? https://stackoverflow.com/questions/13722014/insert-2-million-rows-into-sql-server-quickly – ZorgoZ Dec 25 '18 at 07:43
  • @ZorgoZ You are right. but as per my requirement i have to execute them one by one. – Pinki Dec 25 '18 at 11:43
  • @Pinki, than you will be facing performance issues, and as you already do - resource issues. It is not for fun, that the guys at Microsoft have implemented those batch operations. Your requirements are bad. This looks like an architectural flaw. Or upgrade the hardware, and maybe the SQL Server edition as well, – ZorgoZ Dec 25 '18 at 11:54

1 Answers1

0

Please make sure whether your ram is reaching max limit as @mjwills say . and i have write something . you can try this which might help you for max pool size issue .

 public static int ExecuteNonQuery(string commandText, CommandType commandType, ref List<SqlParameter> parameters)
        {
            int result = 0;
            if (!string.IsNullOrEmpty(commandText))
            {
                using (var cnn = new SqlConnection(Settings.GetConnectionString()))
                using (var cmd = cnn.CreateCommand())
                {
                    cmd.CommandText = commandText;
                    cmd.CommandType = commandType;
                    cmd.CommandTimeout = Convert.ToInt32(Settings.GetAppSetting("CommandTimeout") ?? "3600"); 
                    cmd.Parameters.AddRange(parameters.ToArray());
                    cnn.Open();
                    result = cmd.ExecuteNonQuery();
                    cnn.Close();
                }
            }
            return result;
        }

you can also make this method sync for utilise your memory .

Nikunj
  • 195
  • 10