Connection times out 1

[LV]

I keep getting the following error raised from within the app:

System.InvalidOperationException: 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.

The error can occur from any place in the application just suddenly, although this part was working just fine before.

All connections are closed explicitly in the code.
The settings in SQL server: Maximum # of concurrent connections: 0 (unlimited), Query timeout: 600 sec.

Any ideas? Thanks in advance.

 

[harmmeijer]

Looks like this error is not caused by any syntax error. SQL server just runs out of connections (unlimmited does not exsist). And none of the connections in the pool are released.

You can open SQL server enterprise manager -> management -> current activity -> process info to see if any old processes stay in there (the lists here do not autorefresh so please right click current activity and choose refresh).

In asp it used to be good practice to close a connection and set it to nothing, the connection would not be closed but the asp engine marked this connection as an available connection from the pool.

If you run a transaction you can now:
open a connection
start a transaction
try - do things + commit
catch - rollback things
Finally - close connections

You can be sure that the connection allways closes, if the transaction commits or rolls back.



Greetings, Harm Meijer

 

[LV]

Harm,

Thanks for your reply. We do our transactions in this exact sequence. But I didn't know about the monitoring tool in the Enterprise Mngr. Thanks again.

 

[harmmeijer]

You can use sqlprofiler for a rapport on (among other stuff) connections to sql server.
I use it to see my commands execute.
Open sqlprofiler -> file -> new -> trace -> fill in your sql server and a user with sa privilege ->
template name: sqlprofilerStandard
events: remove Session, Stored procedures, TSQL keep only Security audit (audit login, audit logout)

Start the trace, after a while you should see "audit login", "audit logout". You can keep this trace running and check after a while what is happening.

If you show stored procedures and TSQL you can see what your app is doing as well allthough you could get too much trace info.



Greetings, Harm Meijer

 

[LV]

Harm,

Thanks a lot. I'll give it a shot.

 

[LV]

Harm,

Got two questions, for you:
1. How do you create a table with a name base, say, on GetDate()? I tried but SAL doesn't seek to like it.

2. Is there any command to turn database logging off? Couldn't find it anywhere.

Sorry for bothering, I always do my research first before I start asking but couldn;t find anything in this case.
Thanks in advance.

 

[harmmeijer]

I am not sure what you mean.
1. In sql 2000 it should be possible to have tables starting with a number and a / or - in it (tried in the enterprise manager gui not with a create table statement).
2. If you are talking about the profiler there is a stop button in the menu bar of the main window.

I have to say when I started the profiler on the same pc as the sql server that I run the trace on it freeses when I try to stop the trace. Happend on 2 different computers. (home and @Work).



Greetings, Harm Meijer

 

[juliaroberts]

Regarding the connection timeout.....
.. I had this problem, but it had nothing to do with closing connections. I had a method which opened a connection and I was accidently calling the same method recursively within the method, which resulted in all the connections getting used and a timeout occuring.

 

[LV]

juliaroberts,

Thanks, we have discovered some recursive references to our data provider class, that in turn has a reference to Sql connection.


Harm,

Sorry for mistyping and not explaining it well. It's a different from connection timeout problem.

1. I have a stored proc that runs a cursor, populates a temp table, spits out a recordset off of it and drops the table. I need this table name to be unique (because of multiple users), so I was thinking about giving it a name, based on current datetime. But when I set a variable to a table name in CREATE TABLE statement, SQL doesn't like it.

2. I was wondering, if there is any way inside this stored procedure to turn off and then on (after the proc is executed) database logging, so the cursor execution is not written into the log. Cursors seem to fill the log real quick.

Thanks again for your help.

 

[harmmeijer]

Sorry, I cannot answer the 2 questions.
1. could not do it like this:
declare
@TableName varchar(255)
select @TableName = 'hello'--getdate()
select @TableName
CREATE TABLE @TableName ( ...


2. Is your transaction log getting too big when you use the cursor? Is it not truncated when you are done? What is logged can be influanced with transactions and recovery settings of the database. Here is code I used when the database log would not shrink:
use dbName
Alter database dbName set RECOVERY SIMPLE
dbcc shrinkfile(2,0)
alter database dbName set RECOVERY FULL
A point in Time recovery is not possible anymore because there is no more info in the transaction log.

You could post both questions in the SQL server group.




Greetings, Harm Meijer

 

[LV]

Harm,

Thanks, that exactly what I was looking for. All works now just fine as it supposed to. The log is not growing a lot now when I run the cursor but has a potential to became big, as the amount of records to loop through grows. So I was just thinking a little ahead. Thanks again for your help.