Lesson Learned #370: SQL Server Error Code ‘0’ and SQL state ‘The connection is closed’

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.

A few days ago, we received a service request from a customer who encountered the following error message: "SqlServer error: code = '0', SQL state = '': The connection is closed." The customer informed us that this issue sometimes occurs during a long-running process when the connection remains open but idle for an extended period of time.

This error occurs when the connection between the application and the Azure SQL database is closed due to the proxy connection policy. The proxy server is designed to close connections that have been inactive for an extended period of time, typically after 30 minutes of inactivity. To mitigate this issue, an alternative solution is to use the Redirect connection policy instead of the Proxy connection policy. By using Redirect, the disconnection after 30 minutes of idle time will not occur.

Regarding the error, when the proxy server closes the connection, the application receives this error indicating that the connection has been closed. This error can occur when trying to perform additional operations on the database after the connection has been closed.

If it's necessary to use the Proxy connection policy and changing to Redirect is not an option, one solution is to implement a retry policy.

By incorporating a retry mechanism, the application can automatically reconnect and retry the operation if the connection is closed. This approach allows the application to handle the intermittent nature of the error and gracefully recover from it.

By utilizing a retry policy, the application can improve its resilience in dealing with connection closures and ensure that critical operations are retried when needed. This helps mitigate the impact of the connection closure error and enhances the overall stability and reliability of the application's interactions with the Azure SQL database.

Other alternative, is trying to maintain the connection alive. For this reason, I would like to share the following PowerShell script , as an example, demonstrates how to maintain an active connection and avoid the "The connection is closed" error in Azure SQL Database when using the Proxy Connection Policy and the connection might be in an idle state for more than 30 minutes.

As you could find in the below code, we have function named ExecuteKeepAliveQuery . It creates a command object, sets the SQL query to "SELECT 1", and executes the command against the provided connection that previously was opened by the main thread. If an error occurs during the execution, it will be caught and an appropriate message will be displayed.

# Function to execute the keep-alive query function ExecuteKeepAliveQuery($connection) { try { $command = $connection.CreateCommand() $command.CommandText = "SELECT 1" $command.ExecuteNonQuery() } catch { # Handle any error that occurs while executing the keep-alive query Write-Host "Failed to execute keep-alive query: $_" } }

In the below section, a timer object is created with the specified interval, and an event is registered to execute the ExecuteKeepAliveQuery function at the defined interval. The connection object is passed as a parameter to the function. The timer is then started.

# Register the event to execute the keep-alive query at the specified interval $timer = New-Object System.Timers.Timer $timer.Interval = $keepAliveInterval * 1000 # Convert seconds to milliseconds Register-ObjectEvent -InputObject $timer -EventName Elapsed -Action { ExecuteKeepAliveQuery -connection $connection } $timer.Start()

This is a placeholder section where you can insert your program logic or any other operations you want to perform using the active connection. In this example, a loop is executed, and the current iteration number is written to the output.

# Perform your program logic here # ... for($i=0;$i -le 10000;$i=$i+1) { Write-Output $i }

Finally, when your program logic is completed, the timer is stopped, and the event is unregistered. The timer is also disposed of, and the connection to the Azure SQL Database is closed.

# Stop the timer and unregister the event when your program is finished $timer.Stop() Get-EventSubscriber -Force | Unregister-Event -Force $timer.Dispose() $connection.Close()

By periodically executing the keep-alive query (SELECT 1 in this case) using a timer, you ensure that the connection remains active and prevent it from being closed due to the Proxy Connection Policy in Azure SQL Database. This approach helps avoid the "The connection is closed" error when the connection is in an idle state for more than 30 minutes.

It's important to note that the script provided here is a basic example and may need modifications based on your specific requirements. Additionally, you can customize the error handling and implement retry policies within the ExecuteKeepAliveQuery function to handle any potential failures and enhance the resiliency of your application.

$serverName = "ServerName.database.windows.net" $databaseName = "DBName" $username = "UserName" $password = "Password" $connectionString = "Server=tcp:$serverName,1433;Initial Catalog=$databaseName;Persist Security Info=False;User ID=$username;Password=$password;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;" $keepAliveInterval = 1 # Interval in seconds (e.g., 5 minutes) # Function to execute the keep-alive query function ExecuteKeepAliveQuery($connection) { try { $command = $connection.CreateCommand() $command.CommandText = "SELECT 1" $command.ExecuteNonQuery() } catch { # Handle any error that occurs while executing the keep-alive query Write-Host "Failed to execute keep-alive query: $_" } } $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString) $connection.Open() # Register the event to execute the keep-alive query at the specified interval $timer = New-Object System.Timers.Timer $timer.Interval = $keepAliveInterval * 1000 # Convert seconds to milliseconds Register-ObjectEvent -InputObject $timer -EventName Elapsed -Action { ExecuteKeepAliveQuery -connection $connection } $timer.Start() # Perform your program logic here # ... for($i=0;$i -le 10000;$i=$i+1) { Write-Output $i } # Stop the timer and unregister the event when your program is finished $timer.Stop() Get-EventSubscriber -Force | Unregister-Event -Force $timer.Dispose() $connection.Close()

Leave a Reply Cancel reply