0

sys.sp_reset_connection runs for up to 5 minutes on SQL Server 2019 CU13 [SQL1] .

At the same time other SQL Servers [SQL100..SQL200] that have linked server to [SQL1] are timing out:

TCP Provider: An existing connection was forcibly closed by the remote host.
OLE DB provider "SQLNCLI11" for linked server "SQL1" returned message "Client unable to establish connection"

What is causing the connectivity issue? Why is sys.sp_reset_connection running for so long and could it run faster?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
oleg
  • 1
  • 1
    How are you determining that `sp_reset_connection` takes 5 minutes? One of the tasks it does is rollback uncommitted transactions so that could take some time depending on the amount of data modifications. – Dan Guzman Nov 14 '21 at 17:28
  • `sp_reset_connection` isn't a real stored procedure. It's just a way for SQL Server to reset the connection settings, and can be signaled by the client within a TDS packet, commonly used in drivers that use connection pooling. How are you determining that `sp_reset_connection` is the issue? What else is going on on the server at the same time? Can you run `DBCC OPENTRAN` and `EXEC sp_who2` while this is happening? – Charlieface Nov 14 '21 at 22:32
  • I see in sp_whoisactive it's duration is 5 minutes – oleg Nov 15 '21 at 17:22
  • there are hundred of sp_reset_connection procs that run on a server at any given time. I sp_whoisactive, shows zero open_tran_count and hundred of application SQL Servers that access SQL1 and run the sp_reset_connection – oleg Nov 15 '21 at 17:24

0 Answers0