After a while, you may end up getting complaints or request dealing with timeout issues. I’m going to use this post to help collect some ways to increase the timeout seconds and prevent errors from happening.
*Note: Even though you increase the timeout seconds, timeouts might still occur but with a longer delay then before. This might make your customers even more upset because of the longer wait for the error to display.
- In code, increase the sqlCommand timeout. Ex:
- In code, increase the connection’s string timeout by appending “Connection Timeout=” to it. Ex:
Data Source=mydatabase;Initial Catalog=Match;Persist Security Info=True;User ID=User;Password=password;Connection Timeout=120
- On SQL-Server 2005, In management studio’s Tools > Option > Designers Increase the “Transaction time-out after:” even if “Override connection string time-out value for table designer updates” checked/unchecked.
- Make (non-dynamic) stored procedures instead of using inline sql statements within the code. This will also allow for easy fixes instead of having to recompile and deploy for future changes.
Dim myCommand As New SqlCommand("[dbo].[spSetUserPreferences]", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
'change default time out setting
myCommand.CommandTimeout = 120
If you have any other suggestions, please contact me so I can add them to the list.