alwaysON secondary database in restricted mode

  • U J

    SSCrazy

    Points: 2420

    Secondary replica database(setup in async mode) of AlwaysON went in "restricted mode" during weekly reindex operation.

    So I have tried below steps

    1) Executed following statement on the same secondary replica database where the issue exists

    alter database <DBNAME> set multi_user with rollback immediate

    but it failed with the error saying "The operation cannot be performed on database "dbname" 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."

    2) Primary database is multi_user but still tried following command on primary replia database(thinking it will replicate)

    alter database <DBNAME> set multi_user

    but no luck. The secondary alwaysON database shows (synchronizing) as the alwaysON is set in async mode but the command doesn't replicate across secondary

    so we are left with the only option to re-setup alwaysON but I want to avoid it as database size is huge..

    any other options or am I missing anything?

    Thanks in advance.

  • Perry Whittle

    SSC Guru

    Points: 233859

    Tush (6/27/2015)


    Secondary replica database(setup in async mode) of AlwaysON went in "restricted mode" during weekly reindex operation.

    So I have tried below steps

    1) Executed following statement on the same secondary replica database where the issue exists

    alter database <DBNAME> set multi_user with rollback immediate

    but it failed with the error saying "The operation cannot be performed on database "dbname" 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.

    Have you checked the logs to see why this happened

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • U J

    SSCrazy

    Points: 2420

    Yeah.. but didn't got any clue from errorlog or trace.. :ermm:

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply