"Timeout" and "A transport-level error" exception during multiple simple queries

Question

I have a console application that moves data from one database to another. App reads data, updates it and writes into another db. Queries are very simple and they are logged in case of error. So I can try to execute each of them separately and it doesn't take more than 50 ms (Profiler told me that). But I have a lot of rows and after executing some of them (1 000 - 10 000 very different amount of executed rows) I get a time out: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. TimeOut can be got even during "SELECT" query.

I've set CommandTimout to 120 and got A transport-level error has occurred when receiving results from the server. (provider: Session Provider, error: 19 - Physical connection is not usable) So, I think it's not a timeout case.

Profiler and traces didn't got anything unexpected.

I used SqlConnection.ClearPool(cnnct); but it doesn't work;

Please, help.

SQL Vertion: Microsoft SQL Server 2008 R2 (SP1)

Here is the code for source and destination dbs.

    public IEnumerable<SomeType> GetEntities()
    {
        IEnumerable<SomeType> result = null;
        var selectQuery = GetSelectQuery();

        try
        {
            using (var cnnct = new SqlConnection(_connectionString))
            {
                cnnct.Open();
                var cmd = new SqlCommand(selectQuery, cnnct);
                var reader = cmd.ExecuteReader();

                result = Convert(reader);
                reader.Close();
                cmd.Dispose();
            }
        }
        catch (Exception e)
        {
            Catch(selectQuery, e);
        }

        return result;
    }

    public Int32 Save(SomeType entity)
    {
        Int32 result = 0;
        var insertQuery = GetInsertQuery(entity);

        try
        {
            using (var cnnct = new SqlConnection(_connectionString))
            {
                cnnct.Open();
                var cmd = new SqlCommand(insertQuery, cnnct);

                result = (Int32)(Decimal)cmd.ExecuteScalar();
                cmd.Dispose();
            }
        }
        catch (Exception e)
        {
            Catch(insertQuery, e);
        }

        return result;
    }

have you tried setting the timeout to 0 (unlimited) to see if it actually finishes? — Tanner, Aug 31 '16 at 14:31
@Tanner I've replaced default 30 by 120 and got another exception (described in the initial post). I agree that unlimited is more than 120 but in any case I don't have any troubles with timeout when I chose 120 seconds. I need to find the real reason. Thanks. — Bikutotoro, Aug 31 '16 at 15:28
ADDITIONAL INFO: I've added SqlConnection.ClearAllPools(); after each connection using and got new exception: _Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=1; handshake=12345;_ — Bikutotoro, Sep 01 '16 at 11:25

score 0 · Answer 1 · answered Aug 31 '16 at 14:50

0

Possibly some misconfiguration of "remote query timeout" option?

Have a look into your instance

or

EXEC sp_configure 'remote query timeout', 0 ;  
GO  
RECONFIGURE ;  
GO

answered Aug 31 '16 at 14:50

Antonio

535
10
26

The query is too simple, something like SELECT * FROM Entities WHERE Id = 1; It returns couple of rows. Time spent for the query in database window is less than 50 ms. So the problem is not in timeout but somewhere else. And I want to resolve the real problem before I go to production. Time out isn't the real reason. And by the way, I increased timeout up to 120 and timeout exception was replaced by another one (described in the initial post). Thanks. – Bikutotoro Aug 31 '16 at 15:40
If changing Timeout settings is giving a new error than it HAS something to do with timeout setting. The error you have now indicates problem with Physical connection. Check stability of your client/server connection – Antonio Aug 31 '16 at 15:48
Could you please give a piece of advise how can I check this stability? – Bikutotoro Aug 31 '16 at 16:04
Actually I am a DBA and not a Network Expert. Do you have any Network Specialist in house?...if not...well try pinging the Ip Adress of the server for a few times and/or its name and see if the name is resolved and if any packet get lost – Antonio Sep 01 '16 at 07:57
sorry to bring this up again after 4 years. I was getting the same error and was able to correctly identify the network failure on windows by using the following command: ping [db server hostname or ip] -t > logfile.txt. Let it run for a while then kill the command with ctrl + c. That allowed me to confirm that the connection to the db server was being lost periodically. Running the same test from my colleague workstation showed no issues so it definitely was an issue with my PC network connection. – Kemuel Sanchez Jul 27 '20 at 02:31

"Timeout" and "A transport-level error" exception during multiple simple queries

1 Answers1