Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Query Timeout Expand / Collapse
Author
Message
Posted Friday, March 18, 2011 9:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 17, 2014 1:54 PM
Points: 157, Visits: 221
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..

Post #1080497
Posted Friday, March 18, 2011 10:18 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 7:16 AM
Points: 489, Visits: 1,265
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
Post #1080516
Posted Friday, March 18, 2011 10:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 17, 2014 1:54 PM
Points: 157, Visits: 221
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
Post #1080530
Posted Friday, March 18, 2011 10:50 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 7:16 AM
Points: 489, Visits: 1,265
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
Post #1080539
Posted Friday, March 18, 2011 10:54 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 7:16 AM
Points: 489, Visits: 1,265
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
Post #1080547
Posted Friday, March 18, 2011 12:12 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 17, 2014 1:54 PM
Points: 157, Visits: 221
Great ... thanks!
Post #1080617
Posted Saturday, March 26, 2011 2:57 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 1, 2013 12:16 AM
Points: 278, Visits: 82
hi all

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


please told me


Vimal Kumar
9999797952
Post #1084427
Posted Saturday, March 26, 2011 9:12 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 7:16 AM
Points: 489, Visits: 1,265
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
Post #1084445
Posted Sunday, March 27, 2011 3:13 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 1, 2013 12:16 AM
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
Post #1084548
Posted Sunday, March 27, 2011 3:20 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 1, 2013 12:16 AM
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
Post #1084550
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse