Delete query throws "semaphore timeout period has expired"

Question

Hi All,

I'm running a delete query as part of purge activity. The delete query has about 12+ foreign key constraints, and when we try to delete 1300 records as per our condition we get the below error.

The table has about only 15K records and the db is a Azure Sql database. There's no missing indexes, no fragmentations, no blockings or deadlocks and we have also increased the DTU's. Recently we tried to delete just one record which took like 5mins and got completed. We have similar type of tables which has a lot of foreign key tables, but didn't encountered such errors with other tables.

Any help would be highly appreciated.

Answer 1

The semaphore timeout period has expired is a network related error that occurs when the connection between the server and the client takes more time than expected. Please consider increasing the value of the connection timeout on the connection string.

Do you use a VPN to get connected to your organization? Maybe the issue happens only when you are connected or disconnected from the corporate VPN.

Please compare the network adapter settings in your computer with network adapters of other computers in your organization. A driver update may have changed settings.

The issue could also be related to a corporate firewall that may be blocking the traffic. Please try to connect from another network entirely. Make sure port TCP1433 is not blocked. If you are using Windows Firewall make sure you configure it to allow SQL Server access as explained here.

If you are using SSMS to run the purge activity, make a click on the "Options" button an specify a default database other than the master database. Maybe the login does not have permissions to access the master database. Again, please consider increasing the value of the connection timeout on the connection string.

Other possible cause is network latency in your network.

If you are deleting the rows from an application, please consider adding a retry logic as explained here. If that application uses Entity Framework you can consider make use of connection resiliency.

Answer 2

Hi Arunkumar Janakiraman •,

Welcome to Microsoft Q&A forum.

As I understand, you are getting Delete query throws "semaphore timeout period has expired" in Azure SQL Database.

You have mentioned that you have checked missing indexes, have increased DTUs etc.

Please refer to the below discussion over Stackoverflow post https://stackoverflow.com/questions/8602395/timeout-expired-the-timeout-period-elapsed-prior-to-completion-of-the-operation if this helps.

If not, I would recommend you to please file a support ticket for deeper investigation and in case if you don't have a support plan, do let us know here so that we can check on other options to unblock you. Thanks