Sql-server – After many successive and successful queries SQL Server starts refusing connections

dockernode.jssql server

I have a GraphQL server ( Node v14.15.1 ) which uses typeorm to connect to a SQL Server 12.0.4100.1, also it runs inside a docker container, connection parameters are:

  let connection = await createConnection({
    type: "mssql",
    host: HOST,
    port: PORT,
    database: DB,
    username: USER,
    password: PASSWORD,
    // NOTE: Use typeorm-model-generator for Entities
    logging: false,
    synchronize: true,
    requestTimeout: 300000,
    connectionTimeout: 300000,
    entities: ["./models/mssql"],
    pool: {
      max: 1000, min: 1,
      idleTimeoutMillis: 3000,
      evictionRunIntervalMillis: 1500000,
    },
    options: {
      encrypt: false,
    }
  })

Problem:
After numerous sucessful queries the SQL Server starts refusing connections with the error:

ConnectionError: Failed to connect to adress: port - connect ECONNREFUSED adress: port\n
    at ConnectionError (/home/raa/code/wcc-gql/node_modules/mssql/node_modules/tedious/lib/errors.js: 13: 12)\n
    at Connection.socketError (/home/raa/code/wcc-gql/node_modules/mssql/node_modules/tedious/lib/connection.js: 1290: 56)\n
    at _connector.Connector.execute (/home/raa/code/wcc-gql/node_modules/mssql/node_modules/tedious/lib/connection.js: 1116: 21)\n
    at Socket.onError (/home/raa/code/wcc-gql/node_modules/mssql/node_modules/tedious/lib/connector.js: 140: 7)\n
    at Socket.emit (events.js: 198: 13)\n
    at emitErrorNT (internal/streams/destroy.js: 91: 8)\n
    at emitErrorAndCloseNT (internal/streams/destroy.js: 59: 3)\n
    at process._tickCallback (internal/process/next_tick.js: 63: 19)'

The problem happens when query a specific type which looking down the hierarchy has a lot of resolvers, also the amount of data varies.
The more data the more likely that the problem will occur.
What also interesting is that it is more likely that the problem will occur if the program is running inside a container or as PM2 service.

I've tested the same data where connections are refused inside a container and not refused in VS Code debugger or when run from terminal.
I tried fiddling with the pool options which gave modest results.
I've also checked that I do not exceed the amount of connections allowed by the DB.
I've checked the logs on the SQL Server, there are no issues or errors in them at the time I make requests. I've just now added support for tracking log attempts on the SQL Server and it now registers some 776 successful logins from this application in period 15:03:46.01 – 15:04:48.07 and after that I assume the I get the errors.

EDIT:

I've tried changing the Network Packet Size option in Sql Server options and packetSize connection options of typeorm -> mssql -> tedious which didn't work but revealed some new details. Since I had to reset the server apply new Sql Options, immediately after the reset the queries successfully finished.

So I tried tracking the resources and saw that the server ramps up to 100% processor capacity pretty quickly and after that has problems opening new connections and if I allow a too large max in pool options (in my case 500) of connections it start refusing connections.

When I bring down the max property, however, a new type of error arises which manifests as connection timed out from unknown reason this time stemming from tarn.js.

What I think is happening is that the tarm.js(underlying connection pool) has a default timeout for createTimeoutMillis/ adding new connections to pool, and this option is not revealed through the typeorm API and while it waits on the server to unfreeze and allow a new connection to be allotted it times out.

Best Answer

What node.js version are you on?

Apparently node version: v.12.6.0 required me to use packetSize: 8192 to avoid the Connection lost - read ECONNRESET error

If that is no the problem try to change the Network Packet size in the SSMS Properties

enter image description here

If you said that the connections mostly works but fails for different data is because of the size of the stream of data.