Once SQL Server 10054 troubleshooting

Problem

A new subscription node is added to a library, and some applications need to be sliced into a new subscription repository to distribute the load. When the app switches, there is an app that starts in less than 30 seconds each time, and always reports a time-out error, without any record in error log:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated.

However, switching back to the original subscription library does not time out.

Analysis

1. From the first troubleshooting connection timeout, look for operations to see the connection configuration, connection timeout set to 90 seconds. Exclude it.

2. Troubleshooting the statement time-out, find the development, said to find the historical version of the source, there is no way to view the Sqlcommand.commandtimeout set value, can not continue to troubleshoot.

3.1, 2 without fruit, use XE to capture the error message, the default System_health session is:

Network error code 0x2746 occurred while establishing a connection; The connection have been closed. This may has been caused by client or server login timeout expiration.

Time spent during Login:total 2407 MS, enqueued 0 MS, Network writes 0 MS, network reads 2407 MS, establishing SSL 0 MS, negotiating SSPI 0 MS, validating login 0 ms,

including user-defined login processing 0 ms.

Error code 0x2746 is 10054 and is viewed with net helpmsg, with the result: Connection forcibly closed by remote host.

OK, so far, I'm sure the problem is on the application side. So looking for development, operation and maintenance to discuss together to see if they can detail the application side, the conclusion is: no way.

4. Is there really no way out?

Now that the timeout message can be returned to the application, I can grab the packet and see what's going on. Then use Network Monitor to capture:

Judging from the information captured, the selected line in the figure is the timeout. Its previous record tds:sqlbatch from the application side to the server, after a while, the application side has timed out.

This is obvious, the application side sent a batch to the database execution, wait a moment it said: I timed out. The problem is here, what statement it sends, how long it has been executed, and what caused the timeout.

5. Track with Profiler:

This batch executes approximately 20s, and also gets a specific statement.

6. But why does switching to the original subscription library not time out, and the connection to the new subscription library will time out?

The 5. Captured statements are executed on two instances, and the new subscription library returns data for 8w+ rows, while the old only returns 6 rows of data and is fast.

Further analysis of the statement, the original old subscription library, there is a non-subscription table, the table has a large number of data for filtering, but the new subscription library only this table, no data.

This table data is then imported into the corresponding table in the new subscription library, and the results of both queries are consistent and the application does not time out. This infers that the sqlcommand.commandtimeout in the application code is absolutely less than 20s.

Summary:

1.10054 error A very famous error, for a variety of reasons, more difficult to troubleshoot a problem.

2. If can this in 2. One step can confirm the Sqlcommand.commandtimeout set value, and do the corresponding debugging and troubleshooting, will save a lot of things. The importance of source management Ah!

3. Do things carefully, after the new subscription, I only compare whether the object is the same, if also check the table row number is the same, it will not happen.