Today we are going to learn about how to fix the error: Timeout expired. The timeout period elapsed prior to the completion of the operation.
Some errors when encountered take most of us for a spin. In this category, the error related to “Timeout” surely falls. If you are a web developer and receive the same there are a hundred combinations of why this can possibly happen. The web, results can sometimes lead us in completely opposite directions because we have not analyzed the root cause for this. I sincerely urge everyone when working with generic errors, look for specific symptoms and then use the trick of eliminating one after the other before a final solution can arrive.
This is one of the most common errors which you would hear from web developers. Here is a typical error raised shown on the website.
It’s a very clear message that the timeout duration specified for the operation is completed but actual work didn’t finish. The error can come because of two kinds of timeouts:
- Query Timeout: The default value for a web application or .NET application is generally 30 seconds. If any request is in progress and it couldn’t complete within the timeout period, we would again see an error.
- Connection Timeout: The default value of connection timeout is generally 15 seconds. Within this time if the connection can’t be made, we would see the error.
The next obvious question is – what should we do if we see such error?
Query timeout: The reason a command/query runs longer than expected is commonly due to blocking or the need for query/index tuning or both. A quick way to check for blocking to run sp_who2 while the query is running. The BlkBy column will show the SPID of the blocking connection if the query is blocked. For a slow-running query, you may want to check the execution plan to verify that the statement is touching the rows which are needed. For example, if your intent is to Select a single row but you see a scan operator, that is a strong indication that you need to perform index or query tuning or need to update statistics.
Here is the over-simplified repro of query timeout done via SQL Server Management Studio (SSMS). The default value of query timeout in SSMS in 0 (which is infinite) Tools > Options in the menu would open below the window where the value can be set if needed.
and we can see below after 10 seconds.
It is important to note the error number. It is not an SQL Server error but an error by the client. The query waited for 10 seconds because we caused blocking. If we don’t set the timeout value in SSMS, the query would run forever because the default timeout is zero.
Connection timeout: This I can’t reproduce by management studio query easily. On a slow network, you can easily reproduce it by reducing the connection timeout to a lower value. In SSMS. You need to click on Options on the login page as shown below for the timeout period.
And change the value in the Connection Properties tab.
On the same screen, we can also set the query timeout (shown as execution time-out)
If you are seeing the same errors in your web application, then you need to check a configuration file (normally known as the web. config file). The same parameters (query and connection timeout) can be set in the connection string of the application as well.
Reference: Pinal Dave (https://blog.sqlauthority.com)
