Analysis and Solution to problems that cause SQL database timeout

1. Due to database design problems, the SQL database times out when new data is added.

Symptoms:
Microsoft ole db Provider for SQL Server Error '80040e31' ([odbc SQL Server Driver] timeout expired );
CPU and memory usage is low on the server;
The Event Log prompts: * files in the database '*' are automatically increased after 453 milliseconds. Use alter database to set a smaller FILEGROWTH or a new size.
Cause:
When the database is set, [file growth] increases by percentage. When the database file is large (more than 1 GB), a timeout is reported for the new operation, at this time, the CPU and memory usage are very low.
Solution:
Set the above file growth here to a lower percentage or directly specify how many megabytes are added.

2. SQL Server database timeout settings

Modify the connection timeout settings of the client. By default, the timeout setting for registering another SQL Server through the Enterprise Manager is 4 seconds, and the query analyzer is 15 seconds.

Settings in Enterprise Manager:

A. in Enterprise Manager, select "Tools" from the menu and then "options ";
B. In the displayed "SQL Server Enterprise Manager properties" window, click the "advanced" tab;
C. Enter a large number, such as 30, in the "Login timeout (seconds)" box on the right under "connection settings.

Query settings in Analyzer:

Click "Tools"-> "options"-> "connection"; Set logon timeout to a large number, and set connection timeout to 0.

3. query statement timeout

Cause analysis:

Query timeout generally involves finding the cause from the structure of SQL statements and data tables. Optimizing SQL statements and creating indexes for database query fields is the most common method.
In addition, the database query timeout settings are generally maintained by sqlserver (before you modify the query wait configuration), only when the actual query time exceeds the estimated query time by 25 times, will time out.
There are two possible reasons for exceeding the estimated value:
First, the estimated time is inaccurate;
Second, SQL statements involve queries that consume a large amount of memory (such as sorting and hash operations). If the memory is insufficient, you need to wait in queue for resources.

Solution:
A. Optimize the statement and create/use an appropriate index;
B. To solve the first problem, UPDATE the index distribution STATISTICS of the table to be queried to ensure the correctness of the estimated time. UPDATE the STATISTICS table name;
C. Add memory

To manually set query timeout, use the following statement:
Sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
Sp_configure 'query wait', 2147483647
GO
RECONFIGURE
GO

4. Application connection failed

Faults:
In the application, we will also encounter similar error messages, such:
Microsoft ole db Provider for ODBC Drivers error '20140901'. [Microsoft] [odbc SQL Server Driver] timeout has expired.

Solution:
A. If A Connection timeout error occurs, you can modify the timeout settings of the Connection object in the program and then open the Connection. For example:

<%
Set Conn = Server. CreateObject ("ADODB. Connection ")
DSNtest = "DRIVER = {SQL Server}; SERVER = ServerName; UID = USER; PWD = password; DATABASE = mydatabase"
Conn. Properties ("Connect Timeout") = 15' in seconds
Conn. open DSNtest
%>
B. If a query timeout error occurs, you can modify the timeout setting of the Recordset object in the program and then open the result set. For example:

Dim cn As New ADODB. Connection
Dim rs As ADODB. Recordset
...
Cmd1 = txtQuery. Text
Set rs = New ADODB. Recordset
Rs. Properties ("Command Time Out") = 300
'The unit is second. If it is set to 0, there is no limit.
Rs. Open cmd1, cn
Rs. MoveFirst
...

In addition, some hardware and network factors may also cause connection timeout of the SQL database.