Solved

Can I view Replication history logs?

Posted on 2007-04-01
2
605 Views
Last Modified: 2012-06-21
SUMMARY

When using Merge Replication, how can I view a history of what was actually replicated? Although detail would be ideal, even a summary level (i.e. n rows replicated from table x, n rows from table y, etc.) would be very helpful.

FULL BACKGROUND

We have a pair of SQL Server 2000 databases that were designed, built and maintained by a vendor. They are kept in sync with Merge Replication scheduled to run once each minute. However, as far as I can tell although the databases have very low transaction volume (typically a few updates per minute), the replication performance is unbelievably slow -- each rep cycle takes about 7 minutes! Needlessly to say, this hugely impacts system usability, as even a best case a response takes 14 minutes for what should be a minute or two; worst case is 28 minutes.

Because the entire system is "owned" by the vendor, we have little access to the innards. It is not a total black box, but our access is fairly limited. Despite our serious performance problem, all we get is finger pointing between the vendor and our IT dept. I suspect some gross Replication misconfiguration, like maybe the vendor accidentally set it to do a Snapshot every minute rather than just the Merge.

From my reading of Replication tutorials, I know that logs are kept, but I don’t know if these logs are only temporary data (i.e. rows pending replicated) or persistent data that could shed light on what is going on behind the scenes.

Note that I am not trying to get you folks to try and diagnose our performance problem -- I am sure there are far too many variables for that, plus my knowledge and access are limited. All I really want to know is if there is some kind of replication history log I might be able to access.

I have limited knowledge of SQL Server and Replication. I was a VB/Sybase app developer for many years (controlled tables, indexes, views, and stored procedures), but was never an SA. A few years ago I also ran MSDE on my home PC, using Visual Studio DotNET for the SQL front end. My only knowledge of Replication is by reading a few tutorials on-line.


Thank you in advance,
parkerea
0
Comment
Question by:parkerea
[X]
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
2 Comments
 
LVL 21

Accepted Solution

by:
Kevin3NF earned 250 total points
ID: 18834165
Search Books Online for the term outputverboselevel  It is a parameter you can add to the 'Run Agent' step of the job that runs your merge agent.

Add these:

-output c:\whatever.txt -outputverboselevel 3

You will get detailed info of everything the agent does.

It does not overwrite, so be sure to turn it off when done.

Performance issues are best dealt with using SQL Profiler, not merge logs.

Hope that helps,

Kevin3NF
0
 

Author Comment

by:parkerea
ID: 18839473
Excellent -- thank you very much! Following your advice I found the appropriate article in the Support.Microsoft.Com knowledge base.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

737 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