Have you ever seen a situation where SQL Server mirroring is impacted because of external factors? One of my customers reported several of the following errors in the SQL Server ERRORLOG SQL SERVER – Where is ERRORLOG? Various Ways to Find ERRORLOG Location. Let us learn about how to fix database mirroring error.
2016-11-10 07:05:27.90 Server Error: 1474, Severity: 16, State: 1.
2016-11-10 07:05:27.90 Server Database mirroring connection error 4 ’64(The specified network name is no longer available.)’ for ‘TCP://SQL1.sqlauthority.com:5022’.
2016-11-10 07:05:29.37 spid27s Error: 1474, Severity: 16, State: 1.
2016-11-10 07:05:29.37 spid27s Database mirroring connection error 4 ‘An error occurred while receiving data: ‘121(The semaphore timeout period has expired.)’.’ for ‘TCP://SQL2.sqlauthority.com:5022’.
As we can see above 1474 is a generic error. That can have many different OS errors. In above errors, it has come due to
- OS Error 64 = The specified network name is no longer available.
- OS Error 121 =The semaphore timeout period has expired.
Sometimes we can see error number, but not the message. Here is what you can do: I use net helpmsg command and pass the error number.
As a DBA, we need to look at more data to see and check if we can figure out what’s going on with the machine. In this customer’s scenario.
I reviewed Upon reviewing the System event log many networking warnings are being reported:
11/10/2016 07:05:27 AM Warning 27 e1rexpress HP Ethernet 1Gb 2-port 361T Adapter #3 Network link is disconnected.
11/10/2016 07:03:34 AM Warning 461 CPQTeamMP Team ID: 0 Aggregation ID: 1 Team Member ID: 0 PROBLEM: 802.3ad link aggregation (LACP) has failed. ACTION: Ensure all ports are connected to LACP-aware devices.
11/10/2016 07:03:10 AM Warning 27 e1rexpress HP Ethernet 1Gb 2-port 361T Adapter #3 Network link is disconnected.
11/10/2016 07:03:11 AM Warning 27 e1rexpress HP Ethernet 1Gb 2-port 361T Adapter #2 Network link is disconnected.
Above are at the same time when SQL is reporting errors. Error in the system event logs is indicating that there is something going on with the network cards which needs to be addressed. I informed my client that I am not familiar with this error but I am sure your hardware folks would be able to tell us what this is and how to fix it, if it is a problem.
When we contacted the hardware team for this customer they informed that NIC card drivers are very old and not compatible. As soon as NIC card drivers were upgraded, issues were resolved. Let me know if you have faced any similar error about – database mirroring error.
Reference: Pinal Dave (https://blog.sqlauthority.com)