locked
Replication and Single user mode RRS feed

  • Question

  • I have a database with replication (Transaction and Snapshot). I wants to bring my Publisher DB into Single user mode for some DBCC Consistency repair. Now my questions are:

    1- Will it allowed me to put that DB into Single user mode?
    2- Would there be any impact on Single user mode due to log reader agent? 

     
    Thursday, November 5, 2015 8:40 AM

Answers

  • Yes, you can put a published/subscribed database into single user mode, you will need to be careful while doing so as the replication jobs will continually attempt to access the database. It is best to turn the log reader agent job off (if it is a published database) and the distribution agent off (If it is a subscriber database).

    Now, I normally do this

    use master

    alter database DatabaseName set single_user with rollback immediate

    dbcc checkdb('datbasename', REPAIR_ALLOW_DATA_LOSS)

    You may find even when doing that that you might need to kill active connections to this database.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Friday, November 6, 2015 3:19 PM
    Answerer

All replies

  • Hi

    here is a link that explains most of your concerns and a step by step guide;

    https://msdn.microsoft.com/en-AU/library/ms345598.aspx

    hope this helps

    Thursday, November 5, 2015 8:55 AM
  • This link is for only Single User mode. While my question is about : Impact of Single user mode while Replication is enabled?
    Thursday, November 5, 2015 9:23 AM
  • Yes, you can put a published/subscribed database into single user mode, you will need to be careful while doing so as the replication jobs will continually attempt to access the database. It is best to turn the log reader agent job off (if it is a published database) and the distribution agent off (If it is a subscriber database).

    Now, I normally do this

    use master

    alter database DatabaseName set single_user with rollback immediate

    dbcc checkdb('datbasename', REPAIR_ALLOW_DATA_LOSS)

    You may find even when doing that that you might need to kill active connections to this database.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Friday, November 6, 2015 3:19 PM
    Answerer
  • I would like to put a replicated database (publisher and a subscriber database) into single user mode to turn on service broker. Would it be ok to :

    1) Turn off the SQL agent

    2) Put database into single user mode 

    3) Turn on service broker 

    4) Set database to multi user

    5) Turn on SQL Agent

    Tuesday, January 22, 2019 7:26 PM
  • Yes, this is the recommended solution. Ensure that your spid is the only one accessing the database after putting it into single user mode. On busy databases you may find another spid might steal the single_user sessions. You may need to disable user accounts and kill the spids accessing this database to ensure that you own the single_user mode.
    Wednesday, January 23, 2019 2:30 PM
    Answerer