Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1049
  • Last Modified:

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

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
25112
Asked:
25112
  • 2
  • 2
1 Solution
 
Pawan KumarDatabase ExpertCommented:
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
 
25112Author Commented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
25112Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now