none
ado.net looses connection while executing a sp that updates lot of records RRS feed

  • Question

  • Hi all, this is little strange. I have a code that has been working fine, but it kind of fails when I do a large transaction. I am attaching my code below. In this block it calls a store proc that updates 2M records. When that happens nothing comes back to the code and it silently dies. Dont know what happens inside. No error/exceptions thrown neither in the database nor in the application code. 

    Does anyone face similar issue? What could be wrong? Can someone help me with this problem?

    Note: this works fine with smaller transactions. 

    thanks

    Elangovan S

     

                            using (SqlConnection conn = new SqlConnection(DBConnectionInfo.GetConnString()))
                            {
                                conn.Open();
                                //////////////////
                                using (SqlCommand arithabortCmd = new SqlCommand("SET ARITHABORT ON", conn))
                                {
                                    arithabortCmd.ExecuteNonQuery();
                                }
                                /////////////
                                using (SqlCommand cmd = new SqlCommand(context.StoredProcedureName, conn))
                                {
                                    cmd.CommandType = context.CommandType;
                                    cmd.CommandTimeout = 7200;
                                    SqlParameterCollection parms = cmd.Parameters;
                                    parms = cmd.Parameters;
                                    foreach (SqlParameter p in context.SqlParamList)
                                    {
                                        parms.Add(p);
                                    }
    
                                    cmd.ExecuteNonQuery();
                                    parms.Clear();
                                 
                                }
    
                                conn.Close();
                                conn.Dispose();
                            }
    



    Elangovan S
    Friday, April 2, 2010 1:50 AM

Answers

  • OK. We resolved the issue or at least figured what went wrong. This issue neither was in Stored procedure side nor in ado.net side. 

    The whole ETL process was dealing with almost 4M records in different type of entities. Since the process was taking longer, ASP.net thinks that the thread was idle and kills the thread underneath. So we made a change to app pool configuration under "app pool -> properties -> performance tab -> Stop worker process after being idle for time". Tune based on your requirement. 

     

    HTH. 

    Elangovan S


    Elangovan S
    Thursday, April 8, 2010 4:55 AM

All replies

  • Hmmm...I thought setting ARITHABORT to ON would mean if it comes across any errors the BATCH would terminate without fail or error. 

    Have you tried it without that?

     

    Friday, April 2, 2010 2:55 AM
  • Thanks for the reply. No I havent tried to remove ARITHABORT. My understanding was that this will only ignore errors with "divide by zero". But I could be wrong. Anyways, I will try remove this and see what is the result. 

    However, do you know if there are any issues with ado.net that could swallow exception or close the connection or any such issues when dealing with 2m records manipulations? When I execute the sql stored procedure it runs fine and takes about 14 minutes to run. 

     

     

    thanks

    Elangovan S


    Elangovan S
    Friday, April 2, 2010 2:50 PM
  • I really think it is the ARITHABORT...From the MSDN doc.

    "If SET ARITHABORT is ON, these error conditions cause the query or batch to terminate. If the errors occur in a transaction, the transaction is rolled back. If SET ARITHABORT is OFF and one of these errors occurs, a warning message is displayed, and NULL is assigned to the result of the arithmetic operation."

    http://msdn.microsoft.com/en-us/library/aa259212(SQL.80).aspx

     

     

    Friday, April 2, 2010 4:35 PM
  • Hi,

     

    I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions? 

     

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, April 8, 2010 1:18 AM
    Moderator
  • OK. We resolved the issue or at least figured what went wrong. This issue neither was in Stored procedure side nor in ado.net side. 

    The whole ETL process was dealing with almost 4M records in different type of entities. Since the process was taking longer, ASP.net thinks that the thread was idle and kills the thread underneath. So we made a change to app pool configuration under "app pool -> properties -> performance tab -> Stop worker process after being idle for time". Tune based on your requirement. 

     

    HTH. 

    Elangovan S


    Elangovan S
    Thursday, April 8, 2010 4:55 AM