none
The semaphore time-out period has expired

    Question

  • Hi,

    We have a job that does use linked server to copy some data from a MS SQL 2000 database and insert it into a database which runs in MS SQL Server 2005.This job is configured in the 2005 server.

    Offlate we started encountering the error

    OLE DB provider "SQLNCLI" for linked server "RSSDR" returned message "Communication link failure". [SQLSTATE 01000]

    Error Number::121 Error Message::TCP Provider: The semaphore time-out period has expired.

    Initially thought its a network issue but network team have confirmed there were no network issue at the specified time.Is it something related to network adapters,card or some other firware.

    Any help would be highly appreciated.

    Best Regards,
    Zainu

     

     

    Wednesday, September 01, 2010 7:04 PM

Answers

  • Hi,

     

    Based on your description and the error message, this error message comes from TCP Provider, we generally think this semaphore time-out error is caused by intermittent network failure, but since your network team had confirmed this, I supposed that may be the following reasons:

     

    1.      This semaphore time-out error means your server didn’t receive the response from SQL Server 2000 in time, this might be the both server have high load at the problem time.

    2.      The job you defined is copy the data from SQL Server 2000 to  SQL Server 2005, when the job executed it will scan the full table, meanwhile if there are others job or query to scan the same table, it might be blocked by these query.

    To solve these issues, here are some suggestions for you:

     

    1.      Reduce the load on the SQL Server 2005 server, you could end some unnecessary program at the current time.

    2.      Also you could optimize this query. You could make it run faster and lock less resources by using clustered index and nonclustered index for your tables, therefore reduce the possibility of being blocked.

    3.      Also try to reboot the server and execute the job manually.

    Please feel free to ask any questions.

     

    Thanks,

    Weilin Qiao

    Friday, September 03, 2010 8:02 AM

All replies

  • Semaphores are nothing but a piece of code on which only one thread is allowed to take lock at a time .other threads have to wait.

    The win32 error maps to ERROR_SEM_TIMEOUT.The API that returns this error is tcpRecv.This might also occur when your drive loses communication with the
    rest of your system.But mostly its a network related issue.I still feel that there is some issue with the network or the network adapters .please have a look at this KB :http://support.microsoft.com/kb/325487

    HTH


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Thursday, September 02, 2010 5:50 AM
  • Hi,

     

    Based on your description and the error message, this error message comes from TCP Provider, we generally think this semaphore time-out error is caused by intermittent network failure, but since your network team had confirmed this, I supposed that may be the following reasons:

     

    1.      This semaphore time-out error means your server didn’t receive the response from SQL Server 2000 in time, this might be the both server have high load at the problem time.

    2.      The job you defined is copy the data from SQL Server 2000 to  SQL Server 2005, when the job executed it will scan the full table, meanwhile if there are others job or query to scan the same table, it might be blocked by these query.

    To solve these issues, here are some suggestions for you:

     

    1.      Reduce the load on the SQL Server 2005 server, you could end some unnecessary program at the current time.

    2.      Also you could optimize this query. You could make it run faster and lock less resources by using clustered index and nonclustered index for your tables, therefore reduce the possibility of being blocked.

    3.      Also try to reboot the server and execute the job manually.

    Please feel free to ask any questions.

     

    Thanks,

    Weilin Qiao

    Friday, September 03, 2010 8:02 AM
  • Hi,

    Thanks Welin and Abhay.We are still trying to find if high CPU utilization is the cause of this issue.I would let you know if I get any updates from the server team.

    Regards,

    Zainu

    Friday, September 03, 2010 2:11 PM
  • Thank you all.There were some network related data packet loss issue which was causing this job to fail.

    Really appreciate the support that you all have provided.

    Regards,

    Zainu

    Monday, September 13, 2010 8:45 PM