SQL error timeout with transaction

Question

I have an ASP.NET application importing data from a CSV file, and storing it to a (SQL Server) database table. Basically, the import process consists of:

• Importing the raw CSV data into a corresponding SQL table (with the same columns)
• "Merging" the data into the DB, with some sql clauses (INSERTS and UPDATE)

The whole import procedure is wrapped with a transaction.

using (SqlConnection c = new SqlConnection(cSqlHelper.GetConnectionString()))
{
    c.Open();

    SqlTransaction trans = c.BeginTransaction();

    SqlCommand cmd = new SqlCommand("DELETE FROM T_TempCsvImport", c, trans);
    cmd.ExecuteNonQuery();

    // Other import SQL ...

    trans.Commit();
}

Trying this import procedure from a virtual machine (everything is local), I got an error

[SqlException (0x80131904): Timeout. The timeout period elapsed prior to completion of the operation or the server is not responding.

Trying the same without the transaction, works fine.

Something I tried:

• Executing the same queries from SQL Server Management Studio, all of them runs quite fast (500ms)
• Executing from my development machine, works fine
• Increasing the Command Timeout, I get the error anyhow. I also tried to set CommandTimeout to 0 (infinite), and the procedure seems to run "forever" (I get a server timeout, which I set to 10 minutes)

So, the final question is: why the SQL transaction is creating such problems? Why is it working without the transaction?

Answer 1

After several tests I did, I found out that the problem is ...Not Enough Memory!

What I found out is that my situation is exactly the same as this answer:

Help troubleshooting SqlException: Timeout expired on connection, in a non-load situation

I have both IIS and SQL server on my local machine, with the test running on a virtual machine. This virtual machine was using 2Gb of RAM, that is 50% of the total RAM of my PC. Reducing the RAM available to the virtual machine to 512Mb fixed the problem.

Furthermore, I noticed that using a transaction or not using it has exactly the same results, when the system is working, so my first guess was wrong, as well.