SQL Server Performance

log shipping - how can i change database growth settings on read/only database?

Discussion in 'ALL SQL SERVER QUESTIONS' started by Trev256b, Feb 24, 2012.

  1. Trev256b Member

    hi
    i'm trying to amend the database growth settings, but as the standy by dataabse is read only i can't. any ideas how to do this?
    here is the related error:
    To use ALTER DATABASE, the database must be in a writable state in which a checkpoint can be executed. (Microsoft SQL Server, Error: 5004)
  2. Shehap MVP, MCTS, MCITP SQL Server

    You have to make DB in write mode first before doing that by returning it back to the default status Read-Only=False, but you might encounter some obstacle of some end users connection DB even no active transaction is there , thereby you have to switch DB into single mode first >>>then change read-only option >>>then return back to Multi user mode as below:

    USE [master]

    settransactionisolationlevelserializable

    ALTERDATABASE [2PMallDB] SET SINGLE_USERWITHROLLBACKIMMEDIATE

    ALTERDATABASE [2PMallDB] SET READ_WRITEWITH NO_WAIT

    ALTERDATABASE [2PMallDB] SET Multi_USERWITHROLLBACKIMMEDIATE
  3. Luis Martin Moderator

    Shehap is right.
    You can do it the same using Management Studio (if you have problems writing code).
  4. Trev256b Member

    ok - so i can makie the change when the database is in read-write mode yes?
    but by changing the standby db to read-write mode will this prevent log-shipping from working? what should be done to ensure log shipping does not fail or crash?

Share This Page