C# ASP.Net: Exception (some times only) during long database operation

We have a ASP.Net web application that connects to its business layer using WCF. Sometimes, I am getting exception when it is performing a huge operation. The interesting part is when I ran it for the first time it was successful. Afterwards it is throwing exception.

Exception: The socket connection was aborted.

The operation is that it uploads zipcodes into database tables from a csv file. First we read from csv file and make a single lengthy string of zipcodes. This is passed to the stored procedure and the database operations are performed.

Note 1:) When I placed breakpoint and tested, it is clear that the creation of the string (after taking data from csv and appending) is pretty fast (less than one minute).

Note 2:) I removed the transaction (from C# code) and tested, even then the exception is there.

Note 3:) There are one lakh (one hundred thousand) records in csv. Each row has four columns (ZipCode, City, County, State). Size of the csv file is 3MB.

I had a doubt about the transaction log size. Then I shrinked the log file using the following command. DBCC SHRINKFILE('MyDB_log', 1) GO

Then I checked the log size using SELECT [Size],Max_Size,Data_Space_Id,[File_Id],Type_Desc,[Name] FROM FRAMIS_R2075.sys.database_files WHERE data_space_id = 0

The Size is 128; Max Size is 268435456; Type_Desc = “LOG”

Even after shrinking the exception is still coming.

Framework: .Net 3.0

DB: SQL Server 2005

Well, it seems like there is an excpetion in the business layer too, when I waited for 20 more minutes. It said, “Invalid Attempt to call Read when the reader is called”. By seeing this, I removed the DbDataReader and used SqlCommand to update the database tables. Again, there came an exception in business layer, after some 20 minutes saying “Timeout exception”. Any idea why this is happening?

  private void ProcessDatabaseOperationsForZipCode(StringBuilder dataStringToProcess, int UserID)
    {
        int CountOfUnchangedZipCode = 0;
        string strRetiredZipCode = "";
        string strNewZipCode = "";
        dataStringToProcess.Remove(dataStringToProcess.Length - 1, 1);


        if (dataStringToProcess.Length > 0)
        {

            //TimeSpan.FromMinutes(0) - to make transaction scope as infinite.
            using (TransactionScope transaction = TransactionScopeFactory.GetTransactionScope(TimeSpan.FromMinutes(0)))
            {


                SqlConnection mySqlConnection = new SqlConnection("data source=myServer;initial catalog=myDB; Integrated Security=SSPI;");
                SqlCommand mySqlCommand = new SqlCommand("aspInsertUSAZipCode", mySqlConnection);
                mySqlCommand.CommandType = CommandType.StoredProcedure;
                mySqlCommand.Parameters.Add("@DataRows",dataStringToProcess.ToString());
           开发者_StackOverflow中文版     mySqlCommand.Parameters.Add("@currDate", DateTime.Now);
                mySqlCommand.Parameters.Add("@userID", UserID);
                mySqlCommand.Parameters.Add("@CountOfUnchangedZipCode", 1000);
                mySqlCommand.CommandTimeout = 0;
                mySqlConnection.Open();
                int numberOfRows = mySqlCommand.ExecuteNonQuery();

         //Database db = DatabaseFactory.CreateDatabase();
                //DbCommand cmd = db.GetStoredProcCommand("aspInsertUSAZipCode");
                //cmd.CommandTimeout = 0;
                //db.AddInParameter(cmd, "@DataRows", DbType.String, dataStringToProcess.ToString());
                //db.AddInParameter(cmd, "currDate", DbType.DateTime, DateTime.Now);
                //db.AddInParameter(cmd, "userID", DbType.Int32, UserID);
                //db.AddOutParameter(cmd, "CountOfUnchangedZipCode", DbType.String, 1000);


                //using (DbDataReader rdrUpgradeTypes = (DbDataReader)db.ExecuteReader(cmd))
                //{
                //    //while (rdrUpgradeTypes.Read())
                //    //{
                //    //    if (!String.IsNullOrEmpty(Utility.GetString(rdrUpgradeTypes, "NewZipCode")))
                //    //    {
                //    //        strNewZipCode = strNewZipCode + "," + Utility.GetString(rdrUpgradeTypes, "NewZipCode");
                //    //    }
                //    //}
                //}


                transaction.Complete();

            }
        }


    }

The problem is likely that the connection to the SQL Server times out - the default timeout for both connections and commands is 30 seconds.

You can either up the timeout (on the connection string and in code) or break up the updates into chunks.

Thanks Oded for patiently answering my questions. My problem got resolved when I splitted the huge database operations into small batches (after configuring MSDTC).

During Large Database operations, insufficient memory error may come. But this error may not be visible if it is inside a Transaction.

1) Remove transaction and see if “Insufficient Memory” exception is available.

2) Breakdown large database operations into small batches to remove the “Insufficient Memory” exception

3) Configure MSDTC in app tier and database

4) Ensure that WCF does not time out abruptly. It should have enough time based on database operations.

5) Observe the behavior when the database server is restarted (This is last option)

Some useful information available in

There is insufficient system memory in resource pool 'default' to run this query

MSDTC Exception during Transaction: C#

C# ASP.Net: Exception (some times only) during long database operation

SQL Server 2005 Error 701 - out of memory

Some other checks:

1) Whether the windows account under which the database engine is running have "Lock pages in memory permission"?

2) Chek SQL Sever service pack version.

3) Check size of virtual memory paging file

4) Check max server memory

5) Determining MemToLeave Settings

6) "SQL Server Memory Configuration and MemToLeave"