SQL server Timeout – only happens very occasionally

Question

Hello,

I have a web application that occasionally will throw this error….

Exception message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

When I does I am unable to connect to SQL server, even through management studio, it’s says the server timed out and cannot connect.

As soon as I reset iis, it comes back instantly. So this obviously means it’s something in my code that’s causing this. I have an MVC site that uses Linq to SQL and SQL cache dependency with service broker enabled.

I have used the using statement thoroughly throughout the code, so im sure it’s not leaky connections. Reading through the server logs makes things more confusing as there are so many information and warning events, im not a sys admin so it’s hard to know what’s going on.

It begins with me getting an ASP.net 4.xxxxx Event ID 1309 Exception message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. error

I think I got round it last time on my previous server by restating IIS when ever this error popped up, this is something I don’t want to resort to on this new server.

So my question is, what steps can I take to try and reduce but ideally eliminate this timeout error ?

Any help is most appreciated

Truegilly

Answer 1

This type of problem usually boils down to one of two things. Either you are not disposing of your connections properly or you have a number of heavy queries that are completely taking over your database server.

If you don't dispose of the connections properly then IIS will happily keep them around until garbage collection runs and/or they timeout. If you have a very lightly trafficked site then you won't see the problem.

However, once traffic reaches the point where the number of connections hanging around exceeds the number of times you are trying to make a connection to the database server.. well, you are going to start seeing one of a couple possible errors. Timeout expired is one. The exact error is going to depend on which part is falling over: the connection pool, the sql server itself, etc.

The fix you commented on is a temporary solution. As your traffic continues to increase (good problem to have) then other areas of your code are going to cause the issue to come up again.