One of my clients approach me that after patching of SQL instances, database mirroring stopped working. As usual, I asked them to share ERRORLOG file SQL SERVER – Where is ERRORLOG? Various Ways to Find ERRORLOG Location. In this blog post we will learn about how to fix error 26023 TCP Port is Already in Use.
Here are the interesting messages in ERRORLOG file
2016-11-03 22:11:04.32 spid24s The Service Broker protocol transport is disabled or not configured.
2016-11-03 22:11:04.33 spid18s Database mirroring has been enabled on this instance of SQL Server.
2016-11-03 22:11:04.32 spid24s Error: 26023, Severity: 16, State: 1.
2016-11-03 22:11:04.32 spid24s Server TCP provider failed to listen on [ ‘any’ 5022]. Tcp port is already in use.
2016-11-03 22:11:04.50 spid24s Error: 9692, Severity: 16, State: 1.
2016-11-03 22:11:04.50 spid24s The Database Mirroring protocol transport cannot listen on port 5022 because it is in use by another process.
I asked them if they have multiple instances of SQL and other are also using port 5022? They told that they have other instances, but they are not using mirroring. To be doubly sure, I suggested to run from command prompt.
netstat -aon | find /I "5022"
Here is the output.
As we can see above that port 5022 is already in use by PID 1452 (last column)
We found that PID 1452 was for another instance and it was having an endpoint created on the same port but they were not using Database mirroring. I have asked them to either remove the endpoint from the other instance or if they want to use mirroring, use a different port of an endpoint.
Once we deleted the endpoint from another instance of SQL Server and restarted the endpoints here, database mirroring came back to life again.
Reference: Pinal Dave (https://blog.sqlauthority.com)