Dan Stef Dan Stef - 3 months ago 41
PowerShell Question

MS SQL/Powershell: how to kill background process on secondary node in Availability Group

I have an environment with MS-SQL Server 2014 and always-on high availability group configured (on 2-nodes).

I'm writing a Powershell Script which removes the database from the availability group (on the primary server) and then SHOULD drop the database on the secondary Server.

That works most of the time, but not always...

I use this the following command to drop the database on the secondary Server (the db is by this time already removed from the availability group on the primary server and is in state "recovering" on the secondary server):

$SecondaryServerConnection.Databases[$x.Name.ToString()].Drop()


When it fails, i get the error message:


System.Management.Automation.MethodInvocationException: Exception calling "Drop" with "0" argument(s): "Drop failed for Database
'Customer_2'. "
---> Microsoft.SqlServer.Management.Smo.FailedOperationException: Drop failed for Database 'Customer_2'.
---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or
batch. ---> System.Data.SqlClient.SqlException: Cannot drop database "Customer_2" because it is currently in use.


When i check the DB-Server (sp_who2) while the script is running, i see that there is a process for the DB "Customer_2" with Status="background", Command="DB STARTUP" and LastWaitType="REDO_THREAD_PENDING WORK".

As soon as the script fails, the process for "Customer_2" disapears.

I tried to modify my script to kill all processes, but when I do that, I get the error message:
Only user processes can be killed.


If it happens, it happens always on the second Database. In the availability group are several databases (3 - 5).

So, now I have several questions:


  • How do I get rid of that background process within my Powershell script? Is it possible to do that??

  • Why does it work with the first database and not on the second? Does my script have a process on that DB and therefore the process only disappears after the script fails??

  • Or is it a timing issue, after i've dropped the database on the primary server?? I have a 6 seconds
    start-sleep
    after the drop..

  • What are these processes anyway? They are there for all the databases on the secondary server.



I haven't found a reason why it works on some databases and does not on some others.. It might be an issue with the size of the database.. Some are just a few hundred MB, while others are up to 40Gb...

I cant set the database offline or set it to single user mode, because the database is not online on the secondary server. The database is in state "Restoring".

UPDATE:
Something I forgot to mention is that the SPID of the process is normally above 50 . From what I was reading, SPID below 50 are always system processes. Is that correct?

Answer

I found a solution to this issue (but not what the source of that problem was).

I ended up writing a Powershell function that tries with 3 different methods to drop the database

Function Remove-SqlDatabase
{
[CmdletBinding()]
Param(
[string]$Server,
[string]$Database
)

try
{
    $smo = New-SMOconnection -server $Server
    $smo.KillDatabase($Database)
    $smo.Refresh()
    #Write-Host "Successfully dropped $Database on $($smo.name)"
}
catch
{
    try
    {
        $smo.Databases[$Database].Drop()
        #Write-Host "Successfully dropped $Database on $($smo.name)"
    }
    catch
    {
        try
        {
            $null = $smo.ConnectionContext.ExecuteNonQuery("DROP DATABASE $Database")
            #Write-Host "Successfully dropped $Database on $($smo.name)"
        }
        catch 
        { 
            Write-Error "Could not drop database $Database!"
            Write-Error $_
            throw $_
        }
    }
}
}
Comments