How to prevent this dling/sql error - or is it because bad network?

Question

Step 1) I check that i can acess server: (because i have offline handling to)

public static bool dbOnline(string timeOut="3")
{
  bool online = false;
       try
       {
           var testConn = new SqlConnection(Utils.ConnectionStr(timeOut)); 
           testConn.Open();
           online = true;
           testConn.Close();
       }
       catch (Exception)
       {
           online = false;
       }
       return online;
   }

2) It returns ok, then i Create dlinq instance !

_DataContext = new DataContext(Utils.ConnectionStr);//System.Data.Linq.DataContext
//then i point out a customer in DB

var customer = _DataContext.Customers.TableQuery().FirstOrDefault(c => c.ID == custid);

//Check its not null
if (customer != null)
{
            customer.points+= _points;
            customer.LastUpdated = DateTime.Now;
            customer.SubmitChanges();
}

3) And sometimes i get this error, is it anything i can do to prevent this, or is it to fix the network? (i know there is some trouble with the network), but can i do anything different? - Thanks

Error: *System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.*

The statement has been terminated.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult)
   at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries)
   at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
   at System.Data.Linq.ChangeDirector.StandardChangeDirector.DynamicUpdate(TrackedObject item)
   at System.Data.Linq.ChangeDirector.StandardChangeDirector.Update(TrackedObject item)
   at System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode)
   at System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)

Marc Gravell Marc Gravell · Accepted Answer · 2012-02-22T08:31:33

A timeout is impossible to identify in isolation, however I would usually expect a network error to have a different error. The things I'd look at first:

is the data indexed correctly
was there blocking at the time, most likely by a long-running transaction that has a lock on the data
was there anything like a db grow happening at the time
was the server exceptionally busy at the time

My money would be on the second

Also, we can't see what TableQuery() is; if that returns IEnumerable<Customer>, then it could be related to pulling too much data (in your context, it should probably be returning IQueryable<Customer> at the minumum, but we can't see).

The real thing to do here is to run a SQL trace and try to catch it in the act - see what was happening.

How to prevent this dling/sql error - or is it because bad network?

1 Answers