25112
asked on
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
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
)
=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
ASKER
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?
ALTER AVAILABILITY GROUP [POS1AG] REMOVE DATABASE
and the time the databases are ready to be dropped in secondary?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
Open in new window