SQL Server database connections being dropped intermittently

Question

Occasionally in one of our production environments (we have a dozen or so without different clients pointing to each) we get exceptions at different points in the process. The code doesn't do anything fancy - just your basic inserts and updates. We are however, performing multiple inserts and updates within a single connection across multiple methods.

We'll get exceptions like this:

System.InvalidOperationException: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.
at System.Data.SqlClient.SqlConnection.GetOpenConnection(String method)
at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command)....

and this:

System.Transactions.TransactionManagerCommunicationException: Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool. ---> System.Runtime.InteropServices.COMException (0x8004D024): The transaction manager has disabled its support for remote/network transactions. (Exception from HRESULT: 0x8004D024)
at System.Transactions.Oletx.IDtcProxyShimFactory.ReceiveTransaction(UInt32 propgationTokenSize, Byte[] propgationToken, IntPtr managedIdentifier, Guid& transactionIdentifier, OletxTransactionIsolationLevel& isolationLevel, ITransactionShim& transactionShim)
at System.Transactions.TransactionInterop.GetOletxTransactionFromTransmitterPropigationToken(Byte[] propagationToken)
--- End of inner exception stack trace ---
at System.Transactions.Oletx.OletxTransactionManager.ProxyException(COMException comException)

and this:

System.InvalidOperationException: Invalid attempt to call Read when reader is closed.
at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
at System.Data.SqlClient.SqlDataReader.Read()

I don't think we need to turn on MSDTC (again things work in every other environment). Suggestions on what we can check code-wise or environment-wise?

Answer 1

What do your methods look like that are calling the Database commands? It sounds like you have an open connection spanning across multiple blocks of logical code for a very long time.

I recommend calling SqlConnection.Open() right before you need the connection open, and SqlConnection.Close() when you are complete with the connection. This should all be taking place in a try/catch block.

If you are doing the above method, that means the connection isn't timing out. In which case, you need to look to the SQL Server error log to see if there are any errors being thrown that you need to look at.