none
The semaphore timeout period has expired

    Question

  • Hello,

    For the past few days something weird is happening to our sql server box.  During running some procedures we get the following error message:

    Msg 121, Level 20, State 0, Line 0
    A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)


    It seems to happen when running large procedures or even small queries, the session where i run the query get disconnected but I can reconnect right away but it may happen again couple minutes later.

     I'm connecting to my Windows server 2003 using remote desktop and running procedures from the SQL Server installed on the Windows Server.

    Anybody has an answer?  Thanks.
    Thursday, October 09, 2008 6:45 PM

Answers

All replies

  • This is not a SQL specific issue, it comes from Winsock layer, Please check the following posts see if it applies.

     

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3271640&SiteID=1

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2342582&SiteID=1

     

    Or post a question in Winsock forum.

     

    Thanks.

    Friday, October 10, 2008 1:15 AM
    Moderator
  • Somehow we fixed this procedure by adding some indexes to a temp table, the CPU is now around 50% and the procedure have not timed out.  Looks like it's fixed.
    Friday, October 10, 2008 2:44 AM
  • Hi Axel_2005,

     

    Can you please explain how you resolved the problem. Bacause same problem i am facing here.

     

     

    Thanks in advance.

     

     

    Wednesday, October 06, 2010 2:03 PM
  • Hi Axel_2005,

     

    Can you please explain how you resolved the problem. Bacause same problem i am facing here.

     

     

    Thanks in advance.

     

     


    If there is any temp table involved in operation, try to add some indexes on columns being used, it should resolve the issue...

    Again, this is one scenario which can be different from yours...

    I hope this helps,


    -- Jignesh Patel
    Thursday, March 24, 2011 1:20 PM
  • In my case this error was not related to Network. Any way I got this error for the following for SQL Server 2005(SP2),64-bit in code: --==================================================== DECLARE @StartDate datetime SELECT @StartDate = convert(datetime,CONVERT(varchar(20),dateadd(d,-180,getdate()),101)) --==================================================== looks like the issue happens with dateadd with getdate together the following is work around it: --==================================================== DECLARE @StartDate datetime SET @StartDate = dateadd(d,-180,getdate()) SELECT @StartDate = convert(datetime,CONVERT(varchar(20),@StartDate,101)) --==================================================== OR just update to SQL 2005 SP3, furthermore SQL 2008 SP2 doesn't have this issue.
    • Proposed as answer by gr8_Scott Wednesday, May 25, 2011 6:48 PM
    Wednesday, May 25, 2011 6:44 PM