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?

Pause the debugger during the long waiting period. Post the full stack trace here including external code. After doing that, see if your statement is blocked (maybe using sp_who2). — usr, Jun 23 '14 at 10:01
@usr: with the debugger, doing step-by-step, never triggers the error, and stepping over a single SQL clause is (almost) immediate. Furthermore, the problem is triggered only via a virtual machine, and I don't know how to use the debugger "remotely"... — Nova, Jun 23 '14 at 12:52

score 2 · Accepted Answer · edited May 23 '17 at 10:26

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.

SQL error timeout with transaction

1 Answers1