?
Solved

SQL 2012: AG: drop database on secondary error: "ALTER DATABASE is not permitted while a database is in the Restoring state."

Posted on 2016-11-08
4
Medium Priority
?
621 Views
Last Modified: 2016-11-21
when I do below process, I get error: ALTER DATABASE is not permitted while a database is in the Restoring state.
I run the process again and it seems to work OK, most of the time.. is there a way to foolproof this?
~~~
--PRIMARY NODE
USE MASTER

IF
(
SELECT COUNT(*) FROM sys.availability_databases_cluster WHERE database_name = 'database_one'
)
=1

ALTER AVAILABILITY GROUP [POS1AG] REMOVE DATABASE [database_one];
GO


 

--SECONDDARY NODE
GO
  WAITFOR DELAY '00:01'
/* WAIT ONE MINUTE for sync */
GO
IF  EXISTS
(SELECT name FROM master.sys.databases WHERE name = 'database_one')
BEGIN
        ALTER DATABASE database_one  SET SINGLE_USER WITH  ROLLBACK IMMEDIATE
        DROP  DATABASE database_one  
END
0
Comment
Question by:25112
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 30

Expert Comment

by:Pawan Kumar
ID: 41878887
Try. this...

IF  EXISTS 
(SELECT name FROM master.sys.databases WHERE name = 'database_one')
BEGIN
        USE master


        ALTER DATABASE database_one  SET SINGLE_USER WITH  ROLLBACK IMMEDIATE
        DROP  DATABASE database_one  
END

Open in new window

0
 
LVL 5

Author Comment

by:25112
ID: 41879065
hallo- I see you avoid the WAITFOR and added the USE line.. do you think it will help the time lapse between when we remove database from primary:
ALTER AVAILABILITY GROUP [POS1AG] REMOVE DATABASE

and the time the databases are ready to be dropped in secondary?
0
 
LVL 30

Accepted Solution

by:
Pawan Kumar earned 2000 total points
ID: 41879868
Is this the primary or secondary ? In this case it looks like it is not yet removed from  the availability group.

Try increasing the time to 2 minutes and then lets see.
0
 
LVL 5

Author Comment

by:25112
ID: 41880718
I tried twice with 2 minutes.. one time it worked, other time it still gave the error..

can this be done in a while loop, so that as soon as the database is freed up to be dropped it can be? is it possible?
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question