Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Backup Not Truncating Logs after Putting in Replication Expand / Collapse
Author
Message
Posted Wednesday, April 2, 2014 2:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 20, 2014 8:55 AM
Points: 1, Visits: 509
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.
Post #1557700
Posted Wednesday, April 2, 2014 9:03 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 8:11 AM
Points: 213, Visits: 855
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.
Post #1557791
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse