Have you ever encountered errors in SQL Server mirroring that were caused by external factors? In this article, we will discuss a common issue related to database mirroring and how to resolve it.
One of my customers recently reported several errors in the SQL Server ERRORLOG, such as:
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’.
These errors indicate a generic error with different OS error codes. In this case, the error numbers 64 and 121 correspond to the OS errors “The specified network name is no longer available” and “The semaphore timeout period has expired,” respectively.
When faced with such errors, it is important for a DBA to gather more information to understand the underlying issue. One way to do this is by using the “net helpmsg” command and passing the error number as a parameter.
In the case of my customer, upon reviewing the System event log, we found several networking warnings that were reported at the same time as the SQL Server errors:
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.
These warnings indicated that there were issues with the network cards, which needed to be addressed. Upon further investigation, we discovered that the NIC card drivers were outdated and incompatible. Once the NIC card drivers were upgraded, the database mirroring issues were resolved.
If you encounter similar errors with database mirroring, it is recommended to check the system event logs for any networking warnings or errors. Contact your hardware team to ensure that the network cards and drivers are up to date and compatible with your SQL Server environment.
Resolving external factors that impact SQL Server mirroring is crucial for maintaining a stable and reliable database environment. By addressing these issues promptly, you can ensure the smooth operation of your SQL Server mirroring setup.
Have you faced any similar errors with database mirroring? Share your experiences in the comments below!