Solved

Can I view Replication history logs?

Posted on 2007-04-01
2
603 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
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

823 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