0

I have an application that runs every minute using the Windows Task Scheduler. One of the first things the job does is connect to a SQL Server 2008 instance to record that the application started.

A few times a day the job is failing when making its first query with the following error message:

Connection Timeout Expired. The timeout period elapsed during the post-login phase. The connection could have timed out while waiting for server to complete the login process and respond; Or it could have timed out while attempting to create multiple active connections. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=9; handshake=10342; [Login] initialization=0; authentication=1; [Post-Login] complete=4032;

We have hardware monitoring on the database server so I checked for CPU, RAM or Disk IO spikes at the times of the failures, but there weren't any spikes. The connection string is setup to use integrated security (using one of our domain service accounts). The pre-login handshake is always taking the longest.

This database server is used by a lot of applications and no other application is failing with this message. We have web apps that use the database server without problems as well as other applications that run through the task scheduler (although they don't run as frequent as every minute).

Can I increase the SQL Server logging level to expose more information about what's happening during login? Any ideas how I can address this issue?

Edit: I'm also seeing the following error message (although less frequent):

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The wait operation timed out.)

  • Is it possible you can correlate these incidents with heavy traffic on AD or DNS? Possibly even failing over to backup domain controller? Does it have to use a domain account? Could you try running it for a few days with a SQL auth account, to rule out any AD issues that are outside of SQL Server's direct control? – Aaron Bertrand Sep 23 '15 at 15:22
  • @AaronBertrand - Switching to a SQL account to rule out the domain controller is a great idea. I think that's the next thing I'll try. Is there anything I can do on the SQL Server side to determine an AD or DNS issue? Any logs that would indicate something like that? – Justin Helgerson Sep 23 '15 at 15:42
  • AD/DNS are a black box to SQL Server. It tries to authenticate, and whatever happens it reports in the error log. Usually this reporting is rudimentary. – Aaron Bertrand Sep 23 '15 at 15:47
  • @AaronBertrand - I'll be trying the SQL account tomorrow. I updated my answer with another error that I'm seeing, although it's occurring less often. Could that error still be an indication of an AD issue? Thanks for your help so far! – Justin Helgerson Sep 24 '15 at 14:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.