backup log TO DISK NUL and Database Mirror

  • Dears,

    My database base DB1 is mirrored to another DB1 on another server. I found the size of log file has been increating quickly.

    I want to run the following two statements to release the disk space of the log file:

    backup log DB1 TO DISK = 'nul:' with stats = 10

    DBCC SHRINKFILE ( DB1_log, 1)

    I have questions:

    1. Does this operation affect the database mirror? Will this operation cause the database mirror fail?

    2. Shall I setup a job to run these 2 statements everyday?

    Thank you in advance.

  • No, but it will break your log chain and remove any ability to restore to a point in time. Rather set up proper log backups at an interval based on your data loss SLAs.

    No you should definitely not run those daily. Never is a good frequency for those statements

    Please take a read through this: http://www.sqlservercentral.com/articles/Administration/64582/

    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
  • I'll make a plan to backup log daily and copy it to another server according your suggestion.

    But, now, I need a little disk size.

    I backup the database and run the two statements and found the funny result:

    the mirror database log size also be truncated.

    Thank you, Gail.

  • Daily? Does that meet your data loss SLAs in cases where mirroring doesn't help (like an accidentally dropped table)?

    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
  • ah?

    Will this happen, the DB and mirroring DB are corrupt in the same time? I never thought about this.

    Thank you for your reminding. I'll make a new solution for the log backup and storing..

  • ah?

    Will this happen, the DB and mirroring DB are corrupt in the same time? I never thought about this.

    Thank you for your reminding. I'll make a new solution for the log backup and storing..

  • Simon Su (7/14/2014)


    Will this happen, the DB and mirroring DB are corrupt in the same time? I never thought about this.

    Corrupt? Highly unlikely, the chance of two IO subsystems damaging a database file at the same time are incredibly slim, however that wasn't the example I gave. I said 'accidentally dropped table', as in "Woops, I think I just dropped a table on Production by accident."

    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
  • Your advice is invaluable. I backup the database log in 10 minutes and copy it to another server.

    THANK YOU, Gail.

Viewing 8 posts - 1 through 7 (of 7 total)

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