Posted by rcamarda on 08/15/07 14:09

On Aug 15, 10:02 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
> rcamarda (robert.a.cama...@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, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thanks for the quick and flame free reply Erland. I did fail to
mention the version of SQL I am using: SQL 2005.
I found in Query > Query Options > Execution > General > Execution
Time Out (currently set to 0 seconds).
I'm guessing that if I set this to 600, my query would terminate at 10
minutes if it didn't complete?
rob

[Back to original message]