Solved

Can I view Replication history logs?

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
What is format f12.8 for a CSV file 6 40
SQL Query stumper 3 36
SQL Pivot add row totals 2 12
Update SQL to SP1 on SCCM server 7 8
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

910 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now