Some tips on Database Connection

In the database connection section, an error is always reported when you create a new connection. The timeout time is reached. The timeout time has reached, but the connection has not been obtained from the pool. This may occur because all pool connections are in use and the maximum pool size is reached.

In the database connection section, an error is always reported when you create a new connection. The timeout time is reached. The timeout time has reached, but the connection has not been obtained from the pool. This may occur because all pool connections are in use and the maximum pool size is reached.

In the database connection section, the error message "error cause: the timeout time has reached. The timeout time has reached, but the connection has not been obtained from the pool. This may occur because all pool connections are in use and the maximum pool size is reached ." So I want to check the active connection of the database to see which program occupies too many database connections.

We can use sp_who (or sp_who2) to display all active connections.

USE master;GOEXEC sp_who2;GO

The query result is shown in table 1. For readability, some columns and rows are deleted from the results in table 1. However, the deleted content does not affect what we want to talk about.

Table 1 sp_who2 query results

From table 1, we can see that there are many process statuses (seeStatusColumn) displays sleeping and the command type currently being executed (seeCommand) Displays the awating command.

We have doubts about why there are so many connection processes. First, we need to clarify the concept that SQL Server will not establish any connection pool, and ADO. Net will. When we open a database connection in the C # code, if it is the first connection, SqlClient will open a connection to SQL Server. When we close the connection from the program, SqlClient does not actually close the connection to the database, but is delayed for a period of time before closing. The default value is 60 seconds. If you do not reconnect within the specified delay, SqlClient will actually close the connection. But if you reconnect, SqlClient will reuse the connection. In this way, we can use the same connection property.

However, there are several possible cases that may cause failure, that is, the connection cannot be reused (or it is not closed at the specified time ). For example, when you useExecuteReaderWhen not all rows and result sets are retrieved successfully, and reader is not closed on the connection, this problem may occur.

The common method to troubleshoot database connection problems is to run SQL Server Profiler (using the default template ). If you see an Audit Login event, it indicates that a new connection has been established. If you see event RPC: Completed with the text "sp_reset_connection", this indicates that a connection in the connection pool is reused. If you haven't seen any sp_reset_connection, it means you have encountered a problem. SQL Server Profiler1.

SQL Server Profiler

Additional knowledge: differences between sp_who and sp_who2

Sp_who is officially supported and can be found in documents. Sp_who2 is not officially supported and cannot be found, but it is usually used. They return the same information: Provide information about the current user, session, and process in the Microsoft SQL Server database engine instance. However, sp_who2 adds additional columns not provided by sp_who. In addition, the information in sp_who2 is more compact and more suitable for text display. Kalman writes: It adds additional spid columns to increase the readability of the results.

Postscript:

"The timeout has been reached. The timeout time has reached, but the connection has not been obtained from the pool. This may occur because all pool connections are in use and the maximum pool size is reached. "This problem may be caused by the failure of a successful query during the query. For example, when executing a stored procedure, you must provide three parameters for the stored procedure, but you only provide two parameters, which leads to query errors. When an error occurs, the original database connection cannot be released or reused, which leads to ADO. net to create a new database connection, resulting in the thread pool to reach the pool itself can accommodate the specified number of connections, eventually resulting in new connections cannot continue to be created in the thread pool.

Reference: how to use SQL Server Profiler

SQL Server 2005 books online

Sp_who, sp_who2 and sp_who3

SLEEPING/awaiting command question