0

I would like to do Transactional replication 10 tables on a Prod server to a Secondary server. The prod server is in Full mode and has t-logs. Secondary is in Simple. Can I still capture T-Logs from Production and restore them (with a full backup of course) if I have a catastrophic failure on Prod? Do the T-Logs get truncated each time the subscriber pulls the transaction from the Publisher (Prod) and applies it to the Subscriber?

IMeitzen
  • 35
  • 4
  • If your transactions are already sent to the publisher, the transaction log VLF is released once all other regular transactions are completed. That allows the log file to be truncated. If the distributor is filled up, your log files will grow and it will not release them until it hits the distributor again or you reinit. This is my understanding from 5 years ago, I haven't revisited it since then though. I'll help dig up documentation on it to ensure. – Ali Razeghi - AWS Aug 24 '16 at 19:10
  • Thank you Ali. I have an automated process that takes a full backup of Prod every night and a T-log backup every 15 min. Will Transactional replication still allow me to take t-log backups on Prod? – IMeitzen Aug 24 '16 at 19:40
  • Absolutely. SQL Server internally protects you from losing data to help maintain ACID compliance. Thus if the distributor has the data, SQL Server says it's ok to truncate this after it has been backed up. – Ali Razeghi - AWS Aug 24 '16 at 21:20
  • Ali, Thank you for your comments. More to learn and grow. – IMeitzen Aug 31 '16 at 17:16
  • NP, we all are! Let us know if you need more help later. – Ali Razeghi - AWS Aug 31 '16 at 19:21

1 Answers1

1

Can I still capture T-Logs from Production and restore them (with a full backup of course) if I have a catastrophic failure on Prod?

Yes. Just make sure that the log chain is not broken by adhoc log backups. You can use sp_restoregene (tsql based) or ps_restoregene (PowerShell based) to automate when disaster happens.

Do the T-Logs get truncated each time the subscriber pulls the transaction from the Publisher (Prod) and applies it to the Subscriber?

Once the transaction is replicated to subscriber, the log file can be truncated.

You can review the log_reuse_wait and log_reuse_wait_desc columns of the sys.databases catalog view to determine why the transaction log space is not reused and why the transaction log cannot be truncated. See this KB 317375 for more details.

Check my answer for - Replicated Database Log File Maintenance. Also, Kendra Little wrote an excellent blog post on : You Can’t Kill Transactional Replication

Community
  • 1
Kin Shah
  • 62,225
  • 6
  • 120
  • 236
  • Thank you Kin, This was the right answer. I have researched it and have found from the sys.databases cat view: the word "NOTHING" in the log_reuse_wait_desc column. I have implemented Transnational replication successfully. I will have to research the sp_restoregen. We have a 3rd party tool backing (Veeam) up our system and it also backs up and truncates our T-Logs. So a visit to them will be appropriate. I will read your answer " Replicated Database Log File Maintenance" Everyone is pretty excited about this solution. – IMeitzen Aug 31 '16 at 17:15
  • @IMeitzen Glad you found it useful :-) – Kin Shah Aug 31 '16 at 17:18