?
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
?
919 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
  • 2
  • 2
4 Comments
 
LVL 38

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 38

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
When you discover the power of the R programming language, you are going to wonder how you ever lived without it! Learn why the language merits a place in your programming arsenal.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

621 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