how can I increase Timeout (or set to 0)?

bgravesbgraves Posts: 19
edited February 24, 2011 4:29AM in SQL Data Compare Previous Versions
I'm using the the ExecuteBlock class to replicate data from one server to the other. How can I set whatever SQLCommand.Timeout that this class is using. Currently, I'm timing out after 2 hours.
· Share on Twitter

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello,

    CommandTimeouts are not configurable in ADO .NET, except in the code. So the ExecuteBlock method has a hard-coded timeout of ten minutes.

    If you need to specify your own commandtimeout, you can write your own block executor and set the commandtimeout property of the SqlCommand to whatever you want.

    There is an example of this on the Red Gate knowledge base. It doesn't show setting the command timeout, but it does show how to run your own execution block. You can then set the commandtimeout property on the SqlCommand object.
    · Share on Twitter
  • bgravesbgraves Posts: 19
    Are you sure? I'm currently using the ExcecuteBlock method (not my own block executor) and my timeout is 2 hours (not 10 minutes). Maybe this is version specific (I'm using version 6)?

    Also, in the past week I've notice the the replication time (when not timing out) has increased significantly. Our set of tables replicated in under 11 minutes last week. Currently, it's taking 20-25 minutes on a regular basis. Is there a reason this is happening (even when no new rows are being added to the table)? This is very difficult to troubleshoot since the code is hidden in the redgate DLLs. Could upgrading the libraries help (currently we're using version 6...not 8)? I'm afraid to upgrade them now if the timeout has been decreased to 10 minutes.
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Last time I checked, it was 10 minutes per transaction. So it may be that the updates for one table are split into a few batches.
    · Share on Twitter
  • bgravesbgraves Posts: 19
    Okay, so if I'm replicating 21 tables, I should have 210 minutes to replicate. Therefore, it must be timing out on of the tables and throwing an exception from the ExecuteBlock method. The more serious issue is the replication time increase since this used to be a lot faster last week. Even when there is a very low number of rows replicated, the time increase is doubled. Is there a way to speed this up again?
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Sorry, without knowing what is going on, I can't recommend anything to increease the performance. I suppose you could try SQL Profiler and see what it says. Maybe some tables need an index or something.
    · Share on Twitter
Sign In or Register to comment.