server: drain operation failed and drained nodes not accepting sql connections, but is treated as active for other operations

This issue started on drt-ldr on September 11, 16:51, a _node-kill/sigkill/drain=true_ operation was started which does the following two things:
```
1. Drain Node
./cockroach node drain
2. Kill cockroach process with
kill -9
```

The operation failed in the middle of drain operation and could not run the operation cleanup step which resulted in server not accepting sql client connection [datadog logs for operation failure](https://us5.datadoghq.com/logs?query=status%3A%28warn%20OR%20error%20OR%20info%29%20cluster%3Adrt-ldr1%20-%40file%3A%22%28gostd%29%20net%2Fhttp%2Fserver.go%22%20%22drain%22%20&agg_m=count&agg_m_source=base&agg_t=count&cols=host%2Cservice&fromUser=true&messageDisplay=inline&refresh_mode=paused&storage=flex_tier&stream_sort=time%2Casc&viz=stream&from_ts=1726073100000&to_ts=1726101960000&live=false)

`Sep 11 16:51:55.331 drt-ldr1-0003 drt-cockroachdb drain failed: some sessions did not respond to cancellation within 1s
`
`Sep 11 17:27:13.272 drt-ldr1-0001 drt-cockroachdb drain failed: some sessions did not respond to cancellation within 1s
`
Two nodes were affected drt-ldr1-0001 and drt-ldr1-0003 which were not accepting sql clients. Nodes drt-ldr1-0002, drt-ldr1-0004, drt-ldr1-0005 are working and accepting client connections.

More details in slack thread [[link](https://cockroachlabs.slack.com/archives/C03JCUUSCD6/p1726117614230989)]

Jira issue: CRDB-42266