Error: SQL Server Query Timed Out

Issue

The following error appears:

SQL server query time out

Environment

Connector: SQL Server

Resolution

Use the timing and scope of the timeout to determine which area to troubleshoot first:

• If the timeout occurs during large syncs, affects large tables, or coincides with heavy source-database load, troubleshoot database performance first.
• If the timeout is intermittent, occurs on small tables, or affects multiple connections simultaneously, troubleshoot the network or Proxy Agent first.

Troubleshoot database performance issues

1. Check the server CPU, RAM, and disk I/O for high load.

2. If your database is under heavy load, reduce your connection's sync frequency.

   i. In Fivetran, go to your SQL Server connection page.

   ii. Select the Settings tab.

   iii. Update the value in the Sync Frequency drop-down menu.

3. Work with your database administrator (DBA) to set the following in SQL Server:

   • remote login timeout to 0
   • remote query timeout to 0
   • query wait to 2147483647
   If you use a Hybrid Deployment Agent (HVA) connection, trigger a historical sync after updating these settings. After the sync completes, check the Sync Duration in the Status tab, and set timeout values to a reasonable threshold based on that duration.

4. If query performance is still poor, ask your DBA to rebuild indexes and update statistics.

5. Check for deadlocks or blocking queries in SQL Server and resolve the blocking transaction if possible.

6. If your database load is consistently high, consider upgrading its resources to ensure queries complete within a reasonable time.

Troubleshoot network or Proxy Agent issues

1. Check whether a firewall, proxy, or other network device is interrupting long-running connections.
2. If you connect using a Proxy Agent, verify that the agent is healthy and that its connection to the source database is stable.
3. If you use a Proxy Agent, review the agent logs for connection interruptions or stalled requests when the timeout occurs.

Check whether the issue is resolved

1. In Fivetran, go to your SQL Server connection page.
2. In the top right, click Sync.
3. Wait for the sync to complete.

If the issue persists, contact our support team.

Cause

This issue occurs when a query against SQL Server doesn't return results before the read timeout is reached. This may happen for one of the following reasons:

• The source database is under heavy load.
• A query is slowed by missing indexes, outdated statistics, blocking, or deadlocks.
• A firewall, proxy, or other network device interrupts the connection.
• A Proxy Agent or shared network path is unstable or misconfigured.