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?
Asked
Active
Viewed 1,050 times
1 Answers
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
