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.