Lesson Learned #436: Implementing Retry Mechanism with sqlcmd in Bash (Linux)
Published Sep 27 2023 11:41 AM 2,299 Views


Efficiently managing temporary failures and timeouts is crucial in production environments when connecting to databases. In this article, we’ll explore how to implement a retry mechanism with sqlcmd in a Bash script, dynamically increasing timeouts with each failed attempt.


Problem Statement:

Operations can fail due to network issues, overloaded servers, or other temporary problems when interacting with databases. Implementing a retry mechanism helps address these temporary issues without manual intervention.


Solution Overview:

Below is a Bash script utilizing sqlcmd to interact with an SQL Server database. The script attempts to execute an SQL command and, upon failure, retries the operation up to a maximum number of times, incrementing timeouts with each attempt.




# Variable declaration
LOGIN_TIMEOUT=15  # Timeout for login, in seconds
QUERY_TIMEOUT=30  # Timeout for query, in seconds

# Main loop for retry mechanism
while [ $COUNT -lt $MAX_RETRIES ]; do
  # Execute sqlcmd and capture the return value
  # Get current date and time
  timestamp=$(date +"%Y-%m-%d %H:%M:%S")

  # Check the return value
  if [ $RETVAL -eq 0 ]; then
    message="$timestamp - sqlcmd executed successfully with login timeout $LOGIN_TIMEOUT and query timeout $QUERY_TIMEOUT!"
    echo $message
    echo $message >> $LOG_FILE
    message="$timestamp - Error with sqlcmd, retrying with increased timeouts..."
    echo $message
    echo $message >> $LOG_FILE
    # Increase timeouts by fixed amounts

    # Wait for 5 seconds before retrying; adjust as needed
    sleep 5

# Get current date and time
timestamp=$(date +"%Y-%m-%d %H:%M:%S")

# Check if the maximum number of attempts was reached
if [ $COUNT -eq $MAX_RETRIES ]; then
  message="$timestamp - Failed after $MAX_RETRIES attempts with login timeout $LOGIN_TIMEOUT and query timeout $QUERY_TIMEOUT."
  echo $message
  echo $message >> $LOG_FILE





Script Explanation:

  • Variable Declaration: The script begins by declaring variables like the maximum number of retries, counter, server, database, user, password, input file, and timeouts.

  • Main Loop: A while loop serves as the main retry mechanism. The script runs sqlcmd and captures its return value.

  • Return Value Check: If sqlcmd executes successfully (return value 0), the script prints a success message and terminates. Otherwise, it prints an error message, increments timeouts and the retry counter, and sleeps for a while before retrying.

  • Timeout Increment: On each failed retry, login and query timeouts are increased by 15 and 30 seconds respectively, providing more time for subsequent operations.



This Bash script is a valuable tool for managing database operations that might face temporary issues. With a retry mechanism and dynamically adjusted timeouts, you can enhance the robustness and reliability of your database interactions in unstable or high-load environments. We hope this article provided insightful information on effectively implementing a retry mechanism with sqlcmd in Bash. For optimal results, adjust variable values and timeouts to cater to your specific needs and production environment.


Version history
Last update:
‎Sep 27 2023 11:49 AM
Updated by: