SQL server timeout issue

Question

Possible Duplicate:
SQL server timeout

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

Are you using SQL 2000/2005/2008?

Did this issue start happening after you added the .NET code?

One thing that threw me when I first started working with ASP.NET and SQL Server was to make sure I closed and disposed of any datareaders that I was using.

Answer 2

The main thing to do is to make sure you close and dispose of connections in ADO.NET. One possibility for your problems might be you are not diposing of connections after using them. Unlike in Classic ASP you should open your connection as late as possible and close it as soon as possible. The best way to do this is use the Using syntax in C#. For example:

using (SqlConnection conn = new SqlConnection(Settings.ConnectionString))
{
    using (SqlCommand cmd = new SqlCommand("dbo.UpdateHits", conn))
    {
     cmd.CommandType = CommandType.StoredProcedure;
     cmd.Parameters.Add(new SqlParameter("@PageID", SqlDbType.Int)).Value = 1;

     conn.Open();
     cmd.ExecuteNonQuery();

    }
}

The Using command ensures the connection is closed and disposed of, even if an exception is thrown. Same for the command object.

Answer 3

Are you using connection pooling in your connection string?

See this answer if you are not. You need to use connection pooling because even when you close a connection a port is still in use for 4 minutes, and you could be running out of ports which is why you are seeing this at peak times.

This is if you error is a transport level error.