Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query Timeout


Query Timeout

Author
Message
SQL33
SQL33
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 225
Good morning all.. everything I read seems to indicate that query timeouts when set to "0" means infinite... no timeout. Yet, when I query a table the query runs for about 30 seconds then reports that it has reached the timout threshold. When I check the timout settings in query properties it is set to 0. When I set it to the max allowed (32000 in SQL 2000) the timeout is greatly longer, but still eventually times out. I want it at infinite .. how can I implement this setting for query analyzer?

Thanks..
Jim Murphy
Jim Murphy
SSC-Addicted
SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)

Group: General Forum Members
Points: 497 Visits: 1265
My bet is that the timeout is from a .NET or client application. The default .NET connection timeout is 30 seconds. You can test this by running the same query from Query Analyzer and watching it spin for longer than 30 seconds. That will verify what you are seeing.

You can set the timeout in the .NET 'Command' object. cmd.Timeout = x is the syntax.

Jim

Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
SQL33
SQL33
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 225
There is no .net or any other app being used against the database yet.. I only have the tables right now and am using SQL2000 query analyzer or SQLK25 query analyzer to query the table and getting the timeout with them and when using enterprise manager to query.

[Jim].[dba].[Murphy] (3/18/2011)
My bet is that the timeout is from a .NET or client application. The default .NET connection timeout is 30 seconds. You can test this by running the same query from Query Analyzer and watching it spin for longer than 30 seconds. That will verify what you are seeing.

You can set the timeout in the .NET 'Command' object. cmd.Timeout = x is the syntax.

Jim

Jim Murphy
Jim Murphy
SSC-Addicted
SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)

Group: General Forum Members
Points: 497 Visits: 1265
Then the server settings you are configuring are the right ones. Setting the timeout in SQL Server to -1 will prevent timeouts.

Run:
sp_configure

...and look at the time out settings there. Take not as to if the timeout settings is active for the run_value column. If not, then you need to do a...
RECONFIGURE

...to activate the changed setting.

It's possible that you are changing the timeout setting, but it won't take effect until a RECONFIGURE is performed.

Also, there are different timeout settings; remote login timout (s), remote query timeout (s), etc. Is this a remote query or are you connecting to your local SQL Server instance?

Jim

Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
Jim Murphy
Jim Murphy
SSC-Addicted
SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)

Group: General Forum Members
Points: 497 Visits: 1265
Oh, the 'query wait (s)' property is an Advanced Option. So you can turn on advanced options by running:

sp_configure 'show advanced options', 1
RECONFIGURE


... then you can see all of the options.

Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
SQL33
SQL33
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 225
Great ... thanks!
mca.vimal
mca.vimal
SSC Veteran
SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)

Group: General Forum Members
Points: 278 Visits: 82
hi all

i run this query twise is there any Draback of this......


please told me

Vimal Kumar
9999797952
Jim Murphy
Jim Murphy
SSC-Addicted
SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)

Group: General Forum Members
Points: 497 Visits: 1265
No drawbacks. sp_configure With no parameters, just shows you the existing settings in SQL Server and if it is active or not (Runnable column).

Passing parameters tp sp_configure changes the settings, well, sort of. The settings are not active until reconfigure is run. You can run both of these as much as you want and it shouldn't be a problem.

The only side effect, is not in running the statements to perform the reconfiguration over and over. But changing a value is changing the way SQL Server operates, and this could be a problem if you change to settings which are not good in your environment. Therefore, you should always test on another system before reconfiguring settings. Also, it is wise to research each setting you intend to change and don't just try them, especially on a production system.

Jim

Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
mca.vimal
mca.vimal
SSC Veteran
SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)

Group: General Forum Members
Points: 278 Visits: 82
dear

i am run
sp_configure 'show advanced options', 1
RECONFIGURE

twice in my SQL 2005 Server. if is there any problem will be occur in my Database ...

Vimal Kumar
9999797952
mca.vimal
mca.vimal
SSC Veteran
SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)

Group: General Forum Members
Points: 278 Visits: 82
minimum maximum config_value run_value
0 1 0 0
-2147483648 2147483647 0 0
-2147483648 2147483647 0 0
-2147483648 2147483647 0 0
-2147483648 2147483647 0 0
0 1 1 1
0 1 0 0
0 1 0 0
0 86400 0 0
0 1 0 0
0 1 0 0
0 32767 5 5
0 1 0 0
-1 2147483647 -1 -1
0 1 0 0
0 2147483647 1033 1033
0 9999 0 0
0 1 1 1
0 1 0 0
0 100 0 0
0 32767 100 100
0 32767 0 0
0 32767 100 100
0 32767 0 0
704 2147483647 0 0
0 2 0 0
0 1 0 0
5000 2147483647 0 0
0 64 0 0
0 256 4 4
16 2147483647 2147483647 2147483647
0 2147483647 65536 65536
128 32767 0 0
0 365 0 0
512 2147483647 1024 1024
0 2147483647 0 0
0 1 1 1
512 32767 4096 4096
0 1 0 0
0 2147483647 0 0
1 3600 60 60
0 1 0 0
0 1 0 0
0 2147483647 0 0
-1 2147483647 -1 -1
0 32767 0 0
0 1 1 1
0 1 0 0
0 2147483647 20 20
0 1 0 0
0 2147483647 600 600
0 1 0 0
0 1 0 0
0 1 1 1
0 1 0 0
0 1 1 1
0 1 1 1
0 1 0 0
0 1 0 0
1753 9999 2049 2049
0 32767 0 0
0 32767 0 0
0 1 0 0
0 1 0 0

now i run sp_configure then i got these values please told me how can i got my previous .

Vimal Kumar
9999797952
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search