Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Transactional replication, couple problems

Posted on 2013-01-30
Medium Priority
Last Modified: 2013-02-12
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.
Question by:dbaSQL
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
LVL 37

Expert Comment

ID: 38835833
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
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38835979
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.
LVL 17

Author Comment

ID: 38836327
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?
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

LVL 70

Expert Comment

by:Scott Pletcher
ID: 38837025
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.
LVL 17

Author Comment

ID: 38839227
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'
LVL 17

Author Comment

ID: 38839258
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
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38840247
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).
LVL 17

Author Comment

ID: 38843774
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?
LVL 70

Accepted Solution

Scott Pletcher earned 2000 total points
ID: 38843975
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.]
LVL 17

Author Closing Comment

ID: 38880780
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.
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38880854
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.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question