FAQ: Retry Mechanism in SQL Server Snaps

How retries work in SQL Server Snaps?

The retry mechanism in the SQL Server Snaps is designed to handle temporary system problems, such as:

  • Network issues
  • Database connection failures
  • Temporary issues during batch operations (Insert, Merge, Update, Delete)
  • Temporary failures when retrieving table or stored procedure metadata
  • SELECT queries (if retry is enabled for them)

When do retries happen?

SQL Server Snaps use a fixed‑delay retry mechanism, but it is disabled by default. The five main categories of operations where retries occur in SQL Server Snaps are:

  • Connection-related failures: Triggers include connection pool exhaustion, network timeouts/interruptions, and connection drops during execution. These affect all database operations (Insert, Update, Delete, Select, Merge, TableList, StoredProcedure).
  • Batch operation failures (Insert/Update/Delete/Merge): Temporary resource unavailability during batch execution for Snaps such as Insert, Update, Delete, and Merge.
  • SQL Server specific operations: Retries occur on failures of SET IDENTITY_INSERT tablename ON/OFF statements when enableIdentityInsert=true in Insert and Merge Snaps.
  • Metadata retrieval operations (TableList, StoredProcedure): Triggers are schema metadata query failures, stored procedure parameter discovery failures, and system catalog access issues, specifically in the TableList and StoredProcedure Snaps.
  • SELECT query execution: Triggers include query execution timeouts, connection issues during result set processing, and temporary database locks on read operations.

How to enable retries in SQL Server Snaps?

To enable retries, you must set the Number of Retries value to greater than 0 or as required in the SQL Server Snaps.


Number of Retries Configuration

Note:

By default, the retry mechanism is disabled, which means the Snap will not retry unless you explicitly configure the Number of Retries value to more than 0.

  • Number of Retries: Default value is 0 (The range is 0–100)
  • Retry Interval: Default value is 1 second (The range is 1–3600 seconds)

What errors will not be retried?

The system does not retry permanent data errors, such as:

  • Duplicate key errors
  • Constraint violations
  • Invalid data
  • Other data-related mistakes

Retries are meant to solve temporary technical problems, not data issues. Permanent data errors will not be retried because such errors require correcting the data.

What are the operations that do not use retries in the SQL Server Snaps?

  • Bulk load operation: This operation uses an external BCP (Bulk Copy Program) command that does not support retry wrapping.
  • Any operation with a retry value of 0: When this setting is set to its default value of 0, any operation is executed directly without using the retry framework.