Posted by Erland Sommarskog on 08/15/07 14:02

rcamarda (robert.a.camarda@gmail.com) writes:
> I ran a query that I thought would take an hour, but instead took 14
> hours to run. The consequence was it bogged down our data warehouse
> and the overnight build was adversely impacted.
> Is there a local setting I can set to limit the execution time my
> query will take? I dont want to have a server setting and impact other
> queries, just the one I am running.
> I know there will be people asking about the 14 hour build and what is
> it doing and so forth. I will address that but I also look to these
> situations as a learning opportunity.

In Query Analyzer you can set the Query timeout under Options->Connection.
There is a similar option in Mgmt Studio.

On SQL 2005, combine these with SET XACT_ABORT ON, to make sure that any
open transactions are rolled back. On SQL 2000 this does not work, so you
have to be extra careful. It is possible that the connection option
"Disconnect when query completes" can save you.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]