Wired random timeout expired issue in both ODBC and Oledb DataAdapter.Fill

Question

I have a web application running in Windows Server 2008 SP2/64bit/IIS 7/ASP.Net Framework 2.0. This web application is connecting to a MSSQL server located in the same box(MS SQL 2008 R2 SP1 64bit). In 99% of the time, the web app is working fine, however sometimes I encountered timeout expired issue in some queries which they are used to work perfectly fine.

The issue usually happened when using the DataAdapter.Fill to fill a select query into a dataset. These queries can be as simple as "select * from table_A" and return only 20+ rows. If I remove any 1 row from or add 1 new row into the table, the query can work. If I trim any field in any row, the query can work. If I wrap this query in a stored procedure and call the stored procedure, it can work. 

I have tested ODBC, Oledb and sqlClient driver, only sqlClient works without changing the data or query. Below is the detailed exception:

System.Data.Odbc.OdbcException: ERROR [HYT00] [Microsoft][ODBC SQL Server Driver]Timeout expired
  at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
  at System.Data.Odbc.OdbcDataReader.GetData(Int32 i, SQL_C sqlctype, Int32 cb, Int32& cbActualOut)
  at System.Data.Odbc.OdbcDataReader.internalGetString(Int32 i)
  at System.Data.Odbc.OdbcDataReader.GetValue(Int32 i)
  at System.Data.Odbc.OdbcDataReader.GetValues(Object[] values)
  at System.Data.ProviderBase.DataReaderContainer.CommonLanguageSubsetDataReader.GetValues(Object[] values)
  at System.Data.ProviderBase.SchemaMapping.LoadDataRow()
  at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
  at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
  at System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
  at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
  at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
  at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)

====================================================================================

System.Data.OleDb.OleDbException: Timeout expired
  at System.Data.OleDb.OleDbDataReader.ProcessResults(OleDbHResult hr)
  at System.Data.OleDb.OleDbDataReader.GetRowHandles()
  at System.Data.OleDb.OleDbDataReader.ReadRowset()
  at System.Data.OleDb.OleDbDataReader.Read()
  at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
  at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
  at System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
  at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
  at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
  at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)

---

kragon

Answer 1

Hi Kragon,

Since this is a random issue, here are two suggestions for you.

1. Make sure your SQL Server is fully patched to the latest SQL Server build. There are some known issues associated with “Timeout Expired” and the troubleshooting process might get unnecessary complicated if your SQL Server is not running in the latest build. Base on your description, you are using Service Pack 1 for SQL Server 2008 R2, please try to install the latest Service Pack from here.

2. The query that your application is sending to your SQL Server may be taking more than the default timeout time to complete. So in the application side, maybe you can try to increase the timeout value (The CommandTimeout property of Connection and/or Command object).

For more details, please refer to this blog: Posssible root causes for the timeout error: [Microsoft][ODBC SQL Server Driver] Timeout Expired.

If it doesn’t work for you, please provide more information like the simple code for getting better help.

Best Regards,
Ray Chen

Answer 2

Hi Ray, thanks for the reply. To debug this, I have change my code to simplest and increased the timeout to 1800 seconds, still not working. Below is my code:

ODBC version:

Dim myConn As OdbcConnection
Dim myAdapter As OdbcDataAdapter
Dim myDts As New DataSet
Dim strSql As String = "select * from table_issue"
myConn = New OdbcConnection(odbc_connection_string)
myAdapter = New OdbcDataAdapter(strSql, myConn)
myAdapter.SelectCommand.CommandTimeout = 1800
myAdapter.Fill(myDts,"table_issue")

Oledb version:

Dim myConn As OleDbConnection
Dim myAdapter As OleDbDataAdapter
Dim myDts As New DataSet
Dim strSql As String = "select * from table_issue"
myConn = New OleDbConnection(oldeb_connection_string)
myAdapter = New OleDbDataAdapter(strSql, myConn)
myAdapter.SelectCommand.CommandTimeout = 1800
myAdapter.Fill(myDts,"table_issue")

Some information to add:

I can use the MS SQL Studio to execute these queries without any issue.

The same script works if the web app is hosting in my desktop (windows XP 32bit IIS 6) connecting to the same database.

To me it is like the 64Bit driver having issue.

---

kragon

Answer 3

Hi Kragon,

Base on your sample code, I suggest you to use Using block, please try the below code:

Dim myDts As New DataSet
Dim strSql As String = "select * from table_issue"
Dim odbc_connection_string As String
Using myConn As New OdbcConnection(odbc_connection_string)
  Using myAdapter As New OdbcDataAdapter(strSql, myConn) 
    myAdapter.SelectCommand.CommandTimeout = 1800  
    myAdapter.Fill(myDts, "table_issue")            
  End Using
End Using

It’s same to OLEDB version.

For more details, please refer to this article: Using Statement (Visual Basic).

Best Regards,
Ray Chen

Answer 4

Hi Ray, I have changed the code to using block, but same issue :(

---

kragon

Answer 5

Hi Kragon,

Thanks for update.

Could you please share the related SQL error Log for further troubleshooting?

Best Regards,
Ray Chen

Answer 6

Hi Ray,

DBA has turnned on the SQL profiler to trace this issue, but it does not leave any log in SQL side.

In addition, I just found out that if I add "Set nocount on" before the "select * from table_issue", then it works.

But I still don't understand why select from a table with 23 rows and 7 columns(each column is less than 200 Char) will result in timeout.

---

kragon

Answer 7

Hi Kragon,

I’m very glad to hear that you have got this issue resolved.

According to your workaround, adding “set nocount on” before “select * from table_issue” worked. But you still want to know the root cause of the timeout expired issue, right?

Base on my research, “set nocount on” prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. For store procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduces. For more details, please refer to this article: SET NOCOUNT (Transact-SQL).

So when the setting SET NOCOUNT was off, the timeout expired issue was caused due to the performance issue.

Best Regards,
Ray Chen