1

One of our clients recently came to us with a problem he's facing while working with our application. The error in the log file is the next:

System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. This failure occured while attempting to connect to the Principle server.
System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

As far as I know, that client has set up mirroring between two database servers and he's currently working on the fail-over server (They do that on purpose each now and then).

I am aware that I could use the CommandTimeout property in my code to increase the time before a timeout is reached.

The application is working normally, so it's connecting to the database way before this error happens. Also, connecting through SSMS is working.

Note that, due to security reasons on my client-side, I can't use a profiler to narrow down the problem to a specific query or procedure.

What I am actually concerned about is the error message in bold.

Does that error mean that the actual problem is a connection problem between the Main DB Server and the Fail-Over server or could it also be triggered by a query taking more than the default 30 seconds?

  • I think it is difficult to come up with a direct answer to tell you the root cause, but I would suggest you check the sql server error log first and see whether there is error message there related to login failure. On the other hand, make sure that from the application host side, you can connect to the back-end sql server instance using some different methods such as using SSMS or PowerShell, this will help rule out whether there is any firewall issue that prevents the front-end computers connecting to the back-end server box. – jyao Jul 16 at 5:47
  • @jyao There's no problem with connecting using SSMS, I'll add that as an edit to the OP. – Paul Karam Jul 16 at 5:52
2

You mention two key things in your post:

  1. Mirroring
  2. Work is being performed against the failover-server

I suspect what is going on is actually expected behavior. Mirroring connection strings define both a Server (e.g. the Principal Server) and a Failover_Partner (e.g. the Mirror Server). When establishing a connection to a mirrored database, if a connection to the Principal Server cannot be attained, an attempt to connect to the Mirror Server is then performed.

Per Connect Clients to a Database Mirroring Session (SQL Server):

If a connection attempt fails or the retry time expires before it succeeds, the data access provider tries the other partner. If a connection is not opened by this point, the provider alternately tries the initial and failover partner names, until a connection is opened or the login period times out.

What I suspect the error is showing is the failed attempt to connect to the Principal Server because the database is actually active on the Mirror Server. The error is thrown to your logs and then a successful connection is established against the Mirror Server because that's where the database is open/active.

This is expected behavior in a mirror configuration, and if you're able to (eventually) connect to the database, that means mirroring is working. Take note that once you failover the database back to the Principal server, this error should no longer occur because the initial connection attempt to the Principal Server will once again succeed.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.