none
[298] SQLServer Error: 258, TCP Provider: Timeout error [258]. [SQLSTATE 08001]

    Question

  • I've received this error twice over 2 days in the SQL Agent error log during off-hours from a server which has not had any changes to it in the last few months (once at 3:32AM and the next day at 3:46AM.  I've been able to tie it to log-shipping backup jobs (all of which run under the SQL Service domain account) which run every minute.  It seems to be only momentary, as the next attempt a minute later is successful.

     

    I get the same set of messages for each database for the same time which look like this:

     

    Date  07.03.2008 3:31:51 AM
    Log  SQL Agent (Current - 10.03.2008 6:15:00 PM)

    Message
    [298] SQLServer Error: 258, TCP Provider: Timeout error [258]. [SQLSTATE 08001]

     

    Date  07.03.2008 3:31:52 AM
    Log  SQL Agent (Current - 10.03.2008 6:15:00 PM)

    Message
    [165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]

    Date  07.03.2008 3:31:52 AM
    Log  SQL Agent (Current - 10.03.2008 6:15:00 PM)

    Message
    [298] SQLServer Error: 258, Unable to complete login process due to delay in login response [SQLSTATE 08001]

    Date  07.03.2008 3:31:52 AM
    Log  SQL Agent (Current - 10.03.2008 6:15:00 PM)

    Message
    [382] Logon to server '(local)' failed (SubSystemStepHistoryLogger)

     

    I can't find any documentation on these errors on the web or MSDN site.  Can I chalk this up to possibly "winking" connection to our domain controller?  We've never had these messages before, and our LAN department swears that there have been no upgrades to the network or domain controller servers recently.

    Tuesday, March 11, 2008 9:44 PM

Answers

  • Sunrise,

    I had almost a similar experience this week.We got a STELLENT content software which is using SQL 2K as backend.

     

    There were other databases hosted in this SQL box and one of my colleague restedted the SQL service (planned restart) and Stellent and some of the applications weren't connecting.

     

    When we restarted the server, everything works fine. We again restarted DB services to figure out the root cause. Stellent down after SQL service restart. Raised a ticket with Oracle who bought Stellent and wasn't helpful.

     

    Finally after 2 days we found out the problem.

    The TCP\IP wasn't listening for SQL Server when the service is restarted. But will listen when the machine is rebooted which we can't do much as there is one more instance and some other DB applications.

     

    So we disabled TCP and reenabled after service restart and everything worked like a charm.

    Now we know the immediate workaround for this issue if happen again. Smile

     

    Hope this would help you troubleshoot and resolve this issue.

    Please let us know if this helped you resolve your issue.

     

    thx,

    Jose Mathew

     

    If this post helped you resolve your issue, please mark 'answered'

    Friday, May 23, 2008 12:38 AM

All replies

  • It could be possible that the server is under heavier load during those times and so it is failing to respond within the timeout you provided.  Do you have scheduled jobs running around those times, or is your network/server load any higher between 3 and 4 am?

    Thanks,

    John
    Wednesday, March 12, 2008 6:52 PM
  • No, there is no unusual load on the servers at the times which showed connection failures.  In fact, the log-shipping jobs are the only ones running every minute for at least an hour before and after the failure.

     

    Wednesday, March 12, 2008 7:24 PM
  • With (local), it should go through the loopback device, which won't need to do dns lookup or address resolution, or any of the other usual suspects for these kinds of failures.  As you say, it may be the domain controller for NTLM authentication running slowly at that time or being down for some reason.  It is hard to tell, with this one, why it might be happening.  Do the server logs register anything about the connection attempts?  It seems like it should definitely have gotten the prelogin/login packets -- I wonder if there are log entries indicating something around those times?

    Thanks,

    John
    Wednesday, March 12, 2008 10:12 PM
  • No, the server logs don't indicate any failed attempts.  Only the messages indicated in my original post are shown.

     

    Interestingly, I received exactly the same set of messages again last night, but that was during a known reboot of our domain controllers by our LAN department in order to apply some WSUS patches.

     

    Thursday, March 13, 2008 5:48 PM
  • Just had log-shipping issues on our server this morning, and wanted to add that a burst of the [382] and [298] login failed messages appeared in our server Log File, same as indicated earlier in this thread.  This is the first time we've seen these event log entries.

     

    Our log shipping job runs every 5 minutes.  Last night at 2:00am we ran a re-indexing job which caused the log shipping job to fail with insufficient disk space error on our local log shipping share due to huge transaction log.  This first occurred at 3:35am .  At 6:30 am, with the log shipping still enabled and still failing, we received the burst of logon failures.  Everything else appeared normal on the server --no unusual load.  Also, as with the thread's earlier post, this server has been stable with log shipping running for over 6 mos.

     

    Here's a sample of our errors. Maybe this will shed some additional light.

     

    They come in sets of four.  I'm pasting them in chronological order, top to bottom.

     

    Date  3/21/2008 6:30:35 AM
    Log  SQL Agent (Current - 3/21/2008 6:32:00 AM)

    Message
    [298] SQLServer Error: 258, Shared Memory Provider: Timeout error [258]. [SQLSTATE 08001]

     

    Date  3/21/2008 6:30:35 AM
    Log  SQL Agent (Current - 3/21/2008 6:32:00 AM)

    Message
    [165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]

     

    Date  3/21/2008 6:30:35 AM
    Log  SQL Agent (Current - 3/21/2008 6:32:00 AM)

    Message
    [298] SQLServer Error: 258, Unable to complete login process due to delay in login response [SQLSTATE 08001]

     

    Date  3/21/2008 6:30:35 AM
    Log  SQL Agent (Current - 3/21/2008 6:32:00 AM)

    Message
    [382] Logon to server 'ELMSERVER8' failed (ConnAttemptCachableOp)

     

    Friday, March 21, 2008 3:43 PM
  • The error you are getting is typically seen when a user connects succesfully and recieves an ack from the server, but then sends login credentials and does not complete the login process within a prespecified timeout.  You are connecting over SM, so this is local and network issues can be ignored with the exception of the domain controller, who controls NTLM credential management and authentication.  If you are using Integrated Authentication, your domain controller could be a culprit in this scenario.  One general suggestion people offer for this issue is "lengthen the timeout", but I think that (at least in this case) this would not address your issue, as your job is running every 5 minutes.  You may not be able to affort a 2 minute login timeout, and that doesn't guarantee that you won't see these issues, just lowers the probability of load-based issues causing failure.  Is it possible to ping the domain controller on job failure to see if it is up and what its response time is like?

    Thanks,

    John

    Friday, March 21, 2008 7:41 PM
  • Hi,

     

    The ODBC error "Timeout expired" states that a query did not found to
    return rows with the timeout period,
    The cause for this could be that the query needed long time
    to execute, because of large amounts of data to seek & Bad indexing.

    It could also be a Lock issue with tables,
    and that query was long-running. In fact it does not have to be a query
    as such that was blocking, but a process holding locks reason behind
    an earlier discarded query, which was not properly handled.
    All this could have been started by some abnormal event like a
    network problem. However, it is much more likely that there was
    some unsuitable SQL statement.

    Praveen Barath
    Thursday, May 08, 2008 11:17 AM
  • After one week of researching on the Internet and talking with fellow Sys Admins here, none of us can figure out this issue.  The issue is:  We run a single server SharePoint site and that server's got MS Sql Server 2005 on it.  It is a Windows 2003 server with SP2.  Since last Tuesday, our default web site would go down about every two to two-and-half hours and when trying to access the site would get a message stating "could not connect to configuration database."  When looking in error logs, it is not transparent except that on the server, we'll see errors stating access is denied or there is no database available.  After plugging in every SQL Log error code and many Event Log error codes, we cannot figure out the issue!   When looking at the MSSQLSERVER, the SQLSERVER Agent services, they both still show they're running, but when we restart the services, the site still isn't accessible.  We've talked to our Domain Admins to see if they've pushed anything to the domain controllers that out server authenticates throug (they did say they had pushed the latest Windows Server 2003 SP).  Anyway, our only solution now is to re-boot the server every time the database cannot be accessed.  Here's what I've done so far:

     

    NP, TCP/IP, and Shared Memory are enabled (and were before)

    Service account does have access to the database and is not expired

     

    I am new at this so if there's any technical info required of me to clarify, please let me know and I will provide it.  Thank you.

    (I sincerely hope someone can figure this out as I work on an Army installation and our SharePoint site services systems engineers who make requests through out site.  With the time difference, they will try to access the site when we're long gone (usually 12:00 a.m. local EDT).) and of course, the site is down.  Thank you,

     

    Sunrise66NYC

     

    Tuesday, May 13, 2008 7:47 PM
  • When this happens, log in using osql with that user and try connecting through "master" (e.g. osql -U(user) -P(password) -dmaster.  If this login succeeds, issue "use (your database name) go"  if this fails with the same error, then your database is likely in single user mode.  Do you have scheduled maintenance tasks set up that take the db into single user mode such as archive/backup jobs?

    Also, try going to command line and typing "net stop mssqlserver; net start mssqlserver"  that should restart just SQL Server.  If net stop says "service is not running" then SQL Server is not running, which might explain some issues.

    Based on the frequency of the problem, as you have defined it, it is most certainly a scheduled task on either the middle-tier or the DB server.  If you can capture a list of running processes when it happens, you might be able to identify the culprit if it is a matter of some other process opening the DB file with an exclusive lock.

    Hope that helps,

    John
    Tuesday, May 13, 2008 9:14 PM
  • Oh yes!  We are onto something here!  I tried to login using what you said above (e.g. osql -U(user) -P(password) -dmaster) and got:

     

    "Login failed for user '(username)'.  The user is not associated with a trusted SQL Server Connection."

     

    Is that a single user mode error?  If so, can you please tell me how I can correct it?  If that's not what that means, than can you tell me what my next steps should be? 

     

    I want to say a HUGE "Thank You" for getting me started in the right direction!!!  So many of us are just "stumped" on this.  Looking forward to your reply!

     

    Sunrise66NYC

     

    Wednesday, May 14, 2008 2:47 PM
  • To check and see if you running Single/Multi/Restricted mode, simply do the following:

     

    Open SQL Server Management Studio>Connect to appropriate server>In the Object Explorer, right click on the appropriate database (such as "master")>Select "Options">Scroll down until you see "Restricted Access".

    • Proposed as answer by Mathias Voigt Monday, January 09, 2012 1:15 PM
    • Unproposed as answer by Mathias Voigt Monday, January 09, 2012 1:15 PM
    Thursday, May 15, 2008 11:44 PM
  • Thanks for telling me how to do this.  However, apparently that's not the issue -- All Databases show "Multiple."  So it's still not working....

     

    Friday, May 16, 2008 2:04 PM
  • "Not associated with a trusted SQL Server Connection" indicates an authorization issue. Using the osql arguments as above:

    osql -U(user) -P(password)

    indicates that you want to use SQL Server authentication.  So, the most likely problem is that you actually want Windows authentication, rather than SQL authentication.  To clarify, a SQL user is, for example, the sa user; whereas a Windows user would be like DOMAIN\USER.  If it's actually Windows authentication, you need to be running the command line under the context of a Windows user with SQL Server access (e.g., if your DBA set up a Windows authentication login for himself) and run:

    osql -E

    Now, if the username/password you're providing is for a SQL user, and you're seeing this error message, then make sure the username/password is correct.

     

    Now, to address the original issue you have.  Can you post the exact, full error message appearing in the SQL Server errorlogs when you see the application failure?  There should be information in there that will help us narrow down the cause, so we're not guessing as much Smile

    Friday, May 16, 2008 11:31 PM
  • Check your SQL authentication MODE, If mixed then try to login with Windows account and then also check with SQL login. IF you found any issue plz post.

     

    Praveen Barath

     

    Thursday, May 22, 2008 1:37 PM
  • Sunrise,

    I had almost a similar experience this week.We got a STELLENT content software which is using SQL 2K as backend.

     

    There were other databases hosted in this SQL box and one of my colleague restedted the SQL service (planned restart) and Stellent and some of the applications weren't connecting.

     

    When we restarted the server, everything works fine. We again restarted DB services to figure out the root cause. Stellent down after SQL service restart. Raised a ticket with Oracle who bought Stellent and wasn't helpful.

     

    Finally after 2 days we found out the problem.

    The TCP\IP wasn't listening for SQL Server when the service is restarted. But will listen when the machine is rebooted which we can't do much as there is one more instance and some other DB applications.

     

    So we disabled TCP and reenabled after service restart and everything worked like a charm.

    Now we know the immediate workaround for this issue if happen again. Smile

     

    Hope this would help you troubleshoot and resolve this issue.

    Please let us know if this helped you resolve your issue.

     

    thx,

    Jose Mathew

     

    If this post helped you resolve your issue, please mark 'answered'

    Friday, May 23, 2008 12:38 AM
  • You were right -- Issue was ports.  Disabled then re-enabled and all is well.  Thanks!

     

    Sunrise

     

    Monday, June 02, 2008 2:51 PM
  • Great. Glad to know that it helped.

     

    thx,

    Jose Mathew

     

    Monday, June 02, 2008 4:47 PM
  •  

    Hi Jose,

    i have got the same error, but i cant restart the server till we didnt get the confirmation that it will be resolved after restart, could you please tell me, what else reason can be possible.

     

     

    Sunday, June 29, 2008 6:51 AM
  • Wow, this is quite possibly the worst functioning forum I've ever used.  Login redirect loops to My Profile when I hit reply, finally have a reply box open... Hat's off, MS. 

    Anyways, I was experiencing the same issue due to the following:

    I had set the Recovery Mode (under the database's properties -> options) to "FULL" and as such the transaction log was over 7 GB, which was clearly choking SQL.  I ran the following, which immediately fixed the issue (and shrunk the .ldf log to ~ 700kb):

    DBCC SHRINKDATABASE (MyDatabase, TRUNCATEONLY);

    Hopefully this is helpful for someone.
    Thursday, October 29, 2009 3:44 PM
  • Another variant of this problem...  We are having issues with our 3rd party backup software.  Getting error "Unable to complete the login process due to delay in opening server connection."  Further testing revealed that we can duplicate the error with OSQL.
    OSQL -E returns the same error, but OSQL -U -P works great.

    The backup software only supports Windows authentication using a service.  It fails with both LOCAL SYSTEM and domain authentication.

    Any ideas?  Is the DC still involved in LOCAL SYSTEM authentication?
    Tuesday, December 22, 2009 4:33 PM
  • Hello,

    I am experiencing the Login Timout Expired error message when running multiple SQL Server jobs, each running one or more SSIS packages, each connecting to SQL Server databases. I saw somewhere that in my case this is a server overload issue on the login, and remember that Windows SQL Server 2008 did not appear on impacted products list. If you are in same situation as me, I would then recommand to #1 modify schedule #2 upgrade to SQL Server 2008. Hope this helps,

    a+,=)
    -=Clement=-

    Configuration :
    SQL Server 2005
    Windows Server 2003
    SQL Server Integration Services 2005

    Friday, July 09, 2010 4:28 AM
  • Hello,

    I am runnig into the same issue.  We have 1 W3k SP2 x64 hosting Sharepoing DB server, and 1 W3k SP2 X64 Webserver hosting Sharepoint frontend.  Every 2 weeks at 2:00 AM DB Server would freez, and no one can login to the Sharepoint site. Orion will report website is down, and DB as down. Only way to fix is to login through ILO and hardboot the DB server. I thought there was something going on with legato because legato had a backup  job scheduled which ran at 2:00 AM everyday. Since last 2 weeks we have disabled that job to eliminate Legato out of the picture. Yesterday, we had the same problem so if there is any update on this issue, I would greatly appreceate your help.

    Thanks!

     

     

     

     


    TS
    Wednesday, December 22, 2010 11:14 PM
  • 1)Login time out error may be the system resource (RAM USAGE HIGH) need to a more memory space. so verify the memory usage.

    2) another one point is monitor net work also

    Wednesday, September 21, 2011 12:45 PM
  • It solved our problem as well.

    Another question is how to change from NTLM to Kerberos.

    aa

    Thursday, May 01, 2014 2:51 PM