Home Forums SQL Server 2008 SQL Server 2008 Administration DB will become read only. We cannot make any modifications here, How to avoid this scenario RE: DB will become read only. We cannot make any modifications here, How to avoid this scenario

  • February 26, 2014 at 8:50 pm

    #1692513

    David Webb-CDS (2/26/2014)

    Several possible ways to prevent the log filling:

    Insert the records in batches and take log backups between the batches.

    Switch the DB to 'Simple' recovery mode, insert in batches, switch back to 'Full' recovery mode, take an immediate full backup (this may not be advisable if you need to maintain point in time recoverability for the DB)

    Allow the log to grow, or add space to the log to cover the amount of data. Remove other files from the drive if possible to free up space.

    Without more information about the situation, it's tough to give meaningful advice.

    You don't need to take a FULL backup to reestablish the log chain after switching back to the FULL recovery mode. DIFs work just fine for that.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)