SQL Server Performance Forum – Threads Archive
Time out Expired..command time out
"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." I am getting the above error when i run the web application using ASP.NET. When i debug the application, i found that there is problem with the query timeout. I have increased the commandtimeout to 20 minutes instead of default(30 seconds) and was able to run the application successfully. Is this the correct solution or can anyone suggest if you have any alternative solution? Here is a bit of code of my Data Access Layer class. ***********************************************************************************‘Get the connection from Web.config
conObject.ConnectionString = Configuration.ConfigurationSettings.AppSettings (strConnString)
conObject.Open() dapObject = New SqlDataAdapter(strSpName, conObject)
cmdObject.CommandType = CommandType.StoredProcedure
cmdObject.Connection = conObject
cmdObject.CommandTimeout = 20 — this has been set to avoid query time out
cmdObject.CommandText = strSpName
*********************************************************************************** Appreciate your help. Best Regards,
Ramesh
run the very same stored procedure from query analyser and see how long it takes. I suspect the problem is not in your connectivity but in the performance of the procedure.
I am inserting 1 million records into a table as below Insert into tableA
select A1,A2,A3 from tableB If i don’t use the commandtimeout then the above query will timeout and if i set the commandtimeout to more than 30 secs then the query will run fine.. Can you suggest on the above explanation? Ramesh
what indices are on tableA, if any ?
you have actually decreased the timeout from 30 seconds to 20 seconds… 20 minutes would be 1200 seconds and so cmdObject.CommandTimeout = 1200 would be needed It is common to have to increase the commandtimeout for long running sql batches, however don’t just increase it for everything as it can cause problems sometimes when there a communication issues and the client is left waiting for 20 minutes Cheers
Twan
There are no indexes on the table A. I realised after posting the code.Twan, you are correct it is 1200 seconds. Any how i am setting the session timeout as 20 minutes for the Web users. Thanks
Ramesh
I would advise against setting any timeout to 20 mins. You could have a rogue code that can set exclusive locks which is just gonna hold onto the lock for the duration until it timeouts. I would set it to maybe 30 secs more than the slowest time it took to run the stored proc just to give it some cushion.
]]>