Problem with connection pool 4

[jby1]

Hi

I have been having a recurring problem with the connection pool, getting message like

USERNAME: etc: 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 have tried increasing the maximum pool size in the web.config file. Whilst I knew all along that was only putting off looking for a solution, it helped me until just now, where it is coming back with a vengeance and demanding attention.

Does anybody have any idea where I might look for the root cause of this problem?

Thanks

 

[andrea96]

Are you closing your connections after you are finished with them?
Andrea

 

[jby1]

I believe so, I certainly always intend to do that. I will of course go through and make sure.

Is there anything else that could cause this kind of error?

 

[jby1]

I have had a look, at it does seem as if the connection is getting closed when it should be.

The one area I am not so sure about it when a DataReader is bound directly to a control such as a drop down list. Does the connection get automatically closed once the list is populated?

 

[jby1]

A quick investigation has indicated that the problem may indeed be with directly binding readers to List controls, without then closing them.

If this doesn't seem correct, please let me know before I start changing all my code!

 

[andrea96]

I don't think the connection is automatically closed. You should close the connection after you close the DataReader.

 

[dvannoy]

are you using SQL server as your backend?

if so run SP_WHO. this will show you your connections.

also you need to close eveything that has to do with getting data. readers, adapters etc. if not your app will fail after a while. if you run SP_WHO and see a high amount of connections you may need to kill them as well.

 

[jby1]

Thanks for the help so far

I have gone through the code and made sure that every reader is being explicitly closed. The dataset's connections were already being closed.

I am now using sp_who to monitor the connections as I do certain things in the system. The number of connections does not seem to be as great, although there are still some appearing that do not go away, with the cmd AWAITING_COMMAND. There does not appear to be a pattern to when the number of connections increases.

Should I be concerned about this?

 

[ca8msm]

Maybe...maybe not! I'd suggest looking in Enterprise Manager at the Current Activity (which is found by going into the Management node). You'll be able to see the current activity which is occuring on the db, and also view which processes are locked (which may help you identify if any connections aren't being closed). You can also release these processes if yuo need to.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[jby1]

Thanks again for the excellent advice!

I have checked the current activity, and there are a lot of processes there for my application, with a status of sleeping. There aren't any that appear locked. It still looks to me that there are a lot of connections, although I must admit that DBA work isn't my strong suit, so I find it hard to know whether this is a bad sign or not.

 

[ca8msm]

Yeah, it may be worth a post in the SQL Server forum (forum962) for some more help if they start to increase.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[jby1]

Cheers, I think I will do that, it seems that this problem is beyind the scope of ASP.NET. Although I still believe that the root cause of the problem must be coming from my application code! I have discovered a useful tool under the Management node you mentioned, called Locks/Process ID, which is giving me details of what SQL was running on the connections. Hopefully this will help me track down the problem.

Thanks again for all assistance!

 

[ca8msm]

Yes, you can also go further down a node to the Locks / Object where you can view details of those process id's.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[jby1]

This is an example of the code I have put in to close the reader. However, it does not appear to be letting go of the SQL connection. Am I missing something?

IDataReader reader = null;

try
{
	reader = new TargetAudienceBO().GetAll();
	this.cboTargetAudience.DataSource = reader;
	this.cboTargetAudience.DataValueField = "TargetAudienceId";
	this.cboTargetAudience.DataTextField = "AgeRange";
	this.cboTargetAudience.DataBind();
}
finally
{
	reader.Close();
}

 

[ca8msm]

And what do you have in your GetAll() function?


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[jby1]

Oh sorry!

The GetAll function returns a DataReader.

The code that actually populates the reader:

internal class ExecuteDataReaderCommand : DataAccessBase, ICommand
{
	internal SqlParameter[] sqlParameters = new SqlParameter[] {};
	internal string storedProcedure = "";
	internal SqlDataReader dataReader;
			
	internal ExecuteDataReaderCommand(SqlParameter[] sqlParameters, string storedProcedure) 
	{
		this.sqlParameters = sqlParameters;
		this.storedProcedure = storedProcedure;
	}

	public void Execute()
	{
		if(this.SqlCon.State != ConnectionState.Open)
			this.SqlCon.Open();
		dataReader = SqlHelper.ExecuteReader(SqlCon, storedProcedure, sqlParameters);
	}
}

which is called like this:

public IDataReader GetAll()
{
	ExecuteDataReaderCommand command = new ExecuteDataReaderCommand(new SqlParameter[]{}, "spTargetAudienceGetAll");
	command.Execute();
	return command.dataReader;
}

(SqlHelper is from the Microsoft Application Blocks)

 

[jby1]

I think I have solved it.

The problem seems to have been in the SqlHelper class (which I believe people here have modified)

It was with this code:

// Call ExecuteReader with the appropriate CommandBehavior
if (connectionOwnership == SqlConnectionOwnership.External)
{
	dataReader = cmd.ExecuteReader();
}
else
{
	dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}

The connection was being set to External (which is there for use with transactions), when it shouldn't have been.

Hopefully this will be the end of the matter!

If anybody thinks this doesn't look like it is the problem, please let me know!

 

[ca8msm]

Could be. I haven't used the SQLHelper class so I can't confirm - I'd just keep an eye on it and let us know if it solved it.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[jby1]

I have been watching it this afternoon, and I am now 99% sure that this has solved the problem.

Into the bargain, I now properly understand how connection pooling works, and have learned a few useful DBA commands.

So a success all round!

 

[ca8msm]

Excellent...hope this turns out to be the solution and thanks for posting your findings.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.