Post reply

Drop database on secondary node after removed from Availability Group on primary node

  • October 29, 2015 at 5:07 am

    #306125

    Hi all,

    I have an environment with MS-SQL Server 2014 and always-on 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...

    When it fails I get the error message:

    Cannot drop database "Customer_2" because it is currently in use.

    When i check the secondary 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, this process for "Customer_2" disapears.

    This happens always only on the second database in the availability group.

    Can someone explain to me, what this process does?

    Why is the process still there, even after I removed the database from the Availability Group on the primary node.

    If I remove the database from the availability group manually, the "background" process on the secondary node for that database disappears..

    Thanks for your help.

    Dan

    PS: I asked a similar question on stackoverflow, but there with more questions about Powershell:

    http://stackoverflow.com/questions/33348544/ms-sql-powershell-how-to-kill-background-process-on-secondary-node-in-availabil

  • October 29, 2015 at 8:19 am

    #1836390

    even after you remove the database from the group the redo thread on the secondary may well still be applying transactions, this would be required to ensure the database state is consistent, it may be that the database will be recovered after the group eviction

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • October 30, 2015 at 12:48 am

    #1836578

    Ok, thanks Perry!

    Do you know a way, how I could check if there is still a connection open? Either in T-SQL or Powershell 🙂

    I tried now in a while loop the following (Powershell):

    $SecondaryServerConnection.Databases[$x.Name.ToString()].ActiveConnections -ne 0 -or $SecondaryServerConnection.GetActiveDBConnectionCount($x.Name.ToString()) -ne 0)

    If any of them finds an open connection it refreshes (recreates) the connection again and waits one second.. Do you think that's correct?

    I'm going to try that on the next production run.

    Thanks.

  • October 30, 2015 at 8:08 am

    #1836697

    It still happens the same...

    I'm out of ideas now.....

  • October 30, 2015 at 9:01 am

    #1836722

    is this a synchronous or asynchronous replica

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply