Set database to single user mode for database involved in database mirroring session or an availability group

Question

I'm trying to set a database to single user mode that is currently in an availability group or in a mirroring session. But i'm getting the following error:

The operation cannot be performed on database "DATABASE_NAME" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.ALTER DATABASE statement failed.

Why do you want to change a database that is being mirrored or in an availability group to single user? — Thom A, Aug 13 '20 at 09:28
To prevent any external alterations to the database while scripts to upgrade it are being run — Aditya Nair, Aug 13 '20 at 09:42

score 3 · Accepted Answer · answered Aug 13 '20 at 09:56

3

You need to remove the database from the availability group, you can use the following command.

ALTER DATABASE DBName SET HADR OFF;  
GO

Once you have removed the database from the AlwaysOn AG, then you can put the database in single-user mode.

answered Aug 13 '20 at 09:56

M.Ali

67,945
13
101
127

3

Suppose if i do that...and alter the database...then set HADR back ON, then will the change be reflected in the secondary server database? – Aditya Nair Aug 13 '20 at 10:38
Too risky. No other option? I am manage a bank server. I can not take this RISK. – Meyssam Toluie Apr 03 '22 at 08:40

Set database to single user mode for database involved in database mirroring session or an availability group

1 Answers1