0

I've got a windows service that regulary (every 10 sec) does a lot of db activity. I've verified that all my connections are closed (using using). After some time (days or hours) I get this error:

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

I found that I should check the sysprocesses table, and found that it is creating lots of new entries when my code is run (and I'm really sure I'm closing the connection)

Question is: In my case, where it is the same user doing the same queries all over, should there be a new entry for almost every tick in the service? Other services does not have this behaviour.

Or is maybe looking in the sysprocesses table not giving me any useful info?

Larsi

Larsi
  • 4,654
  • 7
  • 46
  • 75

1 Answers1

1

sysprocesses is deprecated so if you're on 2005+ use sys.dm_exec_requests, sys.dm_exec_connections and sys.dm_exec_session.

that'll give you better info about your connections.

Mladen Prajdic
  • 15,457
  • 2
  • 43
  • 51
  • ok, thanks. Could you explain the relations between them? It seems like for each request I'm getting a new sessions with only one connection in. Is this correct? – Larsi Sep 13 '11 at 10:42
  • if you look at the BOL page on sys.dm_exec_connections you can see the relationships between the DMVs. http://msdn.microsoft.com/en-us/library/ms181509.aspx – Mladen Prajdic Sep 13 '11 at 11:41