SQL Server 18056 Error

From: http://blog.csdn.net/yangzhawen/article/details/8209167

18056
 

The client cannot re-use the session with spid % d. The session has been reset for the connection pool. The failure ID is % d.

This error may be caused by a previous operation failure.

Check the error log to find the failed operation that occurred before the error message is displayed.

2011-11-19 16:33:53. 91 spid5495 the client was unable to reuse a session with spid 5495, which had been
Reset for Connection pooling. The failure ID is 1. This error may have been caused by an earlier operation
Failing. Check the error logs for failed operations immediately before this error message.
2011-11-19 16:33:53. 93 spid2991 error: 18056, severity: 20, status: 1.

Case study:

When error 10856 is continuously reported in the errorlog file of sqlserver, the CPU usage is low at the same time. At this time, the SQL client logs in
The database query operation is normal; the number of IIS connections is soaring, and the website cannot operate the database (such as logon and basic query)

Analysis prerequisites:
This is a common problem. The official explanation does not have a clear answer. It means either patching or setting the IIS connection pool.
The premise is that the database has been installed with the latest patch, the IIS connection string is normal, and the user name and password are normal.

Analysis process:
For example, if the IIS connection pool is set to 1500 m and the IIS connection data is normally set to 1500, the size of the Connection Pool allocated to each session is 1 MB on average,
The default value of network data packets in the database is 4096;

If a request needs to return 20 mb of data at this time, the size of the data packet returned by this session from the database will exceed the size of the session
The size of the obtained connection pool. The data packet is 4096. More data packets are required for normal requests (1 Mbit/s response request ).
The session persistence time is longer than the average. Normally, these requests are not too problematic.
If the number of IIS requests reaches 3000 at the same time, the average size of the Connection Pool allocated to each session is 0.5 Mb. If 20 mb of data is returned,
The session time will be longer!
If the client requests 100 30 m data records at this time, when the database returns to IIS, IIS will find that the connection pool does not have enough memory space.
Allocate this session. At this time, the IIS connection pool size will not automatically increase with the increase of client requests or the IIS server does not have more physical memory. At this time, IIS will
Because there is not enough space for the connection pool to cache the corresponding session, but the client still keeps applying for it from IIS. This is the case!
IIS releases sessions that cannot be processed (or when the IIS process is down or IIS is automatically restarted). When the database receives a session request from the IIS end, It queries the data and returns it
In IIS session, find the spid of the corresponding request and find that the spid of the request does not exist, but the TCP connection of the database will not be discarded because the spid does not exist.
Data, the network card traffic will increase! At the same time, this error occurs in the database errorlog.

Solution:
0. First, check whether the database has a deadlock.
1. the most direct way is to increase the size of the IIS connection pool.
2. Find a large session request in the program and modify the code.
3. Restrict the maximum number of IIS processes and set the connection pool size based on daily running conditions (not recommended, mandatory)
4. Restrictions on SQL session at the Database End: SQL firewall or database restrictions on persistent connections (not recommended, mandatory, no way)