Why does Enterprise Library throw intermittent "Data connection failed to open" errors?

Question

I'm supporting a 3 year old windows service, (C# .Net 1.1) which uses Enterprise Library to manage it's connections to SQL server. The service monitors incoming messages, and does various tasks depending on the message, most of which involve updating a database. As far as I know, the service is single threaded, and reads messages out of a queue one at a time.

A couple of times in the last few days we've seen it "break" randomly (last time at about midnight last night). During these times, some, but not all updates to the DB fail, (about 20% maybe), we see lots of warnings in the event log, and errors in our file logs (reproduced below). Restarting the service makes everything work perfectly again.

I'm thinking perhaps it's something to do with the connection pooling in Enterprise Library? Is it possible Enterprise library is trying to maintain a pool of more connections than the DB server will allow it? But I can't see why it would, and why it wouldn't fail a bit more gracefully.

Is there a way I can get a bit more information out of my enterprise library logging as to the real reason for these failures?

Warnings in the event log look something like: (slightly edited)

    Source: Enterprise Library Data Service
    Description: Data connection failed to open: server=ServerName;database=DatabaseNameintegrated security=true;

Simultaneously, in the Error log file we see Null Reference exceptions from Enterprise library, when called from our service, something like this: (again, slightly edited)

    An exception of type 'System.NullReferenceException' occurred and was caught.
    -----------------------------------------------------------------------------
    02/17/2009 15:00:52
    Type : System.NullReferenceException, mscorlib, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
    Message : Object reference not set to an instance of an object.
    Source : System.Data
    Help link : 
    TargetSite : System.Data.SqlClient.SqlInternalConnection CreateConnection()
    Stack Trace :    at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction)
       at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction)
       at System.Data.SqlClient.SqlConnection.Open()
       at Microsoft.Practices.EnterpriseLibrary.Data.Database.OpenConnection()
       at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteNonQuery(DBCommandWrapper command)
       at OurCode.SomeFunction(MessageType message) in C:\someClass.cs:line 45
       at OurCode.SomeOtherFunction(XmlReader message)

    Additional Info:

    MachineName : MachineName TimeStamp : 17/02/2009 15:00:52
    FullName : Microsoft.Practices.EnterpriseLibrary.ExceptionHandling, Version=1.1.0.0, Culture=neutral, PublicKeyToken=a8dcbcfec587cc95
    AppDomainName : OurCode.service.exe
    ThreadIdentity : 
    WindowsIdentity : OurDomain\SomeAccount

Earlier (in a previous "broken" phase) we were seeing Exceptions like this one: (once again, slightly edited to be coy!). This is what made me suspect the connection pooling having problems. I think this is where our code logs it's (non error) activity to a DB, but the enterprise library code can't get a connection to the DB again (always coupled with the "Data connection failed to open" errors mentioned previously.

    Sink failed because: System.NullReferenceException: Object reference not set to an instance of an object.
       at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction)
       at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction)
       at System.Data.SqlClient.SqlConnection.Open()
       at Microsoft.Practices.EnterpriseLibrary.Data.Database.OpenConnection()
       at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteNonQuery(DBCommandWrapper command)
       at OurCode.Diagnostics.CustomDatabaseSink.ExecuteStoredProcedure(LogEntry logEntry)
       at OurCode.Diagnostics.CustomDatabaseSink.SendMessageCore(LogEntry logEntry).

Any help, especially pointers for getting more info out of our logging much appreciated.

Other background info: The windows service is written in C# .Net 1.1, and runs on a Windows Server 2003 box, under a domain account (which has all the right permissions to the DBs it needs). The DB server is SQL 2005, running in 2000 compatability mode (Yay legacy!), on Windows Server 2008.

casperOne casperOne · Accepted Answer · 2009-02-17T17:30:18

From what I can tell, this is happening when the internal connection object is being created in SqlConnection (you would get this using enterprise library or not, I believe).

There are two things I would check. First is the obvious, are you disposing of all of your connections properly? Even though you are using connection pooling, you still must call Dispose on all of your connections when you are done using them so they are returned to the pool. If not, you could be looking at a starvation issue.

If you are disposing of your connections properly, then I would look at the connection between that machine and the SQL Server, it would seem maybe you have connection problem there.

Why does Enterprise Library throw intermittent "Data connection failed to open" errors?

1 Answers