By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,723 Members | 1,270 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,723 IT Pros & Developers. It's quick & easy.

Limit query execution time

P: n/a
Hello,
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.
Thanks in advance.
Rob

Aug 15 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
rcamarda (ro**************@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Aug 15 '07 #2

P: n/a
On Aug 15, 10:02 am, Erland Sommarskog <esq...@sommarskog.sewrote:
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

Aug 15 '07 #3

P: n/a
rcamarda (ro**************@gmail.com) writes:
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?
I guess that it's faster just to change and try, than wait for me to
answer. :-)

But I remember now that there was a weird bug with the query timeout in SSMS 2005. But it appears affects connections to the local server only. See
https://connect.microsoft.com/SQLSer...dbackID=286298

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Aug 15 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.