ASP.NET Webservice Query MSSQL Timeout (Poolgrösse/Poolsize)

Question

I created a new webservice where I want to get some SQL queries. It worked for a while but sometimes I get the following error:

    System.InvalidOperationException: Timeout abgelaufen. Das Zeitlimit ist vor dem Anfordern einer 
    Verbindung aus dem Pool abgelaufen. Möglicherweise werden alle Verbindungen im Pool bereits 
    verwendet, und die maximale Poolgröße wurde erreicht.
    bei System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, 
    TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, 
    DbConnectionInternal& connection)
    bei System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection 
    outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, 
    DbConnectionOptions 
    userOptions)
    bei System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
    bei System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
    bei System.Data.SqlClient.SqlConnection.Open()
    bei SailTrainer.GetSchueler(String un) in C:\inetpub\wwwroot\alert\App_Code\SailTrainer.vb:Zeile 81.

after a while it just work again.

Any of you an idea?

What I've tried:

I tried con.Close() at the start of the function that it closes already open connection before opening a new one but that doesn't seem to work.

and every time I save my .asmx file it works fine again for a few queries.

Answer 1

Möglicherweise werden alle Verbindungen im Pool bereits verwendet, und die maximale Poolgröße wurde erreicht.

An often cause is when you do not cleanly dispose all managed objects, like DataReader or die connection; then the connection don't get back into the connection pool.

You can check this on SQL Server by listing the existing processes

select *
from sys.sysprocesses
where spid > 50

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 2

Hi Olaf, 

Thank you for your quick reply.

I just tried that and I get 88 rows and most of them have the status "sleeping". I checked all my services and think everytime a make con.Open() I also close it at the end with con.Close().

Will they automatically be available after some time again (don't have the status "sleeping")? Or do I have to do something? Maybe during development I missed some con.Close() and they are still open since then?

Answer 3

As Olaf says, this is often due to not closing connections correctly. Be sure that you create connections in using blocks:

  using (SqlConnection cn = new SqlConnection(connStr) {

  }

This makes sure that the connection is returned to the pool when you exit the using block.

Here is a better version of the query that Olaf posted:

SELECT * FROM sys.dm_exec_sessions WHERE is_user_process = 1

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

Thank you very much, I'm gone try this.

Answer 5

In Visual Studio you can use the code analyzer, which find missing dispose, see Code Analysis for Managed Code Warnings

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 6

I tried putting all my Queries inside a Using but it still doesn't work.

Is Code Analysis for Managed Code Warning -> Run Code Analysis on Solution? Because this does nothing (Or I don't see it)

Answer 7

Is the max size of pools 100? Because I just ran into the problem again and I run your command and it returned 100 rows, I saved the file, tested my application and run the SELECT again and only got 8 rows.

Answer 8

The following is how I used the Using. Is this correct like this?

Dim querystring As String = "UPDATE LessonPos SET Result = CASE WHEN " & res & " = 5 THEN 0.5 ELSE " & res & " END WHERE (LessonPosID = '" & sep & "')"
        Using (con)
            con.Close()
            con.Open()
            Dim query As New SqlCommand(querystring, con)
            query.ExecuteNonQuery
            con.Close()
            con.Dispose()
        End Using

Answer 9

I just saw that another webservice is running in the same project. Is there a ways to Query which file the origin is?

Answer 10

The following is how I used the Using. Is this correct like this?

I don't know ASP .NET, but in C# you say:

  using (SqlConnnection cn = new SqlConnection(connStr)) {

And reasonably, it should be something similar in ASP .NET. Judging from the Dim statement you have, maybe:

  Using(Dim con as new SqlConnection(connStr)

The important is that you create a new object in the Using statement. Just entering an existing object is kind of pointless.

For the exact syntax, you would get better help in an ASP .NET forum.

Now over to something else which is very wrong:

Dim querystring As String = "UPDATE LessonPos SET Result = CASE WHEN " & res & " = 5 THEN 0.5 ELSE " & res & " END WHERE (LessonPosID = '" & sep & "')"

This is something which is completely impermissible in production code. The correct way to write this is:

Dim querystring As String = "UPDATE LessonPos SET Result = CASE WHEN @res = 5 THEN 0.5 ELSE @res END WHERE (LessonPosID = @sep)"

And once you have created your SqlCommand object you do:

query.Parameters.Add("@res", SqlDbType.Int).Value = res
query.Parameters.Add("@sep", SqlDbType.VarChar, 20).Value = sep

This makes the statement a parameterised statement. There are a number of reasons why you should write your code this way. The most important for you is that this is a lot simpler, since the querystring now is one single string and a lot easier to write and maintain.

There is also a very important security aspect: the above code permits for SQL injection. That is, it permits an evil user to enter SQL code that your code will execute, and that code could be doing something really naughty.

I have written some more text about parameterised statements, which you can read about here:
http://www.sommarskog.se/dynamic_sql.html#ClientCode

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 11

Is the max size of pools 100?

That is the default pool size. I belive you can change the size in the connection string.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 12

I just saw that another webservice is running in the same project. Is there a ways to Query which file the origin is?

If you set the application name in the connection string, you can see in sys.dm_exec_session from where the connection comes.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 13

Is the max size of pools 100?

100 is the default max pool size, which can be defined in the connection string, see https://www.connectionstrings.com/all-sql-server-connection-string-keywords/ => "Max Pool Size". You can increase the value, but taht won't solve the cause.

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 14

Hi Felix Niedermann,

Very happy your problem is solved.

""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
For more information, please refer to the sticky post.

Best Regards 
Echo   

---

""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
For more information, please refer to the sticky post.