How to stop SQL time out issue

Question

I keep getting "System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." 9 out of 10 times when I try to see workflow history. here is what I got from the SP log. I binged the error but not finding any resolution. Spent 6 hours trying to find a solution or looking at SQL log but not finding anything that stands out. I want to find out if anyone else has encounter this before and what is the fix.

// Error

Error establishing database connection. SQL Error: -2.: System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.    
 at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)    
 at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)    
 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.ExecuteReader(CommandBehavior behavior)    

Answer 1

Are you only receiving this timeout error in one particular webapp/site collection/subsite/list workflow history or does this seem to happen intermittently and not specific to any one item? Look over the suggestions provided here: http://blogs.technet.com/b/wbaer/archive/2009/10/06/intermittent-database-server-connectivity-and-microsoft-sharepoint-products-and-technologies.aspx. We increased our timeout to 45 seconds per that article, which makes a change to the server's dsn (HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\14.0\Secure\configdb\DSN)

Answer 2

It seems you are putting a heavy load on the server and that cannot be handled by the SQL Server itself. I would suggest splitting it into chunks, executing one by one and also increase the timeout to something relatively better for the particular query.

I think it is ok to increase timeout upto 90 seconds depending on the situation and then can rollback after its done or specify timeout to the specific command.

Also keep a track of timing and plan the future as you may experience the same in future with the solution getting much complicated and heavy with data.