'SqlDataReader timeout exception

I have some code that periodically runs a query against a SQL Server database and stores the rows in a dictionary. This code has been running fine in our production environment for about 3 years. Just recently, it has been crashing with an unhandled exception. For troubleshooting purposes, I've removed everything but the column reads, and wrapped everything in a try-catch. Here is the exception:

A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. 

   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
   at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()
   at System.Data.SqlClient.TdsParserStateObject.ReadByteArray(Byte[] buff, Int32 offset, Int32 len)
   at System.Data.SqlClient.TdsParser.SkipValue(SqlMetaDataPriv md, TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.SkipRow(_SqlMetaDataSet columns, Int32 startCol, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.CleanPartialRead()
   at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)

I'm using something very similar to:

// query is a simple select from 1 table, not long running by any means
string query = "SELECT col1, col2, col3, col4 FROM db.dbo.tbl_name WITH (nolock)"; //connection timeout
string query = "SELECT col1, col2, col3, col4 FROM db.dbo.tbl_name WITH (nolock) order by col1, col2"; //connection does not time out

SqlCommand command = new SqlCommand(query,connection)
SqlDataReader reader = command.ExecuteReader();

while (!reader.IsClosed && reader.Read()) {
  try {
    string test0 = reader[0].ToString();
    string test1 = reader[1].ToString();
    string test2 = reader[2].ToString();
    string test3 = reader[3].ToString();
    // here is where I would normally processes and store into dictionary
  }
  catch (Exception e){
    //make some noises
  }
}

When I run the query with other methods, It returns almost instantly (well under a second), but just to see what would happen, I increased the CommandTimeout to 60 seconds (from the default 30), which just increased the amount of time my program would hang before throwing an exception.

At @frisbee's suggestion I added an order by clause to the query, which stops the connection from timing out.

What I think is happening is that one of the Read() operations is not returning, and then causing the connection to timeout, but I have no idea what would cause this. This usually happens on a certain row when reading column 3, but not always. The query returns just under 50k rows, and sometimes it will make it through all, and sometimes only through 15k

c++sharp.netsql-server


Solution 1:[1]

Why don't you go ahead and set the CommandTimeout property of your SqlCommand instance to a high number? This will get your code working.

On the other end, you'll need to debug whatever's taking the server so long to finish its work. You can't do anything about that from the .NET side. You'll have to step through the underlying code that is executed on the SQL Server.

Solution 2:[2]

Why is it you are checking if the reader is closed on your while loop?

Try using using to ensure things are getting handled correctly. The following should make everything flow smoothly for you.

using (SqlConnection connection = MyDBConnection)
{
    connection.Open();
    SqlCommand command = new SqlCommand("SQL Query Here", connection) { CommandTimeout = 0 };


    using (var reader = command.ExecuteReader())
    {
        try
        {
            while (reader.Read())
            {

                string test0 = reader[0].ToString();
                string test1 = reader[1].ToString();
                string test2 = reader[2].ToString();
                string test3 = reader[3].ToString();

            }

        }
        catch (Exception e)
        {

            //Make some Noise

        }
    }
}

Solution 3:[3]

Every other run? Are you possibly sharing a command?
If you are using MARS and sharing a connection - don't 

using (SqlCommand cmd = con.CreateCommand) 
{   
    using (SqlDataReader rdr = cmd.ExecuteReader())
    {
    }
}

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Xavier J
Solution 2 E-French
Solution 3

Related Questions