PRB: Replication: Distribution Task Timeout Expired

ID: Q191384

The information in this article applies to:

Microsoft SQL Server version 6.5

SYMPTOMS

The distribution task reports the following error:

S1T00 [Microsoft][ODBC SQL driver] timeout expired.

ODBC has timed out while making a large modification to the subscribing table. There may be many large jobs in the distribution database awaiting distribution, or the subscriber server may be very busy.

CAUSE

Distribution task timeouts usually occur when something has prevented activity from being performed on the distribution task for the duration of the timeout period, specified by the -q parameter. Some of the most common causes of this are:

Blocking at the subscriber.

Insufficient network throughput.

Batching.

The workaround for each of these causes is described in the WORKAROUND section of this article.

WORKAROUND

To work around this problem, try the following:

Blocking at the Subscriber

Run the following statements on the subscribing server:


   sp_who2
   select * from sysprocesses
   sp_lock

This may reveal that another client process was holding locks and blocking the distribution task. Investigate the client process that is holding locks longer than necessary.

Insufficient Network Throughput

While running the distributed task over slow lines (for example, a 56 Kbps frame relay or a 28.8 Kbps modem), the task may time out with the initial default setting of 30 seconds for the -q parameter. See Books Online under "Replication Task Scheduling" for a detailed description of this parameter.

In this case, set the -q value higher. The numeric value after the -q is the number of seconds for the timeout setting (for example, -q120 sets the timeout at two minutes). You can add this switch to the distribution task command line. You can find this command line by double-clicking the distribution task in the task list. You will know that you are adding it to the correct location if you see other switches, such as -b, -c, -t, and -i.

NOTE: Setting -q0 sets the timeout to infinite.

Batching

If the timeout value is set to be considerably higher (for example, about 120 or 240 seconds) and you still experience this timeout error, lower batch sizes for the distribution task may help alleviate the situation. Reduce the value of the -b and -c parameters to have a smaller batch size, thereby reducing the possibility of delays and timeouts.

Additional query words: repl dist disttask distask batchsize batchsizes time out
message

Keywords : SSrvRep
Version : WINNT:6.5
Platform : winnt
Issue type : kbprb