SqlDataReader timeout exception

Question

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

If you're suspecting column access is the culprit, remove that (for elimination purposes...). aggregate reader.FieldCount into an int within your loop and do nothing else (that's just so you "touch" each row somehow). see if you still get your timeouts. — Amit
– Amit, Commented Oct 13, 2015 at 20:03
so now you know it's not the columns (was unlikely to begin with). I'd be very surprised if this indeed isn't a DB issue. What has changed since 3 years ago? more data? — Amit
– Amit, Commented Oct 13, 2015 at 20:48
Not that this is a production fix but try with (nolock). If the problem goes away then most likely that problem is locks. Run a dbcc checkdb on the table and degfrag - you may have a data issue on the SQL side. — paparazzo
– paparazzo, Commented Oct 13, 2015 at 20:52
Then look at how you are handing the connection. Are you disposing rdr properly? — paparazzo
– paparazzo, Commented Oct 13, 2015 at 21:40

Xavier J · Accepted Answer · 2015-10-13 20:37:30Z

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.

answered Oct 13, 2015 at 20:37

Xavier J

4,7911 gold badge16 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Pete Over a year ago

I did set it to 60 seconds. But like I said, the server isn't taking that look to return the result. If I run the query in the sql management studio, the query comes back pretty close to instant. I think its timing out on the subsequent Read() operations when iterating over the result set that's causing the problem.

Pete Over a year ago

@Frisbee It should be able to mow through these rows in less than a second, let alone 30 or 60. I did set my timeout higher just to see if there was any effect, still throwing an exception.

paparazzo Over a year ago

@Pete Then see my comment. Do you have locks or data issues?

E-French · Accepted Answer · 2015-10-13 22:11:25Z

0

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

        }
    }
}

answered Oct 13, 2015 at 22:11

E-French

716 bronze badges

2 Comments

Pete Over a year ago

I was checking if the reader was closed because after catching the first exception, it would loop and to read from the closed reader... and throw another exception. I have since changed the code to wrap the whole while loop in a try/catch block.

E-French Over a year ago

Setting the command timeout to 0 will allow it to stay open infinitely, give that a try. If you suspect table locks use WITH (NOLOCK) on every table you bring in.

paparazzo · Accepted Answer · 2015-10-13 23:26:29Z

0

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())
    {
    }
}

edited Oct 13, 2015 at 23:26

answered Oct 13, 2015 at 21:52

paparazzo

45.2k24 gold badges110 silver badges180 bronze badges

Collectives™ on Stack Overflow

SqlDataReader timeout exception

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related