Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Backup Not Truncating Logs after Putting in Replication


Backup Not Truncating Logs after Putting in Replication

Author
Message
EasyEMiller
EasyEMiller
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 656
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.
Cody K
Cody K
SSC Veteran
SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)

Group: General Forum Members
Points: 255 Visits: 1104
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search