2

I have a DLL that is referenced by a Windows Service that has a number of jobs fired by timers throughout the day (different intervals per job, from 20 seconds to 15 minutes) however lately in my log file I have noticed lots of errors with this message:

SQL Error: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

I don't know whether there is some setting in SQL (2015) that I can change or look at or whether it's a code issue.

The code in the Service that calls the different jobs in the DLL (which has an SQLDatabase class) always calls Control.Dispose() after finishing which destroys all the objects including the SQLDatabase class which destroys the connection in it's own dispose method.

e.g after a timed job has run I dispose of it e.g

Control control = new Control();
control.RunJobs("SOME_JOB_TO_RUN");
control.Dispose();

Which then calls a method on my own main class Kill() which calls

this.DataAccess.Dispose();

And that should kill the object and close the connection e.g in my DataAccess object...

public class SqlDataAccess : System.IDisposable


public void Dispose()
{       
    if (_connection != null)
    {
        _connection.Close();
       _connection.Dispose();
    }   
}

So a) I am not sure why these errors have started popping out of nowhere all of a sudden and I have increased the timer gaps to reduce the number of processes running to see if that helps (it hasn't).

Also as multiple processes could be running at the same time (due to timer overlaps), each call to the DLL from each method (called by the relevant timer), shouldn't (as far as I know - I could be wrong) be able to share connections or know about other processes going on at the same time.

So I am unsure whether I should or shouldn't be killing the DataAccess class and destroying the connection to allow for pooling or whether I should be doing something else.

As far as I was aware (like ASP classic), re-use of connections was the preferred method and we always used to open a connection to the DB at the earliest point (when required), re-use that connection throughout the page, and then destroy it at the end when no longer required.

However this is a Windows Service with multiple processes all being called at overlapping times with timers making use of a DLL that does the work. I am re-using the connections during each timed process call and destroying them at the end as you can see but maybe I should be doing something else.

Any help or ideas would be much appreciated.

alex.b
  • 4,547
  • 1
  • 31
  • 52
MonkeyMagix
  • 677
  • 2
  • 10
  • 30
  • Are any exceptions being thrown in the 'RunJobs' code? This will prevent connections from being closed. – Paul Zahra Jun 06 '16 at 14:41
  • You should ideally have your 'RunJobs' code inside a try finally... and in the finally close the connection... that way it will always close, exception or not... or you could put your code in a using statement... which will close if an exception is thrown. – Paul Zahra Jun 06 '16 at 14:42
  • Are you using a DataReader or similar in the RunJobs code? Make sure you close the reader. – Paul Zahra Jun 06 '16 at 14:46
  • So the Service calls the DLL by calling the Control.RunJobs("job-name") in the DLL, the init on control creates my main class that holds references to the data object which is why I thought having the close connection (and other object destruction) in the dispose/kill method was the right place. The exceptions are being thrown inside methods called from the RunJobs methods yes. I didn't know if an exception was thrown whether dispose still got called or not. Most SQL are DataTables being looped through. I will try closing the connection in a finally in the Control.RunJobs method thanks. – MonkeyMagix Jun 07 '16 at 13:32
  • By the way, the ONLY exceptions being thrown are due to these Timeout errors due to not getting a connection. These have only started within the last week or so and no major code changes have occurred. As this DLL can be used anywhere on any of our servers (or my PC), depending on whether its being used by a BOT, Console, Service, it has tests on init to ensure it can connect to the DB and read from it and connect to the 3rd party API. If these fail, I destroy everything and re-connect, re-set session etc. I thought pooling of connections could be managed on the DB side of things as well? – MonkeyMagix Jun 07 '16 at 13:37
  • A question, I just asked a colleague and he said he wrapped EVERY SQL call in a using statement that destroyed the connection so it would open/close a connection for every SQL statement called. As I am calling lots of SQL statements in ONE RunJobs(); call, is it better in .NET to re-use connections or open/close for every SQL statement executed? – MonkeyMagix Jun 07 '16 at 13:50
  • As you are firing off lots of SQL statements, if you can relatively easily encapsulate them in a using statement then go for it... far better than multiple opens and closes... if you are going to start passing around a connection as a parameter be careful... for example in Entity Framework / .Net it's best practice to have one connection / using statement per request (akin to the Unit Of Work pattern), with one or more statements fired off inside.. and once the request is serviced you kill the connection at it's end... it is then left to the connection itself to optimise connection pooling. – Paul Zahra Jun 07 '16 at 14:40
  • When the Control class is instantiated I create an instance of my DataAccess class then that is accessible by any method needing to call an SQL statement. The object isn't passed around as a param, its just in a central class that all other classes have access to. Then I was disposing of it in the Control.Dispose(), I have wrapped my RunJobs method in a try/catch/finally with an explicit call to DataAccess.CloseConnection() now in case of exceptions that prevent the dispose/kill method from running (although I didn't know that if an err was thrown the wrapper objects dispose wasn't called) – MonkeyMagix Jun 07 '16 at 16:26
  • Sounds good... let us know ow it goes... – Paul Zahra Jun 08 '16 at 07:57
  • Still getting those connection pool errors, wondering whether I should add the Pooling=false; to the connection string or increase the Connect Timeout attribute? – MonkeyMagix Jun 08 '16 at 13:32
  • Have a look at bottom of the accepted answer http://stackoverflow.com/questions/670774/how-can-i-solve-a-connection-pool-problem-between-asp-net-and-sql-server I would do some inspection of the pool – Paul Zahra Jun 08 '16 at 16:02
  • I did try implementing the class displayed, ConnectionLeakWatcher, however all I got (apart from a frozen windows app that allows me to turn the service on/off easily), was a list of connection open/close with numbers - all sequential e.g Connection opened 604; Connection closed 604; Connection opened 603; Connection closed 603; I have rewrote my TestConnection method to always close and if any DB errors I have a ReConnect() method to destroy all objects, close connections and reconnect me so far so good. – MonkeyMagix Jun 09 '16 at 14:15
  • Sweet... to give yourself some confidence in your solution take a look at https://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx ... pay attention to the default maxpoolsize which is 100 ... maybe you want to alter that too ? – Paul Zahra Jun 10 '16 at 08:39

0 Answers0