July 17, 2010 at 12:15 pm
Hi ,
We have a data warehouse in which there are jobs which moves the data from publisher to warehouse and to distribution box, now last week we had one production issue in that job didnt fail but missed the one complete step due to "Query time out" in which SQL did not raise any error for "Query Time out" and job succeded without performing one complete step so the data published was not proper to downstream , now we have worked out some work around to handle this situation , but the problem is how to test this with the jobs , i mean can any one tell us any time out configuration which we can use on server/database to minimize or maximize query execution and thats how we can achive that error of query time out.
So the question here is how can we achive "Query time out" error in application keeping some settings on db server?
This is very critical , any pointer/guidence would be highly appreciable 🙂
Thanks,
-Mithun
July 17, 2010 at 12:43 pm
Hi
What do you mean with "job"? Do you use SQL Agent jobs? What do they execute? If you realized the data publication over some kind of client application, you have to set the query timeout in the clients data provider (like ADO.NET).
Greets
Flo
July 17, 2010 at 1:37 pm
there are different time outs:
- connection time out (failure to open connection or receive handshake)
- command time out (failure to receive response from open connection)
- lock time out (failure to acquire a lock - usually infinity)
in T-SQL you can:
waitfor delay '00:00:32'
in this case waiting 2 seconds longer than 30 seconds to trigger a command time out
to test a connection time out you could point it to a un-listened to port (although that may fail fast)
or to a port that connects but does nothing (any http port will do that)
for lock time out you need two connections (afaik)
use one to hold an exclusive lock and the other one to request a lock either on a table or an applock
July 17, 2010 at 1:59 pm
Not totally sure if I get your question but "Query Time Out" is a server level property. Select your target server, right click, Properties, Connections, check Query time-out property. 0=Unlimited
On the other hand, if this is - as it appears to be - a multi-step batch job be sure each step of the job is properly set in case of a failure. Described behavor can be seen if the offending step of the job was set to "continue with next step" in case of failure - most probably set that way in both failure or success.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 18, 2010 at 3:32 am
Thanks for the reply,
This is not a actually a SQL job but its just similar to that , at scheduled time bunch of sps will start executing pulling data from one server to another server, will perform some operatins and then continue dumping that data on dist server.
Now here i am talking about "Query time out" not the connection time out, lock time out, becoz the other time outs will raise the error and job will fail but with "Query time out" SQL is not raising the error, simply printing the message and contuniuing doing the next step.
So wanted to know if i set query wait property of server to some 5 seconds , would the query will give message "Query time out" if query runs more then 5 seconds and to apply this settings sql service restart is required?
i mean in one of the sp i can put some heavy query which will surly take more then 5 seconds and then sql should give the message "Query time out" and then we can see our work around to handle the query time out is working or not, would it be fine , or any other approach can help in testing the "Query time out" scenario?
I hope now i m bit more clear 🙂
Thanks,
-Mithun
July 18, 2010 at 10:22 am
are you using this approach?
exec [server].[database].[schema].[procedure]
The text of the query time out error might be useful also
I also discovered:
- sql server remote query time out
- linked server query time out
- linked server connection time out
right now I am confused as to why there seems to be one timeout for queries against a linked server
and another timeout for executing procedures on a linked server
I don't really know 🙁
July 18, 2010 at 12:58 pm
mithun.gite-1086228 (7/18/2010)
Thanks for the reply,This is not a actually a SQL job but its just similar to that , at scheduled time bunch of sps will start executing pulling data from one server to another server, will perform some operatins and then continue dumping that data on dist server.
Now here i am talking about "Query time out" not the connection time out, lock time out, becoz the other time outs will raise the error and job will fail but with "Query time out" SQL is not raising the error, simply printing the message and contuniuing doing the next step.
So wanted to know if i set query wait property of server to some 5 seconds , would the query will give message "Query time out" if query runs more then 5 seconds and to apply this settings sql service restart is required?
i mean in one of the sp i can put some heavy query which will surly take more then 5 seconds and then sql should give the message "Query time out" and then we can see our work around to handle the query time out is working or not, would it be fine , or any other approach can help in testing the "Query time out" scenario?
I hope now i m bit more clear 🙂
Thanks,
-Mithun
You might be able to simulate a time out by starting a transaction and modifying a couple of rows in the table. Don't forget to do a rollback when you're done testing.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply