none
SQL Server 2012 AlwaysON Replica Backups

    Question

  • I am using SQL Server 2012 on Windows Server 2008 R2 Core.

    I have alwayson configured and running.

    I have transaction log backups set to run on the replica.

    They are running successfully. 

    I do NOT have any transaction log backups running on the primary server, only full backups 1x a day.

    The transaction log on the primary is not truncating, just keeps getting bigger and bigger.

    Is this by design?  If so, I MUST run transaction log backups on Primary, which negates the performance gain of just running them on the secondary replicas.

    The question is...do I have to run tlogs on the primary even if I'm doing them on the secondary to keep the log file from growing out of control?

    Thanks!  Ben Jongewaard

    Friday, July 13, 2012 6:02 PM

Answers

  • something is not right here...

    What is the output of the following

    select log_reuse_wait_desc
    from sys.databases
    where database_id = DB_ID('dbname')

    The way it suppos to work, when you take T-Log backup on the secondary it maintains a communication between it and primary. Once a T-Log backup is taken on the secondary it should send LSN back to the primary and informs it that a log backup occured on secondary; which in turn causes log truncation on primary.


    Friday, July 13, 2012 6:45 PM

All replies

  • something is not right here...

    What is the output of the following

    select log_reuse_wait_desc
    from sys.databases
    where database_id = DB_ID('dbname')

    The way it suppos to work, when you take T-Log backup on the secondary it maintains a communication between it and primary. Once a T-Log backup is taken on the secondary it should send LSN back to the primary and informs it that a log backup occured on secondary; which in turn causes log truncation on primary.


    Friday, July 13, 2012 6:45 PM
  • Hello,

    I have have a similar problem : Hudge tlog on primary so I took a Tlog backup but VLF state is still 2 on the DBCC LOGINFO output. So transaction is open (DBCC OPENTRAN and sysprocesses where open_tran > 0 doesn't return any rows) and log_reuse_desc is NOTHING for this database in sys.databases.

    Same result if synchronisation is Sync or Async mode, without any activities on the primary, and with an  alwaysON state equal "Healthy", without any transactions pending on primary. Tlog backup doesn't free the VLF.

    Can you please advices ?

    Many than'ks for your help

    Olivier MAITRE

    Friday, October 11, 2013 9:15 AM