Post reply

Mirror Database Transaction Log Maintenance

  • June 4, 2012 at 7:07 am

    #257445

    Good Morning Everyone

    Hope that everyone had a relaxing weekend.

    I have a server that all the databases are mirrored. The transaction logs for each database is rather large, and I would like to truncate the old transactions using backup log. Will this bother the LSNs? Or mess up the mirror?

    If anyone has a better way of doing this, please let me know.

    Thank you in advance for all your suggestions and advice.

    Thanks

    Andrew SQLDBA

  • June 4, 2012 at 7:21 am

    #1496469

    The databases are in full recovery so you absolutely have to take log backups on the principal or the log will grow til it fills the drive.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 4, 2012 at 7:53 am

    #1496487

    I would follow Gail's advice. Take log backups on the primary. Don't truncate the logs. Once you have those set up, if you need to shrink the logs, you can do that on the primary.

  • June 4, 2012 at 8:48 am

    #1496531

    Thanks Gail and Steve

    So I can shrink that logs without messing up the LSNs? How can I shrink the logs without putting the database into simple recovery mode?

    Or am I missing something totally?

    Thanks

    Andrew SQLDBA

  • June 4, 2012 at 8:51 am

    #1496534

    AndrewSQLDBA (6/4/2012)

    Thanks Gail and Steve

    So I can shrink that logs without messing up the LSNs?

    Of course. Shrinking will never break anything or interfere with the log in any way under any circumstances.

    How can I shrink the logs without putting the database into simple recovery mode?

    DBCC ShrinkFile. It's not recovery-model dependant.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 4, 2012 at 9:25 am

    #1496571

    Thanks Gail

    Greatly appreciate it. Just needed to make certain before executing

    Andrew SQLDBA

Viewing 6 posts - 1 through 5 (of 5 total)

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