[ACCEPTED]-Why would a database timeout?-sql-server

It's possible that you've got too many long-running 19 queries. Another very likely cause is that 18 you're not closing the connections as soon 17 as you've finished with them, which releases 16 them to the connection pool.

In general, you 15 should acquire connections as late as possible 14 and release them as early as possible with 13 a using statement, to avoid just this kind of 12 situation. If you don't close the connection 11 explicitly (even in the face of exceptions) you're 10 putting yourself at the mercy of the garbage 9 collector and finalizers to return the connection 8 to the pool.

If you believe you genuinely have many 7 active connections with long-running queries, you 6 should increase the number of connections 5 in the connection pool. If you believe it's 4 the actual query itself timing out (i.e. taking 3 longer than you're allowing) then you should 2 increase the timeout for the command or 1 connection.

the work you are trying to do takes longer 4 than the default time out period. your 3 query is running too slow. it may be this 2 query, another query slowing everyone else 1 down, updates blocking, or a number of reasons.

Likely your statement is taking too long. If 3 it is only happening with multiple users, look 2 for locking that might be freezing up resources. Use 1 "with(nolock)" if possible.

Check how long it takes when you run the 5 same query in query analyzer.

Look at the 4 query plan.

Use Profiler to check if there 3 are multiple connections and time taken 2 for this stored proc in question.

Check the 1 command time out in ADO.NET.

This is the client timeout, so if the database 5 is heavy used (many connections/large queries) and 4 is not able to respond in specified amount 3 of time, the client will eventually throw 2 an exception.

Try incising the query timeout, or 1 look at the Profiler to optimize the query.

Not being able to see your query or data 11 structure we can only offer guesses at what 10 could be causing your time out. In most 9 cases the query is to slow. Increasing the 8 Timeout period for your connection can get 7 around the time out issue but it is usually 6 best to fix your query. The other thing 5 to take a look at is the indexes of the 4 tables your query is hitting against. If 3 you have a lot of joins in your query then 2 your indexes should incorporate the fields 1 you are joining on.

In my experience, it's almost always the 14 query.

Open up the query in Query Analyzer 13 or Management Studio and find the estimated 12 execution plan. My rule of thumb is that 11 an often-executed query should have a cost 10 less than 1.00. If it starts getting past 9 2.00, you'll start seeing problems. (Of 8 course, this was specific to my site where 7 there were several other problems...).

If 6 your query costs are already low, run the 5 Profiler to see what other commands are 4 hitting the server. Then start analyzing 3 those queries to find the expensive ones.

If 2 it tends to happen at certain times of the 1 day, check these:

• Is a backup process running and taking longer than normal?
• Is there a scheduled report running that is blocking (a bad query)?
• Are you overwhelming it with connections? Especially after a few hours of running, you could have a bug with your connection management where you are leaving connections open.

What isolation level are you using?

Are you 3 using a connection pool?

If its ok to do 2 a dirty read, make sure your isolation level 1 is uncommited read.