What are some good ways to debug timeouts? (C#)

Question

I'm building a site that runs fine for a few hours, but then *.asmx and *.ashx calls start timing out.

The exception is: "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'm using SubSonic as the ORM.

I suspect that the problem is based on a scheduled task that runs every few minutes and hits the database. When I look in SQL Server 2000's "Current Activity", I see there are:

• 100 processes with the status "sleeping"
• 100 locks

The 100 processes are from the Application ".Net SqlClient Data Provider" and the command is "AWAITING COMMAND".

So I'm guessing that's the issue . . but how do I troubleshoot it? Does this sound like a deadlock condition in the db? As soon as I

c:\> iisrestart

, everything's fine (for a while).

Thanks - I've just never encountered something like this and am not sure the best way to proceed.

Michael

Answer 1

I don't know anything about Subsonic, but maybe you are leaking database 'contexts'? I'd check that any database resource is being disposed after you're finished with it...

Answer 2

It could be a duplicate of this problem - http://stackoverflow.com/questions/690828/is-connection-pooling-working-correctly-in-subsonic

If you're loading objects with Load() instead of LoadAndCloseReader(), each connection will be left open and eventually you'll exhaust the connection pool.

Answer 3

When you call Load() on a collection it will leave the Reader open - make sure you call LoadAndCloseReader() if you want the reader to close off - or use a using block.

It helps to have some source code as well.