Home
Trouble shooting
 

Cry about...
SQL Server Troubleshooting

Distribution Agent: Timeout expired

Symptom:

A distribution agent has failed, the full error message text is:

Timeout expired.

Cause:

The query is timing out due to poor network performance or queries take longer than is allowed to complete.

This error has been observed when replicating over a (relatively slow) WAN.

Remedy:

The profile for the replication agent has a ‘query timeout’ value that can be changed to ensure that the queries have sufficient time to complete. A new profile has to be created by copying the existing profile and increasing the ‘query timeout’ value:

  1. Open Enterprise Manager and navigate to ‘Agents’ via SQL Server Groups > SERVERNAME > Replication Monitor

  2. Expand ‘Agents’ and select the agent that is giving timeouts

  3. Select the Publication agent in the right-hand display, right-click and select ‘Agent Profile’

  4. Select ‘Default Agent Profile’ and then click ‘Copy…’

  5. Enter the ‘Name’ and ‘Description’ for the new profile.

  6. Edit the ‘query timeout’ value, the units used are seconds. The default value is 300. Increase it (perhaps double it?) and then click ‘OK’.

  7. Select the new profile and click ‘Apply’.

Contributed by: Alan Wong