I am calling a SQL Server stored procedure using ADO.NET, and I have to pass a very large Table-Valued parameter. I get this timeout exception very often:
System.Data.SqlClient.SqlException (0x80131904): Timeout expired.
The timeout period elapsed prior to completion of the operation or the server is not responding. --->
System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out at System.Data.SqlClient.SqlCommand.EndExecuteNonQueryAsync(IAsyncResult asyncResult)
I am calling ExecuteNonQueryAsync
, which according to the documentation is not affected by the CommandTimeout.
The question is: what timeout value should I increase to be able to pass that large TVP to the stored procedure without getting a timeout?
A solution that helped me recently with this situation was increasing the Connection Timeout (not to be mistaken with Command Timeout). This makes me think that sometimes when there is a lot of data being passed to a stored procedure via a table value parameter that it can sometimes take longer to initialize the connection (this occurred during peak usage but not when the system was under low load). A simple way to increase the Connection Timeout is via the query string.
Example of setting timeout to 120 seconds instead of the default 15:
"Data Source=localhost;Integrated Security=SSPI;Connection Timeout=120;"
User contributions licensed under CC BY-SA 3.0