Timeout expired error | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Timeout expired error

I am trying to troubleshoot a timeout issue with SQL 2k and .NET. we are tring to insert bulk data from collection to DB. While inserting we call another procedure to transerf the same data to some other table> Here is the error message
Erro On Save .Net SqlClient Data Provider Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
The timeout period elapsed prior to completion of the operation or the server is not responding. It will be a great help if any one can comes out with some solution Thanx in advance prad
Not sure if this will help, but it could possibly be the value set for remote query time out. Check out Remote query time out option in BOL…by default queries time out after 10 minutes… Hope this helps!
Ben
Are you calling this from a web page? If so, you can set the query timeout on the page: server.scripttimeout = 420 (increase this number as needed) This increases the amount of time the page will wait for a result from the DB. When you say that you run another procedure while the main one is running, is this fired by a trigger on the main table? Petew
The stored procedure may be poorly designed. Analyze the execution plan ofthe stored procedure to look for joins and statements that you can further optimize. Also check the size of tempdb during this procedure execution. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
While transfering the same data to other tables try to use NOLOCK in select statement. I think you are blocking the table where you are pulling the data from to other tables. Hope this helps you. Srinivas
Hi All: Thanks you all for your great answers, we did manage to over come this issue by increasing the command time out to 60000. I used to belive that by setting Command time out as zero will make it work indefinitly, anyway i am going to try 600000 next time. Thanks one again prad
The timeout increase may help you upto some extent and referring to other suggestions like using NOLOCK hint, and re-compiling the referred stored procedures will have optimum performance gain. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Any ideas to fix the error when I use application blocks.
quote:Originally posted by bluesky Any ideas to fix the error when I use application blocks.

In the SQLHelper class set the SQLCommand.CommandTimeout property to a higher number the server.scripttimeout applies to the page and how long it takes to display while the commandTimeout property applies to the actual query for sp the default is 30 seconds. Refer to this article for info on DAAPhttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp Raulie
Hewlett-Packard

]]>