Windows Service SQL timeout exception

Question

I have a Windows service which continuously each 10 seconds queries the SQL Server database table, and I also have a web site which also queries the same database table after 25 seconds.

After some time I get exception - server timeout exception has occurred. I went through stack-overflow site and has done the changes to SQL configuration settings but still I got the same exception. What to do?

Stack Trace

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(CommandBehavior behavior)
at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteReader(DbCommand command, CommandBehavior cmdBehavior) at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(DbCommand command) at Microsoft.Practices.EnterpriseLibrary.Data.CommandAccessor1.<Execute>d__0.MoveNext() at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source) at System.Runtime.Remoting.Messaging.Message.Dispatch(Object target, Boolean fExecuteInContext) at System.Runtime.Remoting.Messaging.StackBuilderSink.SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext)

Show us the full stack trace, and exception message, to start with. — Adriaan Stander
You need to determine where this happening. Is it the connection, is it a transaction, command etc. use some logging to determine where the error is and post the exception trace. That should give you some clues as to what needs adjusting. As it stands your question is like mind reading. — Preet Sangha

Nelson Patricio Jimenez Nelson Patricio Jimenez · Accepted Answer · 2012-10-23T04:56:18

Show me the code! timeout exception usually are because you don't close the database connection.

try add the code in the final of algorithm

if (connection.State == System.Data.ConnectionState.Open)
    {
        connection.Close();
        connection.Dispose();
    }

If you use to EnterpriseLibrary, try add more time to commandTimeOut

DbCommand dbcommand = database.GetStoredProcCommand("usp_TheStoredProcedureName");
dbcommand.CommandTimeout = 120;

Windows Service SQL timeout exception

1 Answers