Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2005
    Posts
    319

    Unanswered: Linked Server woes (Login timeout expired)

    We are getting intermittent login timeout errors from our jobs that run every minute (24 jobs).

    After some investigation I created an alias under SQL Server Configuration Manager to ensure my connections were using TCP/IP. The alias is used in my linked server instead of the private IP.

    If I run:

    Code:
    SELECT * FROM My_Alias.OurDB.dbo.OurTable D
    I get this message after 20 seconds (the default login timeout):

    OLE DB provider "SQLNCLI10" for linked server "My_Alias" returned message "Login timeout expired".
    OLE DB provider "SQLNCLI10" for linked server "My_Alias" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
    Msg 10048, Level 16, State 1, Line 0
    TCP Provider: Only one usage of each socket address (protocol/network address/port) is normally permitted.


    I am not sure if this is related to the fact that I did not delete the original linked server (I wanted to test to make sure this one works first). The original linked server works fine (albeit intermittently fine).

  2. #2
    Join Date
    Jun 2005
    Posts
    319
    Here is the definition of my test linkedserver:

    Code:
    /****** Object:  LinkedServer [My_Server]    Script Date: 03/22/2011 13:47:56 ******/
    EXEC master.dbo.sp_addlinkedserver @server = N'My_Server', @srvproduct=N'sql_server', @provider=N'SQLNCLI10', @datasrc=N'My_Alias'
     /* For security reasons the linked server remote logins password is changed with ######## */
    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'My_Server',@useself=N'False',@locallogin=NULL,@rmtuser=N'My_Login',@rmtpassword='########'
    
    GO
    
    EXEC master.dbo.sp_serveroption @server=N'My_Server', @optname=N'collation compatible', @optvalue=N'true'
    GO
    
    EXEC master.dbo.sp_serveroption @server=N'My_Server', @optname=N'data access', @optvalue=N'true'
    GO
    
    EXEC master.dbo.sp_serveroption @server=N'My_Server', @optname=N'dist', @optvalue=N'false'
    GO
    
    EXEC master.dbo.sp_serveroption @server=N'My_Server', @optname=N'pub', @optvalue=N'false'
    GO
    
    EXEC master.dbo.sp_serveroption @server=N'My_Server', @optname=N'rpc', @optvalue=N'true'
    GO
    
    EXEC master.dbo.sp_serveroption @server=N'My_Server', @optname=N'rpc out', @optvalue=N'true'
    GO
    
    EXEC master.dbo.sp_serveroption @server=N'My_Server', @optname=N'sub', @optvalue=N'false'
    GO
    
    EXEC master.dbo.sp_serveroption @server=N'My_Server', @optname=N'connect timeout', @optvalue=N'0'
    GO
    
    EXEC master.dbo.sp_serveroption @server=N'My_Server', @optname=N'collation name', @optvalue=null
    GO
    
    EXEC master.dbo.sp_serveroption @server=N'My_Server', @optname=N'lazy schema validation', @optvalue=N'false'
    GO
    
    EXEC master.dbo.sp_serveroption @server=N'My_Server', @optname=N'query timeout', @optvalue=N'0'
    GO
    
    EXEC master.dbo.sp_serveroption @server=N'My_Server', @optname=N'use remote collation', @optvalue=N'true'
    GO
    
    EXEC master.dbo.sp_serveroption @server=N'My_Server', @optname=N'remote proc transaction promotion', @optvalue=N'true'
    GO

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    It actually may not have anything to do with the Linked Server itself, but a matter of how many calls are being made to the remote server per second. Have a look at this article. Be sure to document well, as the fixes/workarounds involve registry hacks that you might not remember when you move to newer hardware.

  4. #4
    Join Date
    Jun 2005
    Posts
    319
    Thanks for the link. We also have replication set up to publish to about 40 machines so that might be eating up socket connections. Never had this issue before, but also managed such "large" publications.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •