Don't panic

 

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


These notes are believed to be correct for SQL Server 7 and may apply to other versions as well.



About the author: is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.