Fix SQL Server Error 121 – The Semaphore Timeout Period Has Expired
Yesterday one of my teammate got a weird error while executing a T-SQL script. We are talking about SQL Server error 121 the semaphore timeout period has expired. Error details are showing below:
A transport-level error has occured when receiving results from the server. (provider: TCP Provider, error: 0 – The semaphore timeout period has expired.) (Microsoft SQL Server, Error:121)
This was an intermittent issue and when we were executing the script from other server it was not throwing any issue. I did some research on this and found out that this error came because of unstable network connectivity.
SQL Server Error 121 The Semaphore Timeout Period Has Expired – Root Cause
There are various parameters that can affect network connectivity like network adaptors, packet drop, configured packet size etc. So i would suggest first you should connect to your network team and ask them to look into this issue. If network team has not found anything then you need to check application level network configurations at your level.
You should ask some questions to your network resource like whether any network packet drop being observed or not, or is there any NIC card issue etc. Let’s assume you have already reached out to network team and they haven’t found anything. I would suggest you to look into below options at your level to make sure everything is in place and not misconfigured.
- Check TCP\IP settings
- Ensure you have appropriate network packet size configured in SQL Server
- Make sure you have properly configured TCP Chimney Offload
- Validate you don’t have Network Interface Card (NIC) driver issue, you can get it checked with your network team
Solution
I have already described high level areas where you need to look in in above section. Let’s discuss them further here.
Check TCP\IP settings
You should open SQL Server configuration manager and check whether TCP configuration is properly done or not. Make sure to review all settings in SQL Server Network configuration settings. You should also read below article about this settings which causes a network error.
Review Network Packet Size
You should also review network packet size configuration in SQL Server. This could be one of the possible reason for this issue. Ideally we should not change the default value of this setting and keep it as it is until you have a specific requirement to change network packet size. Read below article to understand and learn network packet size in SQL Server and also step by step process about how to change the network packet size to your desired value.
TCP Chimney Offload
This settings is very important if we have network related connectivity issue. TCP Chimney Offload is a networking technology that helps transfer the workload from the CPU to a network adapter during network data transfer. It can be enabled or disabled in the following two locations:
- The operating system
- The advanced properties page of the network adapter
TCP Chimney Offload will work only if it is enabled in both locations. By default, TCP Chimney Offload is disabled in both these locations. However, OEM installations may enable TCP Chimney Offload in the operating system, in the network adapter, or in both the operating system and the network adapter.
Validate Network related Driver Issues
You should speak to your network resource and Windows OS platform resource and validate all drivers at OS layer and network layer should be up to date and they should not have any issue. Make sure to update the drivers if you found any issue any of these layer.
I hope you can fix your issue by following above steps mentioned in this article. Comment us how you have fixed this issue, the semaphore timeout period has expired.) (Microsoft SQL Server, Error:121)
Related Articles
- SQL Server Error 40: A network-related or instance-specific error occurred while establishing a connection to SQL Server.
- Fix Error 1418: The server network address cannot be reached or does not exist.
- Fix: SQL Network Interfaces Error 28 – Server doesn’t support requested protocol
- SQL Server Native Client Error 50000: A network error occurred while attempting to read from the file
- Fix SQL Error 18456: failed to open the explicitly specified database - September 18, 2021
- Fix Always ON Connection Timeout Error 35206 in SQL Server - July 23, 2021
- How to Enable Preview Features in Azure Data Studio - July 15, 2021
