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?
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

Open in new window

sjamal_hsjAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> The timeout period elapsed prior to completion of the operation or the server is not responding.

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.
0
Roshan DavisCommented:
got it!!! the timeout is in milliseconds, if you need to give 10 seconds, give 10000
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Guy Hengel [angelIII / a3]Billing EngineerCommented:
>got it!!! the timeout is in milliseconds, if you need to give 10 seconds, give 10000
I have to disagree on that one, actually.
0
sjamal_hsjAuthor Commented:
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.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> 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..
0
sjamal_hsjAuthor Commented:
>>>> 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.
0
sjamal_hsjAuthor Commented:
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(Config.ConnectionString, "Transactions_CreateProductionTransaction");
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> If it is not closing connections properly, how would I be able to easily check this?

You can identify those queries blocking other queries in Profiler in an indirect manner.

>> Would I not be able to connect to the database from other applications / queries?

You would be able to connect to the database, but the issue being that tables alone would be locked out since that is bound in another transaction.

>> Would my current connections be over 50, etc.?

By default SQL Server allows a max of 32767 connections limited by your Hardware resources and hence you need not worry about that..
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.