SQL server timeout

Question

Hi, my application is developed on classic asp, but also uses asp.net as I am migrating the application on .Net. Its using SQL server as database and hosted on Windows server 2003.

Now the problem is that the application continue to work perfectly fine for a long time but then after some time SQL server gives timeout error and it could fulfill any of the requests made. It doesn't get fixed even when I restart my SQL server or even IIS, ultimately I have to restart my server every time which only fixes the problem.

Any idea what might be causing the problem? Just to give a rought idea, the site is used by around 300 people at peak times.

Any idea what might be causing the problem? Just to give a rought idea, the site is used by around 300 people at peak times. I am certainely closing connection everywhere, my end code on each page closes the connection. If an error occurs before the end page, the expection handler closes the connection. So I am sure that closing the connection isn't an issue. And that there are no open connection if I see the sql logs. Our server, only one box, has SQL Server, IIS, iMail (our mail server). After I had restarted SQL Server, it did not solve the problem. Only restarting Windows Server, it worked. From perfom, IO usage is quite high. Is there any suggestions?

Thanks, wildanjel

Answer 1

At the very least, are you closing the connection to the database, once you are done using it in the code? Also, how does your connection string look like? does it use connection pool?

EDIT: I saw your comments. Are there pending transactions to be committed?

Answer 2

It sounds a lot like there's an unmanaged resource of some kind that you aren't cleaning up properly. We don't have enough information to know exactly what that resource might be, so all we can do is guess.

My first instinct is database connections, except that restarting that restarting sql server should fix it if that were the case. Next on the list is file handles and threads, so if you do any multithreading work or extra file io that would be something to look at. Remember, in ASP.Net, the using statement (not directive) is your friend.

Answer 3

First, you need to talk to your DBA... they can check the number of open connections, table locks, slow-running queries, etc.

My gut reaction is that you aren't closing your connections somewhere, or your connection pool is too low.

Answer 4

Are you doing regular database maintenance? Rebuilding / defragmenting indexes, recalculating statistics (unless it's set to do this automatically). Check the size of your transaction log, etc.

Answer 5

Hi guys, thanks for the reply. I am working in the same company as the poster. Here I want to add some more details about the problem. Our server, only one box, has SQL Server, IIS, iMail (our mail server). After I had restarted SQL Server, it did not solve the problem. Only restarting Windows Server, it worked. From perfom, IO usage is quite high. Is there any suggestions? Thanks