Query Timeout Setting

SQL Server 2005 is timing out when running loooong queries.

Is there a global place I can extend the timeout for queries? I do not mean in code but just in general use?

Thanks

Craig

Perhaps its better to fix the long running query?

|||

ndinakar:

Perhaps its better to fix the long running query?

I agree completely!

The number of reusable connections is limited. A few users of a single long-running query can bring an entire system down!

Think about driving on a road with ten lanes going your way. Just two slooooow drivers, each in different lanes, can cause traffic jams really fast in busy traffic.

|||

When you have 80 - 160 million records in 2 or more tables we need all options possible, so any answers to the original question?

|||

80-160 mill rows is nothing for SQL Server, if your query is properly optimized and have the right indexes. I work with tables over 500 mill rows to as high as 1.2 bill rows. So, fixing the query will be a better solution in the longer run. If you can show us the query that's timing out we can try to help with that.

Check this article on various ways to set timeout limits:http://vyaskn.tripod.com/watch_your_timeouts.htm

|||

Both the connection object and the command object have a property that deals with maximum times.

For legitimate, long-running queries that cannot be better tuned or designed, just up the time on those properties.

That way, you force your developers (assuming you are developing with test datasets of the appropriate size) to identify long-running queries before they hit production. It gives you an opportunity to improve the sql before the system goes live.

By searching for those property names, you can identify long-running queries (from the developer's point of view) and route them to your sql specialist for tuning.