Causes and Solutions for SQL database connection timeout

1. Timeout when SQL database adds data due to database design issues

Symptoms:

A,
Microsoft ole db Provider for SQL Server error '80040e31'
[Odbc SQL Server Driver] the timeout has expired)

B. Low CPU and memory usage on servers;

C. 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 statements to create/use suitable indexes;

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. Increase the 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 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:

The code is as follows:	Copy code
<% 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, 0 indicates no limit Conn. open DSNtest %>

B. If a query timeout error occurs, you can modify the timeout settings of The Recordset object in the program and then open the result set. For example:

The code is as follows:	Copy code
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

...