Use-DbRetry.ps1

<#

.SYNOPSIS
Retry SQL operations.

.DESCRIPTION
Retry SQL operations. These are only in limited scenarios:
* SQL Server deadlocks and timeouts.
* SQL Server policy evaluation errors (caused by race conditions).

.PARAMETER ScriptBlock
The script you want to execute. It's best to keep this as short as possible, and don't modify variables outside of the scriptblock's scope as they may not be preserved.

.PARAMETER Tries
The maximum number of tries to attempt. This defaults to 3, meaning one attempt plus two retries may be made.

.INPUTS
A scriptblock.

.OUTPUTS
Anything output by the scriptblock. But failure information is also written to the Verbose stream.

.EXAMPLE
$serverInstance = ".\SQL2016"
New-DbConnection $serverInstance master | New-DbCommand "If Object_Id('dbo.Moo', 'U') Is Not Null Drop Table dbo.Moo; Create Table dbo.Moo (A Int Identity (1, 1) Primary Key, B Nvarchar(Max));" | Get-DbData
$dbData = New-DbConnection $serverInstance master | New-DbCommand "Select * From dbo.Moo" | Enter-DbTransaction -PassThru | Get-DbData -OutputAs DataTables
$dbData.Alter(@{ A = 1; B = "B" })
try {
    $dbData2 = New-DbConnection $serverInstance master | New-DbCommand "Select * From dbo.Moo" -CommandTimeout 2 | %{
        Use-DbRetry { Get-DbData $_ } -Verbose
    }
} catch {
    "Exception was caught: $_"
}
Exit-DbTransaction $dbData -Rollback

This drops and recreates a dbo.Moo table (no output), begins a transaction and then upserts a record (returning 1 for 1 record modified).

It then starts a second connection with a short timeout and attempts to select data from the table again. With verbose output this shows a series of timeouts and retry attempts, before throwing an exception which we catch (outputs exception text).

The transaction is then rolled back (no output).

#>


function Use-DbRetry {
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $true)]
        [scriptblock] $Script,
        [int] $Tries = 3
    )

    $try = 1

    while ($try -le $Tries) {
        try {
            $ErrorActionPreference = "Stop"
            . $Script
            break
        } catch {
            if (Test-Error -Type System.Data.SqlClient.SqlException) {
                if (Test-Error @{ Number = 1205 }) {
                    Write-Verbose "Caught SQL deadlock. Try $try."
                    $try++
                } elseif (Test-Error @{ Number = -2 }) {
                    Write-Verbose "Caught SQL timeout. Try $try."
                    $try++
                } else {
                    Write-Verbose "Caught unknown SQL error: $_"
                    throw
                }
            } elseif (Test-Error Microsoft.SqlServer.Management.Dmf.PolicyEvaluationException) {
                Write-Verbose "Caught SQL policy evaluation error. Retry $try."
                $try++
            } else {
                Write-Verbose "Caught unknown non-SQL error: $_"
                throw
            }

            if ($try -gt $Tries) {
                throw
            } else {
                Start-Sleep -Milliseconds (Get-Random 5000) # Somewhere up to 5 seconds
            }
        } 
    }
}