Transactional replication, couple problems

v2005, transactional replication. Fairly simple, one pub, one sub, running all day long, just to keep redundancy in synch.  Came to my attention that many tables were not included. We spent some time getting them in place over the weekend.

Now, we're having recurring problems blowing the team log in the subscriber, and I have an error I haven't encountered before.

Server Error Log
Time      Error
2013-01-29T19:45:51.743      The transaction log for database 'XX' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

2013-01-29T19:45:51.743      if COLUMNPROPERTY( OBJECT_ID('MSreplication_subscriptions'),'distribution_agent','AllowsNull') <> 1 BEGIN alter table MSreplication_subscriptions alter column distribution_agent sysname null end

2013-01-29T19:45:51.743      The transaction log for database  'XX' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Aside from the above, there are no obvious errors.  And, the articles are being transferred just fine.

Any suggestions from EE replication experts would truly be appreciated.
LVL 17
Who is Participating?

Improve company productivity with a Business Account.Sign Up

Scott PletcherConnect With a Mentor Senior DBACommented:
When you do "immediate update", SQL does exactly that: it immediately connects to the subscriber(s) and involves them in what's called a two-phase commit process for all modifications (DELETEs, INSERTs, UPDATEs).  

Basically this means the entire transaction integrity process ("all or none") must occur in tandem on both machines involved, and SQL literally waits until both processes complete before applying any mods to the underlying table.  [Naturally Google "two phase commit" for more details.]
have you tried to truncate it manually?
the transaction log can't be truncated until all subscribers get the updates from that log so if for some reason replication is behind, you might get these messages
Scott PletcherSenior DBACommented:
FIRST, you MUST give the log more disk space or another file to use.  The database is DEAD until it can write to its log.
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

dbaSQLAuthor Commented:
i gotcha, scott. in fact, i already have the new disk getting plugged in at eod.  But, that is not the source of the problem.  It is only a bandaid until I can figure out what is causing this.

My gutt says it is simply that we are using some default replication settings, that are no longer adequate.  Even with that, adding 1 new article or 50 new articles, should not impede the integrity of my redundancy.  I'm thinking it's default configured at every end of the stick -- replication, transaction log, and otherwise.  We  were logging X before,  we added maybe 45 new articles, and now we are logging XXXX.  The box is already weak on disk.  There's no way it is going to allow for this w/out a lot of pain.

LOG_BACKUP is the reason that the last log was blown.  (log_reuse_wait_desc)  When I looked at it the other day, we had 9K + VLF's.  Then, after clearing the log, we had 380.  Pretty sure Miss Tripp would wrinkle her nose:

I was reading this earlier:

Specifically, the 'Transactional Replication and the Transaction Log' section.

All of it suggesting that yes, we've got some default config that isn't going to allow for the new articles without all kinds of pain.  (you should see the sql error log.. i've got 3314's all over the place)

So.  What to do?
Scott PletcherSenior DBACommented:
Once the disk issue is temporarily overcome ...

If you're using immediately updating subscription, you could change it to use queued.  That should reduce the overhead.
dbaSQLAuthor Commented:
This replication was authored by someone else. Im always a little hestitant on something i did not defined.  In the gui, on the replcation monitor, Subscription Options, these are all of the sub options:

Independent Distribution Agent         True
Snapshot always available                   False
Allow anonymous Subscriptions         False
Attachable Subscription database      False
Allow pull  subscriptions                      True
Allow initialization from backup files    False
Allow non-SQL subscribers                    False
Allow data transformations                   False
Allow peer to peer subscriptions          False
Replicate schema changes                     True
Allow immediate updating subscriptions    False
Allow queued updating subscriptions          False

Those bottom two are grey/untouchable. I would have thought at least one would be enabled.  I'm not sure how to alter this, scott.  Also, if i run this on the subscriber, i get nothing back.  

sp_helppullsubscription @publisher = 'xxxx',
    @publisher_db = 'xxx',
    @publication =  'xxx'
dbaSQLAuthor Commented:
sp_helpsubscription on the publisher tells me this:

status                       Active
synch type              Automatic
sub type                       Push
full sub                            1
update mode           read only
loopback  detection        0
offload enabled               0
Scott PletcherSenior DBACommented:
If the data to be replicated is at all large, you want to enable "allow initialization from backup files" and do the initial "sync" using a db backup.  You can do that in the existing publication.  You might have to drop and re-add the subscribed, "telling" SQL you'll do the initialization with a backup instead of SQL being forced to initially send *all* data to the subscriber to start the replication process.

Sorry; I should have explicitly stated that to change to queued sync from automatic that you would need to create a new publication (i.e., probably drop the old one and recreate it).
dbaSQLAuthor Commented:
Scott, can you elaborate for me, let me know why the queued synch is a more optimal mode?  Or even just why you think this may help to resolve the problem?
dbaSQLAuthor Commented:
Hey Scott, I apologize for the delayed response.  If you could give me the scoop on the correct steps to take, in order to make this change, I would be hugely grateful.  Otherwise, I am grateful for the input, and I can see now, why this would be to our gain.

Thank you very much for the insight.
Scott PletcherSenior DBACommented:
Unfortunately, IIRC, I think the type of publication -- immediate or delayed -- is set at the time you create the pub.  Thus, I think you would have to drop the old publication and start a new one to switch to deferred updates.  But, in your situation, I think deferred updates would be preferable, so it's probably worth the one-time hassle to do the switch.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.