April 2, 2014 at 2:44 pm
We are currently running SQL Server 2008 R2. One of our business processes dictated that we replicate data from 2 tables into a reporting database on another server with minimal latency. To achieve this, I put in a "simple" publication with the 2 tables using a distribution database on the primary server. Everything seems to be working properly with data flowing from the primary database to the secondary; however, we are running into issues with our log backups and the error message below.
The log was not truncated because records at the beginning of the log are pending replication or Change Data Capture. Ensure the Log Reader Agent or capture job is running or use sp_repldone to mark transactions as distributed or captured.
Our issue is that our transaction log for this database is not removing the pending transactions and I want to get this figured out before we run into a disk space issue. I did a bunch of research about using sp_repldone, but I understand that to be a temporary fix. I have tried totally removing the subscriptions, publication, and the database as a publisher then recreating everything again. I still receive this error during my hourly transaction log backups. Can anyone point me to what I should look for in order to make sure I have things setup correctly or any tips in general? I'm relatively new to using replication.
April 2, 2014 at 9:03 pm
If the log reader agent is running, and changes are being picked up and sent out, and the sys.databases log_reuse_wait_desc for the publication database is showing "replication", then I'm not sure.
I haven't found a good source for replication internals so often take the shotgun approach, turning off transactional replication entirely and recreating the publications and subscriptions. That's an option for us anyway because we're just sending out data at night time. (Also we use replicate data only... so... it doesn't create and pre-populate the tables or anything costly like that).
So next choice: google log_reuse_wait_desc replication (like this hit: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/633ea08c-942d-4703-bc03-f2befd35f612/log-file-size-keeps-growing-replication?forum=sqlreplication). And post back when you find out. Sorry I can't be of more help.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply