none
SQL2012 Availability Groups. Can't drop database on secondary server.

    Question

  • Hello,

    I am currently testing AlwaysOn by creating an Availability Group with a test db in it. The Availability Group has now been dropped as has the database on the primary but I now have a database on the secondary which I can not drop.


    If I run

    DROP DATABASE [AvailabilityGroupDb1]

    Then I get the following error

    Msg 3752, Level 16, State 1, Line 1
    The database 'AvailabilityGroupDb1' is currently joined to an availability group.  Before you can drop the database, you need to remove it from the availatility group.

    Trying to set the database to Offline or Single User to force the drop also fails.


    If I run

    ALTER DATABASE [AvailabilityGroupDb1] SET HADR OFF

    I get the following error.

    Msg 35242, Level 16, State 14, Line 1
    Cannot complete this ALTER DATABASE <database-name> SET HADR operation on database 'AvailabilityGroupDb1'. The database is not joined to an availability group. After the database has joined the availability group, retry the command.

    The Replica_id and Group_Database_id in sys.databases show as null.

    I tried this originally in version:

    Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
        Feb 10 2012 19:39:15
        Copyright (c) Microsoft Corporation
        Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

    and I have then installed the CU's and run this in version:

    Microsoft SQL Server 2012 - 11.0.2332.0 (X64)
        Aug 22 2012 17:05:49
        Copyright (c) Microsoft Corporation
        Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)


    This was orignally an Availability Group with just this database in, the database was removed from the group using ALTER AVAILABILITY GROUP [AG1] REMOVE DATABASE [AvailabilityGroupDb1] on the primary before dropping the database, the database was successfully dropped on the Primary.

    I have included the script which I have been using for reference below.

    I have managed to drop the database following a restart of the SQL service on the secondary at this point the database reappears in a restoring state but can any one help identify why this is happening so that I can adjust the script accordingly?






    --create Availability Group
    
    
    --variables needed
    :setvar Server1 "Srv1"
    :setvar Server2 "Srv2"
    :setvar Location "\\Srv1\Backups"
    --this is the service acount of Server1 to be created on Server2
    :setvar Login1  "domain\user" 
    --this is the service acount of Server2 to be created on Server1
    :setvar Login2 "domain\user"
    :setvar Database1 "AvailabilityGroupDb1"
    :setvar AvailGroupName "AG1"
    :setvar CreateDummyDB "1"
    
    
    
    
    
    
    :connect $(Server1)
    IF (SELECT '$(CreateDummyDB)') ='1'
    BEGIN
    	CREATE DATABASE $(Database1);
    
    	BACKUP DATABASE [$(Database1)] 
        TO DISK = N'$(Location)\$(Database1)_hadr.bak' 
        WITH 
    		  COMPRESSION,  
    		  STATS = 5,
    		  INIT
    
    
    END
    GO
    :Connect $(Server1)
    USE [master];
    IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') IS NULL 
    BEGIN
        CREATE ENDPOINT [Hadr_endpoint] 
    	    AS TCP (LISTENER_PORT = 5022)
    	    FOR DATA_MIRRORING (   
    						  ROLE = ALL, 
    						  ENCRYPTION = REQUIRED ALGORITHM AES
    					   )
    END				
    GO
    
    IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0
    BEGIN
    	ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
    END
    GO
    
    IF (SELECT name from sys.server_principals WHERE name = '$(Login2)') IS NULL
      BEGIN 
        CREATE LOGIN [$(Login2)] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
       END
    GO
    USE [master]
    GO
    IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') IS NOT NULL 
    BEGIN
        GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [$(Login2)]
    END    
    GO
    
    :Connect $(Server2)
    
    USE [master]
    GO
    IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') IS NULL 
    BEGIN
        CREATE ENDPOINT [Hadr_endpoint] 
    	    AS TCP (LISTENER_PORT = 5022)
    	    FOR DATA_MIRRORING (
    					   ROLE = ALL, 
    					   ENCRYPTION = REQUIRED ALGORITHM AES
    					   )
    END				    
    GO
    
    IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0
    BEGIN
    	ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
    END
    GO
    
    use [master]
    GO
    IF (SELECT name from sys.server_principals WHERE name = '$(Login1)') IS NULL
      BEGIN 
        CREATE LOGIN [$(Login1)] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
       END
    GO
    IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') IS NOT NULL 
    BEGIN
        GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [$(Login1)]
    END    
    GO
    
    :Connect $(Server1)
    
    IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
    BEGIN
        ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
    END
    IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
    BEGIN
        ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
    END
    GO
    
    :Connect $(Server2)
    
    IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
    BEGIN
        ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
    END
    IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
    BEGIN
        ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
    END
    GO
    
    
    
    :Connect $(Server1)
    
    USE [master]
    GO
    IF (SELECT  name FROM sys.availability_groups WHERE NAME = '$(AvailGroupName)') IS NULL
    BEGIN
    
    			CREATE AVAILABILITY GROUP [$(AvailGroupName)]
    			WITH (AUTOMATED_BACKUP_PREFERENCE = NONE)
    			FOR DATABASE [$(Database1)]
    			REPLICA ON N'$(Server1)' WITH (
    											ENDPOINT_URL = N'TCP://$(Server1).server.com:5022', 
    											FAILOVER_MODE = MANUAL, 
    											AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, 
    											BACKUP_PRIORITY = 50, 
    											SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)),
    						N'$(Server2)' WITH (
    											ENDPOINT_URL = N'TCP://$(Server2).server.com:5022',
    											FAILOVER_MODE = MANUAL, 
    											AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, 
    											BACKUP_PRIORITY = 50, 
    											SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL));
    
    
    END
    GO
    
    :Connect $(Server2)
    
    IF NOT EXISTS(SELECT name FROM  sys.availability_groups WHERE NAME = '$(AvailGroupName)')
    BEGIN
        ALTER AVAILABILITY GROUP [$(AvailGroupName)] JOIN;
    END    
    GO
    
    
    -- now backup db1 and restore this to server2
    :Connect $(Server1)
    
    IF (SELECT  name FROM  sys.databases WHERE NAME = '$(Database1)') IS NOT NULL
    BEGIN
        IF (SELECT recovery_model_desc FROM sys.databases WHERE name = '$(Database1)') <> 'FULL'
        BEGIN	   
    	   ALTER DATABASE $(Database1) SET RECOVERY FULL;
        END
        
        BACKUP DATABASE [$(Database1)] 
        TO DISK = N'$(Location)\$(Database1)_hadr.bak' 
        WITH  COPY_ONLY, 
    		  COMPRESSION,  
    		  STATS = 5,
    		  INIT
    END		  
    GO
    
    :Connect $(Server2)
    
    	RESTORE DATABASE [$(Database1)] 
    	FROM  DISK = N'$(Location)\$(Database1)_hadr.bak' 
    	WITH	   NORECOVERY,  
    		   STATS = 5,
    		   REPLACE
    
    GO
    
    :Connect $(Server1)
    IF (SELECT  name FROM  sys.databases WHERE NAME = '$(Database1)') IS NOT NULL
    BEGIN
        BACKUP LOG [$(Database1)] 
        TO  DISK = N'$(Location)\$(Database1)_hadr.trn' 
        WITH	   COMPRESSION,  
    	   	   STATS = 5,
    		   INIT
    END	   	   
    GO
    
    :Connect $(Server2)
    
    RESTORE LOG [$(Database1)] 
    FROM  DISK = N'$(Location)\$(Database1)_hadr.trn' 
    WITH	   NORECOVERY,  
    	   STATS = 5
    GO
    
    
    :Connect $(Server2)
    
    	ALTER DATABASE [$(Database1)] 
        SET HADR AVAILABILITY GROUP = [$(AvailGroupName)]
    	WITH ROLLBACK IMMEDIATE;
    GO
    
    
    :connect $(Server1)
    USE [master];
    IF (SELECT '$(CreateDummyDB)') ='1'
    BEGIN
    
    	ALTER AVAILABILITY GROUP [$(AvailGroupName)]
    	REMOVE DATABASE [$(Database1)];
    
    END
    GO
    WAITFOR DELAY '00:00:10'
    
    :connect $(Server1)
    USE [master];
    IF (SELECT '$(CreateDummyDB)') ='1'
    BEGIN
    	DROP DATABASE [$(Database1)]
    END
    GO
    
    
    :connect $(Server2)
    USE [master];
    IF (SELECT '$(CreateDummyDB)') ='1'
    BEGIN
    	WAITFOR DELAY '00:00:10'
    	DROP DATABASE [$(Database1)]
    END
    GO
    


    Wednesday, October 17, 2012 11:03 AM

Answers

  • Hi NG,

    Any progress?

    After have involved someone familiar with this topic, I can give you the suggestions below.

    The following syntax is not necessary and not really valid:

    ALTER DATABASE agdb3
        SET HADR AVAILABILITY GROUP = ag1
           WITH ROLLBACK IMMEDIATE;

    It should be just:    

      ALTER DATABASE agdb3
        SET HADR AVAILABILITY GROUP = ag1

    You could refer to the documentation about the command which clearly shows that ‘WITH ROLLBACK IMMEDIATE’ is not part of this command.

    http://technet.microsoft.com/en-us/library/ff877974(v=sql.110).aspx

    Thanks.

     


    Maggie Luo

    TechNet Community Support

    Friday, November 09, 2012 2:54 AM
    Moderator

All replies

  • Hi NG,

    Before you drop the database, you need to remove it from the availability group.

    We could remove a secondary database from an AlwaysOn availability group by using SQL Server Management Studio, Transact-SQL, or PowerShell in SQL Server 2012. Have you tried other methods?

    Remove a Secondary Database from an Availability Group (SQL Server): http://msdn.microsoft.com/en-us/library/hh231120.aspx.

    TechNet Subscriber Support
    If you are
    TechNet Subscriptionuser and have any feedback on our support quality, please send your feedback here.


    Thanks.


    Maggie Luo

    TechNet Community Support



    Friday, October 19, 2012 9:20 AM
    Moderator
  • Hi NG,

    Any progress?

    When a secondary database is joined to an availability group, the local secondary replica changes the state of that secondary database from RESTORING to ONLINE. If a secondary database is removed from the availability group, it is set back to the RESTORING state by the local secondary replica. This allows you to apply subsequent log backups from the primary database to that secondary database.

    It seems that the secondary database has been removed from the availability group.

    If you have any problem, please feel free to let us know.


    Thanks.


    Maggie Luo

    TechNet Community Support

    Tuesday, October 23, 2012 2:25 AM
    Moderator
  • Hi Maggie,

    Thanks for looking in to this for me. I have tried through SSMS and get the same errors but I have not tried through Powershell.

    When I try to remove the secondary database, with the code below, from the availability group prior to the primary

    :connect $(Server2)
    USE [master];
    IF (SELECT '$(CreateDummyDB)') ='1'
    BEGIN

        ALTER AVAILABILITY GROUP [$(AvailGroupName)]
        REMOVE DATABASE [$(Database1)];

    END
    GO


    I get the following error

    Msg 41190, Level 16, State 8, Line 6
    Availability group 'AG1' failed to process remove-database command.  The local availability replica is not in a state that could process the command.  Verify that the availability group is online and that the local availability replica is the primary replica, then retry the command.

    If I try to do it after the primary then I get the same error message.

    However running it after the Primary despite throwing the error does seem to consistently allow the database to be dropped.

    Tuesday, October 23, 2012 9:52 AM
  • Hi Maggie,

    Yes the secondary has been removed from the Availability Group but the drop database still fails with the error below until the SQL Service is restarted at which point it can be dropped without any problems.

    Msg 3752, Level 16, State 1, Line 1
    The database 'AvailabilityGroupDb1' is currently joined to an availability group.  Before you can drop the database, you need to remove it from the availatility group.

    Tuesday, October 23, 2012 9:53 AM
  • Hi NG,

    Any progress?

    Thank you for your question. 
    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 
    Thank you for your understanding and support.


    Maggie Luo

    TechNet Community Support

    Wednesday, October 24, 2012 8:25 AM
    Moderator
  • Hi NG,

    Any progress?

    After have involved someone familiar with this topic, I can give you the suggestions below.

    The following syntax is not necessary and not really valid:

    ALTER DATABASE agdb3
        SET HADR AVAILABILITY GROUP = ag1
           WITH ROLLBACK IMMEDIATE;

    It should be just:    

      ALTER DATABASE agdb3
        SET HADR AVAILABILITY GROUP = ag1

    You could refer to the documentation about the command which clearly shows that ‘WITH ROLLBACK IMMEDIATE’ is not part of this command.

    http://technet.microsoft.com/en-us/library/ff877974(v=sql.110).aspx

    Thanks.

     


    Maggie Luo

    TechNet Community Support

    Friday, November 09, 2012 2:54 AM
    Moderator