Restore-DbaDbSnapshot fails due to deadlock

[yagudron]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

WARNING: [14:35:53][Restore-DbaDbSnapshot] Failiure attempting to restore [Redacted] on [DESKTOP-Redacted] | Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. RESTORE DATABASE is terminating abnormally.

Steps to Reproduce

param (
    [parameter(Mandatory = $true)]
    [string] $File
)
$xml = [xml](Get-Content -Path $File)
$user = $xml.package.config.user
$password = $xml.package.config.password
$instance = $xml.package.config.server
[array]$databases = $xml.package.config.databases.database   

if (!$databases.count -gt 0) {
    Write-Host "No databases to restore!"
    exit 0
}

if ( ! ([string]::IsNullOrEmpty($user)) -and ! ([string]::IsNullOrEmpty($password)) ) {
    Write-Host " =========================================`n Set credetials for Restore-DbaDbSnapshot! "
    $pass = ConvertTo-SecureString -String $password -AsPlainText -Force
    $credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $user, $pass
}

Write-Host "Databases to restore: ${databases}"

foreach ($database in $databases) {
    $snapshot = "${database}_snapshot"
    Write-Host "Restoring the ${database} snapshot..."
    
    if ($credential) {
        Restore-DbaDbSnapshot -SqlInstance $instance -Database $database -Snapshot $snapshot -SqlCredential $credential -Force
    }
    else {
        Restore-DbaDbSnapshot -SqlInstance $instance -Database $database -Snapshot $snapshot -Force
    }

    Write-Host 'Snapshot restored'
}

Please confirm that you are running the most recent version of dbatools

2.1.4

Other details or mentions

Hello,

If you look at the attached script in Steps to Reproduce I'm running a simple script to restore several databases to a snapshot based on the provided XML configuration file. I sometimes run it numerous times a day when switching branches on my machine. Occasionally the aforementioned error happens, and one of the databases gets stuck in the restoring state, and has to be re-created/restored from the full backup. I can't find any prerequisites or patterns in when it happens. Looking at the server logs, no other processes are interfering with it. I'm not sure if the issue is really in the dbatools, and would appreciate any advice.

What PowerShell host was used when producing this error

Windows PowerShell (powershell.exe)

PowerShell Host Version

Name Value

---

PSVersion 5.1.22621.2506
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.22621.2506
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

SQL Server Edition and Build number

Microsoft SQL Server 2019 (RTM-GDR) (KB5029377) - 15.0.2104.1 (X64) Aug 16 2023 00:09:21 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 22631: ) (Hypervisor)

.NET Framework Version

.NET Framework 4.8.9181.0

[niphlod]

does it happen every time or once in a while ?

[yagudron]

Once per ~5 runs, sometimes less frequently.

[niphlod]

then it's something else with sysadmin privileges doing things on the database, with -Force we TRY to kill sessions, but there's nothing else we can do. You should put Restore-DbaDbSnapshot in a loop if you want to solve the issue.

[wsmelton]

You might include pulling the active sessions on the database just before you run the Restore command in your script. That will at least give you details on who is in the database.