Search code examples
sqlsql-serverdatabase-mirroring

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

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.
Solution

  • 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.