question

sp_lock avatar image
sp_lock asked

SQL 2012 Availability Group scipting issue

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 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 (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 (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? Attached is the SQL script that TRIES to do the trick.[link text][1] [1]: /storage/temp/ 437-ag_test.txt
sql-server-2012availability-groups
ag_test.txt (6.6 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.