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:
- Open Enterprise Manager and navigate to ‘Agents’ via SQL Server Groups > SERVERNAME > Replication Monitor
- Expand ‘Agents’ and select the agent that is giving timeouts
- Select the Publication agent in the right-hand display, right-click and select ‘Agent Profile’
- Select ‘Default Agent Profile’ and then click ‘Copy…’
- Enter the ‘Name’ and ‘Description’ for the new profile.
- Edit the ‘query timeout’ value, the units used are seconds. The default value is 300. Increase it (perhaps double it?) and then click ‘OK’.
- 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: Brian Cryer 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.