After timeout expired error on executing SQL 2005 stored procedure continues to timeout until restarting the C# .net program.
The problem:
I have a program that is running constantly, and every hour runs a piece of code, including an SQL stored procedure. Every once in awhile, a timeout expired occurs when trying to execute the SQL 2005 stored procedure. Once this error occurs, the program catches the error and lets me know via email, but continues to run. BUT, once the error occurs, it continues to occur everytime that code is executed (on the hour). If I close the program, and open it up again, the code runs fine, sometimes for days, sometimes for a week.
I have heard that it is a good idea to change the connection timeout to 300 or maybe even to 0, but if it was a timeout problem, wouldn't the code timeout, even when I restart the program?
Also, is there a reason that once the timeout expires it continues to expire every time after that, until the restart?
I have a program that is running constantly, and every hour runs a piece of code, including an SQL stored procedure. Every once in awhile, a timeout expired occurs when trying to execute the SQL 2005 stored procedure. Once this error occurs, the program catches the error and lets me know via email, but continues to run. BUT, once the error occurs, it continues to occur everytime that code is executed (on the hour). If I close the program, and open it up again, the code runs fine, sometimes for days, sometimes for a week.
I have heard that it is a good idea to change the connection timeout to 300 or maybe even to 0, but if it was a timeout problem, wouldn't the code timeout, even when I restart the program?
Also, is there a reason that once the timeout expires it continues to expire every time after that, until the restart?
An exception has occurred while attempting to process Production Transaction Queue.
************************
Exception Details:
************************
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
>7
************************
Application 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(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
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.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters)
at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String connectionString, String spName, Object[] parameterValues)
at Company.BarcodeWMS.Transactions.DataAccess.Classes.ProductionTransactionsDataAccess.CreateProductionTransaction() in E:\non vss source\BarcodeWMS\Barcode Sonic Interface Service - PreQuebec\Company.BarcodeWMS.Transactions.DataAccess\Classes\ProductionTransactionsDataAccess.cs:line 74
at Company.BarcodeWMS.Transactions.BusinessLogic.Classes.ProductionTransactionBusinessLogic.ProcessProductionTransactionQueue() in E:\non vss source\BarcodeWMS\Barcode Sonic Interface Service - PreQuebec\Company.BarcodeWMS.Transactions.BusinessLogic\Classes\ProductionTransactionBusinessLogic.cs:line 96
got it!!! the timeout is in milliseconds, if you need to give 10 seconds, give 10000
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
usually, this is because sql server has taken ALL the RAM of the machine on the server, and starts swapping.
you should ensure that the MAX MEMORY SETTING on the server is NOT the default (just a big number to say: take it all), but some realistic value.
for example, on a dedicated sql server box with 4GB RAM, the setting should be 3500 (MB), for example
you should ensure that the MAX MEMORY SETTING on the server is NOT the default (just a big number to say: take it all), but some realistic value.
for example, on a dedicated sql server box with 4GB RAM, the setting should be 3500 (MB), for example
>got it!!! the timeout is in milliseconds, if you need to give 10 seconds, give 10000
I have to disagree on that one, actually.
I have to disagree on that one, actually.
ASKER
rrjegan17: I have started using profiler. Not sure if this will help me find the problem, but maybe.
roshmon: Timeout in milliseconds does not help, because default timeout was 30 seconds.
angelIII: I will check the RAM the next time this occurs, but remember, I do not have to restart the SQL server for the program to work again, I need to restart just the program. So maybe it has to do with transactions / open connections.
roshmon: Timeout in milliseconds does not help, because default timeout was 30 seconds.
angelIII: I will check the RAM the next time this occurs, but remember, I do not have to restart the SQL server for the program to work again, I need to restart just the program. So maybe it has to do with transactions / open connections.
>> I have started using profiler.
Capture all events and list out all queries having Higher CPU, Reads and Writes and tune those queries which might help.
>> So maybe it has to do with transactions / open connections.
Make sure that your application closes all active connections properly as mentioned earlier..
Capture all events and list out all queries having Higher CPU, Reads and Writes and tune those queries which might help.
>> So maybe it has to do with transactions / open connections.
Make sure that your application closes all active connections properly as mentioned earlier..
ASKER
>>>> So maybe it has to do with transactions / open connections.
>>Make sure that your application closes all active connections properly as mentioned earlier..
If it is not closing connections properly, how would I be able to easily check this?
i.e.
Would I not be able to connect to the database from other applications / queries?
Would my current connections be over 50, etc.?
Thanks.
>>Make sure that your application closes all active connections properly as mentioned earlier..
If it is not closing connections properly, how would I be able to easily check this?
i.e.
Would I not be able to connect to the database from other applications / queries?
Would my current connections be over 50, etc.?
Thanks.
ASKER
I've been looking around, could it be because I need to set the timeout for my SqlHelper.
The Command I that is getting the error is:
SqlHelper.ExecuteNonQuery(
The Command I that is getting the error is:
SqlHelper.ExecuteNonQuery(
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Check for the following:
* Whether connections are closed properly once it is completed
* Whether transactions are closed out properly.
* Whether blockings, deadlocks are occuring on your tables
* Whether there are long running queries..
Request you to use profiler to find long running queries or queries involved in deadlock and tune out those queries so that they complete faster resulting in reduced timeouts.