Post reply

Query time out

  • July 17, 2010 at 12:15 pm

    #237793

    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

    #1194566

    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

    The more I learn, the more I know what I do not know
    Blog: Things about Software Architecture, .NET development and T-SQL

    How to Post Data/Code to get the best Help How to Post Performance Problems

  • July 17, 2010 at 1:37 pm

    #1194571

    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

    #1194576

    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

    #1194616

    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

    #1194634

    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

    #1194655

    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

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply