Solution for SQL database timeout expiration problem

If the SQL database is more and more, sometimes encounter a reading timeout, deadlock, such as a lot of problems, according to experience, the data structure design is unreasonable, often use views and other reasons, how to solve it? Read the article below

1. Timeout due to database design problems resulting in new SQL database data

Symptoms:

Microsoft OLE DB Provider for SQL server error ' 80040e31 ' ([ODBC SQL Server Driver] timeout expired);

The server looks at the CPU, memory occupancy rate is very low;

The event log prompts: The automatic growth of the file ' *********** ' in the database ' ********* ' has been canceled or timed out after 453 milliseconds. Use ALTER DATABASE to set a smaller filegrowth or to set a new size.

Reason:

When the database is set up, [file growth] grows in percent, and when the database file is large (above 1G), the new operation will report a timeout, at which point the CPU, memory occupancy rate is very very low.

Workaround:

Set the above file growth here to a lower percentage or specify how many megabytes to increase directly.

2.SQL Server Database Timeout setting

Modify the client's connection timeout setting. By default, the timeout setting for registering another SQL Server through Enterprise Manager is 4 seconds, and the Query Analyzer is 15 seconds.

Settings in Enterprise Manager:

A, in Enterprise Manager, select "Tools" on the menu, then select "Options";

B, in the SQL Server Enterprise Manager Properties window that pops up, click the Advanced tab;

C, enter a larger number, such as 30, in the box to the right of logon timeout (seconds) under Connection settings.

Settings in Query Analyzer:

Click "Tools"-> "Options"-> "Connect"; Set the logon timeout to a larger number, and the connection timeout to 0.

3. Timeout when querying statements

Reason Analysis:

Query timeout in general, first of all, from the structure of SQL statements and data tables to find the reason, the optimization of SQL statements and queries for the database Jianjian index is the most common method.

In addition, the query timeout setting for the database is typically maintained by SQL Server itself (before you modify query wait configuration) and will time out only if your actual query is more than 25 times times the estimated query time.

There are two possible reasons for exceeding the estimated value:

First, the estimated time is not accurate;

The second is that SQL statements involve a large amount of memory-consuming queries (such as sorting and hashing), and there is not enough memory to queue up for resources.

Solution:

A. Optimize the statement to create an appropriate index;

B. To solve the first problem, update the index distribution statistics to query the table to ensure the correctness of the estimated time, update STATISTICS table name;

C. Increase memory

If you want to set the query timeout manually, you can 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

Fault:

We also encounter similar error messages in the application, such as:

Microsoft OLE DB Provider for ODBC Drivers error ' 80004005 '. [Microsoft] The [ODBC SQL Server Driver] timeout has expired.

Workaround:

A. If you encounter a connection timeout error, we can modify the timeout setting for the Connection object in the program, and then open the connection. For example:

　　

B. If you encounter a query timeout error, we can modify the Recordset object time-out setting 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

' Same in seconds, if set to 0 means unrestricted

Rs. Open Cmd1, CN

Rs. MoveFirst

. . .

In addition, some hardware and network-related reasons may also cause SQL database connection timeouts.