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!

@DavidBeaumont The Commit() doesn't happen in the function I pasted, but this is working code, except for this one instance where the SQLServer happens to be accessed remotely. — John, May 14 '15 at 07:52
Any news on this issue ? Running into something similar and need ideas. — TheLegendaryCopyCoder, Feb 25 '16 at 13:45
By default, If you, as a developer, is debugging code that is running in a TransactionScope block then all operations against the table(s) involved in that tx will go into WAIT. — Ross Bush, Jan 03 '22 at 02:11

score 0 · Answer 1 · answered May 13 '15 at 15:33

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.

Thanks. I did try increasing the connection timeout but it did not help. — John, May 14 '15 at 07:51

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

1 Answers1