SQL Server "The wait operation timed out" when inserting records on remote server, but not locally

Question

There is a weird problem with a deployed Windows application that uses a remote connection string to SQL Server 2012.

When inserting records, the SQL Server times out after a relatively short time saying "The wait operation timed out". I'm not able to debug the deployed application to find out why it is happening and where in the code it is happening.

However, I don't get this error when using the same database on the development machine, with a local connection.

Generally the code used is:

void MapData( SqlTransaction transaction, Dictionary<int, IDataObject> items )
{
    foreach ( var i in items )
    {
        transaction.Save( "CHECKPOINT" );

        try
        {
            ImportItem( transaction, i );
        }
        catch ( Exception e )
        {
            transaction.Rollback( "CHECKPOINT" );
        }
    }

    ReportStatus();
}   

While this code has been working, I am uncertain about remote connections. We only have this one single case where it does NOT work.

What can it be? Is there a more solid or performant approach than using Save() and Rollback() in a loop? I don't want to use TransactionScope to spawn new "child" transactions.

Thanks!

Answer 1

Your transaction is taking too long (not sure if it's committing or rolling back). In order to understand why you'd have to run a trace to get performance metrics.

But to get it working you could increase your timeout. Set the SqlCommand CommandTimeout to a larger value or 0 (no timeout). Also, the connection timeout is used for the transaction timeout - usually an issue only on expensive rollbacks. You specify this in the connection string like Connection Timeout=30.