SQL Sever Replication - is there a good place to check the state of the data in large replications?

rzup
rzup used Ask the Experts™
on
I have a SQL Server 2005 (std) database that replicates across a relatively slow link (DS1) to another server (same type)--I replicate an entire database (not just a few tables). Recently I had to delete and start the replication again, since it is 70gigs in size, it takes a long while to replicate. I notice after a day, it has created the schema on the subscriber, but no data is present in the subscriber (I started by deleting the old subscriber db and starting with just the db and no schema or data).  The job seems to still be churning. Is there a way to see where it is or view the data? I feel like it's too much in the background and I can't tell the state of the replication or if it will be ultimately successful.  

Any good tools for monitoring replication more granularly--I'd like to know exactly what is it doing at the moment. This is a pull-based replication created using the replicaiton options in 2005 (built with management studio)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Analyst
Commented:
Are you using SQL Server Replication Monitor? You can monitor all described below and setup warnings and treshholds for different events. Or you could use SQL Profiler but caution as it is resource consuming!

Monitoring Replication with Replication Monitor:
http://msdn.microsoft.com/en-us/library/ms151780(v=SQL.90).aspx

Microsoft SQL Server Replication Monitor is a graphical tool that allows you to monitor the overall health of a replication topology. Replication Monitor provides detailed information on the status and performance of publications and subscriptions, allowing you to answer common questions, such as:

Is my replication system healthy?
Which subscriptions are slow?
How far behind is my transactional subscription?
How long will it take a transaction committed now to reach a Subscriber in transactional replication?
Why is my merge subscription slow?
Why is an agent not running?
in replication monitor go to the left hand pane and expand your server.
Click on your replication.
In the right pane you should then see a tab saying "all subscriptions" after this there will be a tab called warnings and agents.
 Click on this: the bottom pane called agents and jobs related to thsi subscription should tell you the status so far.  Double click on the agent and it should tell you the status of the job in detail.

Once you've got this out of the way I might be a good idea to separate out your replication into tables.  Although a pain to set up in the first place if there is an issue you only have to re run a small part of the process later.

Cheers

Author

Commented:

The replication monitor looks like a pretty good tool, thanks.

I am looking at the replication monitor. it says the Log reader agent is running, but it shows "no replicated transactions are available".  Why would that be? Under the undistributed commands there seem to be 1302 commands ready waiting to be applied. I'm really still not sure if anything is happening--it looks like not. Even though the data is clearly not synchronized. Thanks for the suggestions.

Author

Commented:
Looks like the replication monitor is finally showing some signs of activity in the  associated logs. Thanks for the pointer

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial