Solutions to SQL database timeout and expiration Problems

If there are more and more SQL databases, there may be a lot of problems such as read timeout and deadlocks. Based on experience, the data structure design is unreasonable and views are often used. How can we solve these problems? See Article 1. Symptoms of timeout when SQL database adds data due to database design problems: MicrosoftOLEDBProviderforSQLServ

If there are more and more SQL databases, there may be a lot of problems such as read timeout and deadlocks. Based on experience, the data structure design is unreasonable and views are often used. How can we solve these problems? See Article 1 below. Symptoms of timeout when SQL database adds data due to database design problems: Microsoft ole db Provider for SQL Serv

If there are more and more SQL databases, there may be a lot of problems such as read timeout and deadlocks. Based on experience, the data structure design is unreasonable and views are often used. How can we solve these problems? See the article below

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 select "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 and 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. 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:

　　

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.