Shulou(Shulou.com)05/31 Report--

This article mainly explains "what is the solution to MSSQL database timeout". The explanation in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what is the solution to MSSQL database timeout".

If there are more and more SQL databases, sometimes we will encounter a lot of problems, such as read timeout, locks, and so on. According to experience, there are reasons such as unreasonable design of data structure, frequent use of views, and so on.

1. Timed out when adding data to SQL database due to database design problems.

Symptoms:

A 、

Microsoft OLE DB Provider for SQL Server error '80040e31'

[ODBC SQL Server Driver] timeout has expired)

B, look at CPU on the server, memory occupancy rate is very low

C, the event log indicates that the automatic growth of files in the database'* 'has been cancelled or timed out after 453 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH or set a new size.

Reason:

When setting up the database, [file growth] increases by a percentage. When the database file is very large (more than 1G), new operations will time out. In fact, the CPU and memory occupancy rates are very low.

Solution:

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

2. SQL Server database timeout setting

Modify the connection timeout setting for the client. By default, the timeout setting for registering another SQL Server through Enterprise Manager is 4 seconds, while query Analyzer is 15 seconds.

Settings in Enterprise Manager:

A. in Enterprise Manager, select tools on the menu, and then select options.

In the pop-up "SQL Server Enterprise Manager Properties" window, click the "Advanced" tab

C. under connection Settings, enter a larger number, such as 30, in the box to the right of login timeout (seconds).

Settings in query Analyzer:

Click tools-> options-> Connect; set the login timeout to a large number and the connection timeout to 0.

3. The query statement timed out

Cause analysis:

Generally speaking, the reason for query timeout should be found from the structure of sql statements and data tables. Optimizing sql statements and indexing the query fields of the database are the most commonly used methods.

In addition, the database query timeout settings are generally maintained by sqlserver itself (before you modify the query wait configuration), only when your actual query time exceeds 25 times the estimated query time. There are two possible reasons for exceeding the estimated value:

First, the estimated time is not accurate.

Second, the sql statement involves a large amount of memory-consuming queries (such as sorting and hashing operations), which is caused by insufficient memory and the need to wait in line for resources.

Solution:

A. optimize the statement to create / use the appropriate index

B, the method 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 STATISTICS table name

C, increase memory.

If you want to manually set the query timeout, 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 will encounter similar error messages in the application, such as:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft] [ODBC SQL Server Driver] timeout has expired

Solution:

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.